{"id":468,"date":"2023-02-20T21:18:06","date_gmt":"2023-02-20T13:18:06","guid":{"rendered":"http:\/\/blog.yuekegu.com\/?p=468"},"modified":"2023-02-20T21:18:06","modified_gmt":"2023-02-20T13:18:06","slug":"mysql%e8%ae%a1%e7%ae%97%e6%ad%a3%e6%80%81%e5%88%86%e5%b8%83%e3%80%81%e5%b7%ae%e5%bc%82%e7%b3%bb%e6%95%b0%e3%80%81%e7%a6%bb%e5%9d%87%e5%b7%ae%e3%80%81%e7%a6%bb%e5%9d%87%e5%b7%ae%e7%8e%87","status":"publish","type":"post","link":"https:\/\/book.yuekegu.com\/index.php\/2023\/02\/20\/mysql%e8%ae%a1%e7%ae%97%e6%ad%a3%e6%80%81%e5%88%86%e5%b8%83%e3%80%81%e5%b7%ae%e5%bc%82%e7%b3%bb%e6%95%b0%e3%80%81%e7%a6%bb%e5%9d%87%e5%b7%ae%e3%80%81%e7%a6%bb%e5%9d%87%e5%b7%ae%e7%8e%87\/","title":{"rendered":"mysql\u8ba1\u7b97\u6b63\u6001\u5206\u5e03\u3001\u5dee\u5f02\u7cfb\u6570\u3001\u79bb\u5747\u5dee\u3001\u79bb\u5747\u5dee\u7387"},"content":{"rendered":"\n<p><strong>1\u3001\u6570\u636e\u51c6\u5907<\/strong>\u3000\u3000\u5efa\u8868\u3000<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">drop table if exists score;\ncreate table score(\n    id int not null primary key auto_increment,\n    score decimal(<strong>10<\/strong>,<strong>2<\/strong>) not null\n);<\/pre>\n\n\n\n<p>\u3000\u3000\u63d2\u5165\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">insert into score (score) values (<strong>89.4<\/strong>);\ninsert into score (score) values (<strong>82.5<\/strong>);\ninsert into score (score) values (<strong>65<\/strong>);\ninsert into score (score) values (<strong>98<\/strong>);\ninsert into score (score) values (<strong>92<\/strong>);\ninsert into score (score) values (<strong>34<\/strong>);\ninsert into score (score) values (<strong>59<\/strong>);\ninsert into score (score) values (<strong>83<\/strong>);\ninsert into score (score) values (<strong>88<\/strong>);\ninsert into score (score) values (<strong>63<\/strong>);\ninsert into score (score) values (<strong>100<\/strong>);\ninsert into score (score) values (<strong>96<\/strong>);\ninsert into score (score) values (<strong>68<\/strong>);\ninsert into score (score) values (<strong>56<\/strong>);<\/pre>\n\n\n\n<p>\u6ce8\u610f\uff1a\u8ba1\u7b97\u6807\u51c6\u5dee\u6700\u5c11\u9700\u8981\u4e24\u4e2a\u503c<\/p>\n\n\n\n<p><strong>2\u3001\u8ba1\u7b97\u6b63\u6001\u5206\u5e03<\/strong><\/p>\n\n\n\n<p>\u8ba1\u7b97\u65b9\u6cd5\u662f\uff1a\u5e73\u5747\u5206-\u6807\u51c6\u5dee\uff08\u56db\u4e2a\u503c\uff1a\u03bc-2\u03c3\uff0c\u03bc-\u03c3\uff0c\u03bc+\u03c3\uff0c\u03bc+2\u03c3\uff09<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select round(score_avg - score_bzc * <strong>2<\/strong>, <strong>2<\/strong>) zt1,\n       round(score_avg - score_bzc, <strong>2<\/strong>)     zt2,\n       round(score_avg + score_bzc, <strong>2<\/strong>)     zt3,\n       round(score_avg + score_bzc * <strong>2<\/strong>, <strong>2<\/strong>) zt4\nfrom (select avg(score) score_avg, stddev_samp(score) score_bzc\n      from score\n      where id in (<strong>1<\/strong>, <strong>2<\/strong>)) as a;<\/pre>\n\n\n\n<p><strong>3\u3001\u8ba1\u7b97\u5dee\u5f02\u7cfb\u6570<\/strong><\/p>\n\n\n\n<p>&nbsp;\u8ba1\u7b97\u65b9\u6cd5\u662f\uff1a\u6807\u51c6\u5dee\/\u5e73\u5747\u5206\uff08\u03c3\/\u03bc\uff09<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select round(score_bzc\/score_avg,<strong>2<\/strong>) cyxs\nfrom (select avg(score) score_avg, stddev_samp(score) score_bzc\n      from score\n      where id in (<strong>1<\/strong>,<strong>2<\/strong>)) as a;<\/pre>\n\n\n\n<p><strong>4\u3001\u8ba1\u7b97\u79bb\u5747\u5dee<\/strong><\/p>\n\n\n\n<p>\u8ba1\u7b97\u65b9\u6cd5\u662f\uff1a\u5355\u4e2a\u503c-\u5e73\u5747\u503c\uff08x-\u03bc\uff09<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select round(score - (avg(score) over ()), <strong>2<\/strong>) ljc\nfrom score\nwhere id in (<strong>1<\/strong>, <strong>2<\/strong>);<\/pre>\n\n\n\n<p><strong>5\u3001\u8ba1\u7b97\u79bb\u5747\u5dee\u7387<\/strong><\/p>\n\n\n\n<p>\u8ba1\u7b97\u65b9\u6cd5\u662f\uff1a\u79bb\u5747\u5dee\/\u5e73\u5747\u503c\uff08\uff08x-\u03bc\uff09\/\u03bc\uff09<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select round((score - (avg(score) over ())) \/ (avg(score) over ()), <strong>2<\/strong>) ljcl\nfrom score\nwhere id in (<strong>1<\/strong>, <strong>2<\/strong>);<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>1\u3001\u6570\u636e\u51c6\u5907\u3000\u3000\u5efa\u8868\u3000 drop table if exists score; create table score( id int not null primary key auto_increment, score decimal(10,2) not null ); \u3000\u3000\u63d2\u5165\u6570\u636e insert into score (score) values (89.4); insert into score (score) values (82.5); insert into score (score) values (65); insert into [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[],"class_list":["post-468","post","type-post","status-publish","format-standard","hentry","category-psychology"],"_links":{"self":[{"href":"https:\/\/book.yuekegu.com\/index.php\/wp-json\/wp\/v2\/posts\/468","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/book.yuekegu.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/book.yuekegu.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/book.yuekegu.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/book.yuekegu.com\/index.php\/wp-json\/wp\/v2\/comments?post=468"}],"version-history":[{"count":0,"href":"https:\/\/book.yuekegu.com\/index.php\/wp-json\/wp\/v2\/posts\/468\/revisions"}],"wp:attachment":[{"href":"https:\/\/book.yuekegu.com\/index.php\/wp-json\/wp\/v2\/media?parent=468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/book.yuekegu.com\/index.php\/wp-json\/wp\/v2\/categories?post=468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/book.yuekegu.com\/index.php\/wp-json\/wp\/v2\/tags?post=468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}