{"id":473,"date":"2009-12-26T23:11:00","date_gmt":"2009-12-26T14:11:00","guid":{"rendered":"http:\/\/www.code-life.jp\/blog\/archives\/473"},"modified":"2010-08-08T19:26:09","modified_gmt":"2010-08-08T10:26:09","slug":"%e6%ad%af%e6%8a%9c%e3%81%91%e3%81%aeid%e3%82%92%e5%85%a8%e3%81%a6%e6%b1%82%e3%82%81%e3%82%8b","status":"publish","type":"post","link":"https:\/\/www.code-life.jp\/blog\/2009\/12\/26\/%e6%ad%af%e6%8a%9c%e3%81%91%e3%81%aeid%e3%82%92%e5%85%a8%e3%81%a6%e6%b1%82%e3%82%81%e3%82%8b\/","title":{"rendered":"\u6b6f\u629c\u3051\u306eID\u3092\u5168\u3066\u6c42\u3081\u308b"},"content":{"rendered":"

\u3053\u3053\u6700\u8fd1\u306e\u30a2\u30af\u30bb\u30b9\u30ed\u30b0\u3092\u95b2\u89a7\u3057\u3066\u307f\u305f\u3068\u3053\u308d\u3001\u6b6f\u629c\u3051\u306eID\u306e\u6c42\u3081\u65b9\u3092\u63a2\u3057\u3066\u3044\u308b\u65b9\u304c\u591a\u3044\u3053\u3068\u306b\u6c17\u3065\u3044\u305f\u306e\u3067\u3001\u81ea\u5206\u3067\u3082\u3061\u3087\u3063\u3068\u8003\u3048\u3066\u307f\u305f\u3002<\/p>\n

\u904e\u53bb\u306e\u30a8\u30f3\u30c8\u30ea\u3067\u7d39\u4ecb\u3057\u305f\u65b9\u6cd5\u306f\u6b6f\u629c\u3051\u306e\u6700\u5c0f\u5024\u3092\u6c42\u3081\u308b<\/a>\u3082\u306e\u3067\u3001\u4eca\u56de\u306f\u6307\u5b9a\u3057\u305f\u7bc4\u56f2\u306e\u6b6f\u629c\u3051\u3092\u5168\u3066\u6c42\u3081\u308b\u3082\u306e\u3067\u3042\u308b\u3002<\/p>\n

\u4eca\u56de\u306e\u65b9\u6cd5\u3067\u3082min\u95a2\u6570\u3092\u4f7f\u3063\u3066\u6b6f\u629c\u3051\u306e\u6700\u5c0f\u5024\u306f\u6c42\u3081\u308b\u3053\u3068\u304c\u3067\u304d\u308b\u3002<\/p>\n

\u52d5\u4f5c\u691c\u8a3c\u306fMySQL\u3067\u884c\u306a\u3063\u3066\u3044\u308b\u3051\u308c\u3069\u7279\u5225\u306a\u95a2\u6570\u306f\u5168\u304f\u4f7f\u7528\u3057\u3066\u3044\u306a\u3044\u306e\u3067\u3001\u4ed6\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3067\u3082\u307b\u3093\u306e\u5c11\u3057\u4fee\u6b63(Oracle\u306e\u5834\u5408\u306ffrom dual\u3092\u8ffd\u52a0\u3059\u308b\u3068\u304b)\u3059\u308b\u3060\u3051\u3067\u52d5\u4f5c\u3059\u308b\u3068\u601d\u3046\u3002<\/p>\n

\u4f5c\u6210\u3057\u305ftablename\u30c6\u30fc\u30d6\u30eb\u306eID\u30ab\u30e9\u30e0\u306f\u6570\u5024\u578b\u3067\u3001\u73fe\u5728(2, 3, 5, 7, 11)\u304c\u4f7f\u7528\u6e08\u307f\u3067\u3042\u308a\u3001\u6b6f\u629c\u3051\u72b6\u614b\u306b\u306a\u3063\u3066\u3044\u308b\u3002<\/p>\n

mysql>\u00a0 select id from tablename;\r\n+----+\r\n| id |\r\n+----+\r\n|\u00a0 2 |\r\n|\u00a0 3 |\r\n|\u00a0 5 |\r\n|\u00a0 7 |\r\n| 11 |\r\n+----+\r\n5 rows in set (0.05 sec)<\/pre>\n
\u3053\u306etablename\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u4f7f\u7528\u3055\u308c\u3066\u3044\u306a\u3044ID(1, 4, 6, 8, 9, 10)\u3092\u6c42\u3081\u308bSQL\u6587\u306f\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u306a\u3063\u305f\u3002<\/p>\n
mysql>\u00a0 select id\r\n\u00a0 \u00a0 ->\u00a0 from (\r\n\u00a0 \u00a0 ->\u00a0 select (ones.num + tens.num + 1) id\r\n\u00a0 \u00a0 ->\u00a0 from (\r\n\u00a0 \u00a0 ->\u00a0 select 0 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 1 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 2 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 3 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 4 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 5 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 6 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 7 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 8 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 9 num) ones\r\n\u00a0 \u00a0 ->\u00a0 cross join\r\n\u00a0 \u00a0 ->\u00a0 (\r\n\u00a0 \u00a0 ->\u00a0 select 0 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 10 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 20 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 30 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 40 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 50 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 60 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 70 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 80 num union all\r\n\u00a0 \u00a0 ->\u00a0 select 90 num) tens\r\n\u00a0 \u00a0 ->\u00a0 ) dummy\r\n\u00a0 \u00a0 ->\u00a0 where dummy.id not in (select tablename.id from tablename)\r\n\u00a0 \u00a0 ->\u00a0 and dummy.id < (select max(tablename.id) from tablename);\r\n+----+\r\n| id |\r\n+----+\r\n|\u00a0 1 |\r\n|\u00a0 4 |\r\n|\u00a0 6 |\r\n|\u00a0 8 |\r\n|\u00a0 9 |\r\n| 10 |\r\n+----+\r\n6 rows in set (0.05 sec)<\/pre>\n
\u81ea\u5206\u304c\u6301\u3063\u3066\u3044\u308b\u77e5\u8b58\u3067\u601d\u3044\u3064\u3044\u305f\u306e\u306f\u3001\u4e0a\u8a18\u306e\u4ed6\u306b\u306fexcept(\u5dee\u96c6\u5408)\u6f14\u7b97\u3092\u7528\u3044\u305f\u65b9\u6cd5(\u4e0b\u8a18)\u3002<\/p>\n
\u3051\u308c\u3069\u3082MySQL\u3067\u306fexcept(\u5dee\u96c6\u5408)\u6f14\u7b97\u304c\u307e\u3060\u30b5\u30dd\u30fc\u30c8\u3055\u308c\u3066\u3044\u306a\u3044\u306e\u3067\u3001\u4ee5\u4e0b\u306eSQL\u6587\u306fOracle\u3067\u52d5\u4f5c\u691c\u8a3c\u3002<\/p>\n
Oracle\u3067except(\u5dee\u96c6\u5408)\u6f14\u7b97\u3092\u884c\u306a\u3046\u306b\u306f\u3001minus\u6f14\u7b97\u5b50\u3092\u4f7f\u3046\u3002<\/p>\n
 select id\r\n from (\r\n   select (ones.num + tens.num + 1) id\r\n   from (\r\n\u00a0 \u00a0  select 0 num from dual union all\r\n\u00a0   \u00a0select 1 num from dual union all\r\n  \u00a0 \u00a0select 2 num from dual union all\r\n\u00a0   \u00a0select 3 num from dual union all\r\n\u00a0   \u00a0select 4 num from dual union all\r\n\u00a0   \u00a0select 5 num from dual union all\r\n  \u00a0 \u00a0select 6 num from dual union all\r\n\u00a0   \u00a0select 7 num from dual union all\r\n  \u00a0 \u00a0select 8 num from dual union all\r\n\u00a0   \u00a0select 9 num from dual\r\n   ) ones\r\n   cross join\r\n   (\r\n\u00a0 \u00a0  select 0 num from dual union all\r\n  \u00a0 \u00a0select 10 num from dual union all\r\n\u00a0   \u00a0select 20 num from dual union all\r\n  \u00a0 \u00a0select 30 num from dual union all\r\n  \u00a0 \u00a0select 40 num from dual union all\r\n  \u00a0 \u00a0select 50 num from dual union all\r\n\u00a0   \u00a0select 60 num from dual union all\r\n\u00a0 \u00a0  select 70 num from dual union all\r\n  \u00a0 \u00a0select 80 num from dual union all\r\n\u00a0   \u00a0select 90 num from dual\r\n   ) tens\r\n ) dummy\r\n where dummy.id < (select max(tablename.id) from tablename)\r\n minus\r\n select id from tablename;\r\n<\/pre>\n
\u6700\u521d\u306eSQL\u6587\u3068\u306e\u9055\u3044\u306f\u3001where\u7bc0\u306e\u6761\u4ef6\u306e\u3046\u3061<\/p>\n
\u00a0 \u00a0 -> dummy.id not in (select tablename.id from tablename)<\/pre>\n
\u306e\u6761\u4ef6\u6587\u304c\u524a\u9664\u3055\u308c\u3066\u3001<\/p>\n
 minus\r\n select id from tablename;<\/pre>\n
\u304c\u8ffd\u52a0\u3055\u308c\u308b\u3002<\/p>\n
\u3069\u3061\u3089\u306b\u3057\u3066\u3082\u3001\u3082\u3063\u3068\u30b9\u30de\u30fc\u30c8\u3067\u51b4\u3048\u305f\u65b9\u6cd5\u304c\u3042\u308b\u3068\u601d\u3046\u3002\u304d\u3063\u3068\u3002<\/p>\n
\u306a\u306e\u3067\u3001\u3053\u3053\u3067\u7d39\u4ecb\u3057\u305f\u6b6f\u629c\u3051\u306eID\u3092\u6c42\u3081\u308b\u65b9\u6cd5\u306f\u3001\u6570\u591a\u304f\u3042\u308b\u65b9\u6cd5\u306e\u3072\u3068\u3064(\u3044\u3084\u3001\u3075\u305f\u3064\u304b)\u3068\u3057\u3066\u53c2\u8003\u306b\u3057\u3066\u3044\u305f\u3060\u3051\u308c\u3070\u5e78\u3044\u3067\u3042\u308b\u3002<\/p>\n
\u3061\u306a\u307f\u306b\u4e0a\u8a18\u306e\u6700\u521d\u306b\u7d39\u4ecb\u3057\u305fSQL\u6587\u306f\u3001\u521d\u3081\u3066\u306eSQL<\/a>\u306e10\u7ae0\u300c\u7d50\u5408\u300d\u3067\u7d39\u4ecb\u3055\u308c\u3066\u3044\u308bSQL\u6587\u306e\u5fdc\u7528\u3067\u3042\u308b(\u66f8\u7c4d\u3092\u5965\u304b\u3089\u5f15\u3063\u5f35\u308a\u51fa\u3057\u3066\u78ba\u8a8d\u3057\u307e\u3057\u305f)\u3002<\/p>\n
\u540c\u66f8\u306e\u5185\u5bb9\u306fMySQL\u3092\u57fa\u6e96\u306b\u66f8\u304b\u308c\u3066\u3044\u308b\u304c\u3001MySQL\u7279\u6709\u306e\u6a5f\u80fd\u306b\u4f9d\u5b58\u3057\u3066\u3044\u308b\u90e8\u5206\u306f\u5c11\u306a\u304f\u3001\u3068\u3053\u308d\u3069\u3053\u308d\u3067Oracle\u3084SQL Server\u3068\u306e\u5dee\u7570\u3084\u4e92\u63db\u6027\u3092\u4f8b\u306b\u6319\u3052\u3066SQL\u306e\u57fa\u672c\u69cb\u6587\u3092\u7d39\u4ecb\u3057\u3066\u3044\u308b(\u540c\u66f8\u306b\u3088\u308c\u3070\u3001\u672c\u6587\u4e2d\u306e\u30b5\u30f3\u30d7\u30eb\u306fOracle\u3084SQL Server\u3067\u3082\u52d5\u4f5c\u3059\u308b\u3089\u3057\u3044)\u3002<\/p>\n
\u3053\u308c\u304b\u3089SQL\u3092\u52c9\u5f37\u3059\u308b\u65b9\u3084\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30b9\u30da\u30b7\u30e3\u30ea\u30b9\u30c8\u3092\u52c9\u5f37\u3059\u308b\u65b9\u306b\u3082\u3001\u8208\u5473\u304c\u3042\u308c\u3070\u4e00\u8aad\u3092\u304a\u52e7\u3081\u3059\u308b\u3002<\/p>\n
\"4873112818.09.MZZZZZZZ.jpg\"Amazon.co.jp\uff1a \u521d\u3081\u3066\u306eSQL<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
\u3053\u3053\u6700\u8fd1\u306e\u30a2\u30af\u30bb\u30b9\u30ed\u30b0\u3092\u95b2\u89a7\u3057\u3066\u307f\u305f\u3068\u3053\u308d\u3001\u6b6f\u629c\u3051\u306eID\u306e\u6c42\u3081\u65b9\u3092\u63a2\u3057\u3066\u3044\u308b\u65b9\u304c\u591a\u3044\u3053\u3068\u306b\u6c17\u3065\u3044\u305f\u306e\u3067\u3001\u81ea\u5206\u3067\u3082\u3061\u3087\u3063\u3068\u8003\u3048\u3066\u307f\u305f\u3002 \u904e\u53bb\u306e\u30a8\u30f3\u30c8\u30ea\u3067\u7d39\u4ecb\u3057\u305f\u65b9\u6cd5\u306f\u6b6f\u629c\u3051\u306e\u6700\u5c0f\u5024\u3092\u6c42\u3081\u308b\u3082\u306e\u3067\u3001\u4eca\u56de\u306f\u6307\u5b9a\u3057\u305f\u7bc4\u56f2\u306e\u6b6f\u629c\u3051… \u7d9a\u304d\u3092\u8aad\u3080 \u6b6f\u629c\u3051\u306eID\u3092\u5168\u3066\u6c42\u3081\u308b<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[11],"tags":[],"_links":{"self":[{"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/posts\/473"}],"collection":[{"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/comments?post=473"}],"version-history":[{"count":3,"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/posts\/473\/revisions"}],"predecessor-version":[{"id":824,"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/posts\/473\/revisions\/824"}],"wp:attachment":[{"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/media?parent=473"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/categories?post=473"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.code-life.jp\/blog\/wp-json\/wp\/v2\/tags?post=473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}