{"id":668,"date":"2008-06-28T13:58:31","date_gmt":"2008-06-28T04:58:31","guid":{"rendered":"http:\/\/www.kazu.tv\/blog\/?p=668"},"modified":"2008-06-28T13:58:31","modified_gmt":"2008-06-28T04:58:31","slug":"sql_1","status":"publish","type":"post","link":"https:\/\/kazu.tv\/blog\/2008\/06\/28\/sql_1\/","title":{"rendered":"SQL \u6700\u5927\u5024\u3092\u6301\u3064\u5217\u3092\u53d6\u5f97"},"content":{"rendered":"

\u524d\u306b\u4f3c\u305f\u3088\u3046\u306a\u30a8\u30f3\u30c8\u30ea<\/a>\u3092\u66f8\u3044\u305f\u3002<\/p>\n

\u3068\u308a\u3042\u3048\u305aMySQL\u4e0a\u3067\u30c6\u30b9\u30c8\u3057\u305f\u4e8b\u3092\u66f8\u304f\u3051\u3069\u3001MySQL\u9650\u5b9a\u306e\u8a71\u3067\u306f\u306a\u3044\u3002<\/p>\n

\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u30c6\u30fc\u30d6\u30eb<\/p>\n

CREATE TABLE  `test`.`testmax` (\n`ID` int(10) unsigned NOT NULL,\n`DateField` datetime NOT NULL,\n`NumField` int(10) unsigned NOT NULL,\n`Val` double NOT NULL\n);<\/pre>\n
\u30c7\u30fc\u30bf\u306e\u4e2d\u8eab\u3068\u3057\u3066\u306f<\/p>\n\n\n\n\n\n\n\n\n\n
ID<\/td>\nDateField<\/td>\nNumField<\/td>\nVal<\/td>\n<\/tr>\n
1<\/td>\n2007-12-20<\/td>\n1<\/td>\n3<\/td>\n<\/tr>\n
3<\/td>\n2007-10-01<\/td>\n1<\/td>\n2<\/td>\n<\/tr>\n
3<\/td>\n2007-10-01<\/td>\n2<\/td>\n4<\/td>\n<\/tr>\n
3<\/td>\n2007-10-01<\/td>\n3<\/td>\n2.3<\/td>\n<\/tr>\n
3<\/td>\n2007-11-01<\/td>\n1<\/td>\n3.3<\/td>\n<\/tr>\n
3<\/td>\n2007-11-01<\/td>\n2<\/td>\n4.1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u25cb\u3084\u308a\u305f\u3044\u4e8b
\n\u5404ID\u6bce\u306b\u6700\u65b0\u306e\u65e5\u4ed8\u306e\u30ec\u30b3\u30fc\u30c9\u306e\u4e2d\u3067\u6700\u5927\u306eNumField\u3092\u6301\u3064\u5024\u3092\u53d6\u308a\u51fa\u3059\u3002<\/p>\n
\u6b32\u3057\u3044\u7d50\u679c\u3068\u3057\u3066\u306f\n<\/p>\n\n\n\n\n\n
ID<\/td>\nDateField<\/td>\nNumField<\/td>\nVal<\/td>\n<\/tr>\n
1<\/td>\n2007-12-20<\/td>\n1<\/td>\n3<\/td>\n<\/tr>\n
3<\/td>\n2007-11-01<\/td>\n1<\/td>\n3.3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/p>\n
\u3068\u308a\u3042\u3048\u305a\u3053\u308c\u304c\u6700\u9069\u304b\u3069\u3046\u304b\u306f\u77e5\u3089\u306a\u3044\u3051\u3069\u3001\u4ee5\u524d\u3084\u3063\u305f\u306e\u306b\u3082\u30461\u3064iNNER JOIN\u3092\u52a0\u3048\u3066\u4f55\u3068\u304b\u51fa\u6765\u305f\u3002SQL\u3063\u3066\u96e3\u3057\u3044…<\/p>\n
SELECT t1.* FROM test.testmax t1\nINNER JOIN (\nSELECT t2.ID, t2.DateField, Max(NumField) as MAXNUM FROM test.testmax t2\nINNER JOIN (\nSELECT ID, MAX(DateField) as MAXDATE FROM test.testmax\nGROUP BY ID\n) as t3\nON (\nt2.ID = t3.ID\nAND t2.DateField = t3.MAXDATE\n)\nGROUP BY t2.DateField\n) as t4\nON (\nt1.ID = t4.ID\nAND t1.DateField= t4.DateField\nAND t1.NumField = t4.MAXNUM\n)<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u6700\u5927\u5024\u3092\u6301\u3064\u5217\u3092\u53d6\u5f97\u3059\u308bSQL<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8],"tags":[135,251,136,137,252],"class_list":["post-668","post","type-post","status-publish","format-standard","hentry","category-8","tag-group-by","tag-inner-join","tag-max","tag-sql","tag-252"],"_links":{"self":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/posts\/668","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/comments?post=668"}],"version-history":[{"count":0,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/posts\/668\/revisions"}],"wp:attachment":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/media?parent=668"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/categories?post=668"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/tags?post=668"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}