{"id":629,"date":"2007-07-18T08:15:22","date_gmt":"2007-07-17T23:15:22","guid":{"rendered":"http:\/\/www.kazu.tv\/blog\/?p=629"},"modified":"2007-07-18T08:15:22","modified_gmt":"2007-07-17T23:15:22","slug":"sql","status":"publish","type":"post","link":"https:\/\/kazu.tv\/blog\/2007\/07\/18\/sql\/","title":{"rendered":"SQL \u6700\u5927\u5024\u3092\u6301\u3064\u5217\u3092\u53d6\u5f97"},"content":{"rendered":"<p>\u4f8b\u3048\u3070\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u88fd\u54c1\u540d\u3001\u30b5\u30a4\u30ba\u3001\u5024\u6bb5\u3068\u6700\u7d42\u66f4\u65b0\u65e5\u3092\u5165\u529b\u3059\u308b\u30c6\u30fc\u30d6\u30eb\u3092\u8003\u3048\u308b\u3002\u30c7\u30fc\u30bf\u306fINSERT\u3059\u308b\u306e\u307f\u3002\u540c\u3058\u88fd\u54c1\u3067\u3082\u30b5\u30a4\u30ba\u304c\u9055\u3048\u3070\u5024\u6bb5\u306f\u9055\u3046\u3082\u306e\u3068\u3059\u308b\u3002<\/p>\n<table cellspacing=1>\n<tr>\n<td>PRODUCT<\/td>\n<td>SIZE<\/td>\n<td>UPDATED<\/td>\n<td>PRICE<\/td>\n<\/tr>\n<tr>\n<td>\u30b7\u30e3\u30c41<\/td>\n<td>L<\/td>\n<td>2007-01-01<\/td>\n<td>2900<\/td>\n<\/tr>\n<tr>\n<td>\u30b7\u30e3\u30c41<\/td>\n<td>S<\/td>\n<td>2007-01-01<\/td>\n<td>2900<\/td>\n<\/tr>\n<tr>\n<td>\u30b7\u30e3\u30c41<\/td>\n<td>L<\/td>\n<td>2007-02-01<\/td>\n<td>1900<\/td>\n<\/tr>\n<tr>\n<td>\u30b7\u30e3\u30c42<\/td>\n<td>S<\/td>\n<td>2007-01-01<\/td>\n<td>4900<\/td>\n<\/tr>\n<tr>\n<td>\u30b7\u30e3\u30c42<\/td>\n<td>S<\/td>\n<td>2007-02-01<\/td>\n<td>3900<\/td>\n<\/tr>\n<\/table>\n<p>\u3053\u3063\u304b\u3089\u5404\u88fd\u54c1\u306e\u73fe\u5728\u306e\u5024\u6bb5\u3092\u4e00\u89a7\u3092\u77e5\u308a\u305f\u3044\u3002PRODUCT\u3068SIZE\u3067GROUP BY\u3057\u3066\u3001\u5404\u30b0\u30eb\u30fc\u30d7\u304b\u3089UPDATED\u304c\u6700\u5927\u306e\u5217\u3092\u9078\u3079\u3070\u3044\u3044\u3002<\/p>\n<p>\u307e\u3001\u7d50\u8ad6\u304b\u3089\u66f8\u304f\u3068\u3053\u3093\u306a\u611f\u3058\u3002MySQL\u306e\u5834\u5408\u3060\u3051\u3069\u3001\u4ed6\u306eDBMS\u3067\u3082\u5927\u4f53\u4e00\u7dd2\u304b\u3068\u3002<\/p>\n<pre class=\"code\">SELECT T.* FROM TABLE1 T\nINNER JOIN (\nSELECT MAX(UPDATED) AS LAST_UPDATED\nFROM TABLE1\nGROUP BY PRODUCT, SIZE\n) AS T2\nON (\nT1.PRODUCT=T2.PRODUCT\nAND T1.SIZE=T2.SIZE\nAND T1.UPDATED = LAST_UPDATED)\n<\/pre>\n<p>ON\u53e5\u306b\u6ce8\u76ee\u3063\u3066\u611f\u3058<\/p>\n<p>\u3061\u306a\u307f\u306bMySQL\u306e\u5834\u5408\u3001\u4e0a\u306e\u4f8b\u3067\u306eAS T2\u3092\u30b5\u30d6\u30af\u30a8\u30ea\u30fc\u306e\u4e2d\u306eFROM TABLE1\u306e\u5f8c\u306b\u4ed8\u3051\u308b\u3068\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u30a8\u30e9\u30fc\u306b\u306a\u3063\u305f\u3002<\/p>\n<pre class=\"code\">Every derived table must have its own alias<\/pre>\n<p>\u53c2\u8003\u306b\u3057\u305f\u306e\u306f\u3001\u3053\u306e<a href=\"http:\/\/homepage1.nifty.com\/rucio\/main\/VBdotNet\/Database\/Database9.htm\" target=\"_blank\">\u30da\u30fc\u30b8<\/a>\u306e5-2\u306e\u76f4\u524d\u306b\u66f8\u3044\u3066\u3042\u308bSQL\u3002<\/p>\n<p><!--more--><br \/>\n\u5b9f\u969b\u306b\u3053\u306e\u3088\u3046\u306a\u5546\u54c1\u30c7\u30fc\u30bf\u3092\u6271\u3046\u5834\u5408\u306f\u5f53\u7136\u9055\u3046\u30c6\u30fc\u30d6\u30eb\u8a2d\u8a08\u306b\u3059\u308b\u3051\u3069\u3001\u4eca\u56de\u306eSQL\u306e\u89e3\u8aac\u306e\u305f\u3081\u306b\u9069\u5f53\u306b\u4f5c\u3063\u305f\u30c7\u30fc\u30bf\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4f8b\u3048\u3070\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u88fd\u54c1\u540d\u3001\u30b5\u30a4\u30ba\u3001\u5024\u6bb5\u3068\u6700\u7d42\u66f4\u65b0\u65e5\u3092\u5165\u529b\u3059\u308b\u30c6\u30fc\u30d6\u30eb\u3092\u8003\u3048\u308b\u3002\u30c7\u30fc\u30bf\u306fINSERT\u3059\u308b\u306e\u307f\u3002\u540c\u3058\u88fd\u54c1\u3067\u3082\u30b5\u30a4\u30ba\u304c\u9055\u3048\u3070\u5024\u6bb5\u306f\u9055\u3046\u3082\u306e\u3068\u3059\u308b\u3002 PRODUCT SIZE UPDATED PRICE \u30b7\u30e3\u30c41&hellip;<\/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,136,137,138],"class_list":["post-629","post","type-post","status-publish","format-standard","hentry","category-8","tag-group-by","tag-max","tag-sql","tag-138"],"_links":{"self":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/posts\/629","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=629"}],"version-history":[{"count":0,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/posts\/629\/revisions"}],"wp:attachment":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/media?parent=629"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/categories?post=629"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/tags?post=629"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}