{"id":407,"date":"2020-08-15T15:34:07","date_gmt":"2020-08-15T06:34:07","guid":{"rendered":"https:\/\/outdoor-development.work\/index\/?p=407"},"modified":"2020-08-15T15:34:09","modified_gmt":"2020-08-15T06:34:09","slug":"%e3%80%90oracle%e3%80%91%e3%80%90pl-sql%e3%80%91%e9%87%8d%e8%a4%87%e3%83%ac%e3%82%b3%e3%83%bc%e3%83%89%e3%81%ae%e6%8a%bd%e5%87%ba","status":"publish","type":"post","link":"https:\/\/outdoor-development.work\/index\/2020\/08\/15\/%e3%80%90oracle%e3%80%91%e3%80%90pl-sql%e3%80%91%e9%87%8d%e8%a4%87%e3%83%ac%e3%82%b3%e3%83%bc%e3%83%89%e3%81%ae%e6%8a%bd%e5%87%ba\/","title":{"rendered":"\u3010Oracle\u3011\u3010PL\/SQL\u3011\u91cd\u8907\u30ec\u30b3\u30fc\u30c9\u306e\u62bd\u51fa"},"content":{"rendered":"\n<h1>\u91cd\u8907\u30ec\u30b3\u30fc\u30c9\u306e\u62bd\u51fa<\/h1>\n\n<h2>\u3084\u308a\u305f\u3044\u3053\u3068<\/h2>\n\n<p>\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u30c6\u30fc\u30d6\u30eb\u304c\u5b58\u5728\u3057\u305f\u5834\u5408\u306b\u300cPRODUCT_CD\u300d\u3001\u300cPRODUCT_NAME\u300d\u304c\u91cd\u8907\u3057\u3066\u3044\u308b\u30ec\u30b3\u30fc\u30c9\u3092\u62bd\u51fa\u3057\u307e\u3059\u3002<\/p>\n\n<table>\n<thead>\n<tr>\n<th align=\"right\">ID<\/th>\n<th align=\"center\">PRODUCT_CD<\/th>\n<th align=\"center\">PRODUCT_NAME<\/th>\n<th align=\"right\">PRICE<\/th>\n<\/tr>\n<\/thead>\n\n<tbody>\n<tr>\n<td align=\"right\">1<\/td>\n<td align=\"center\">p001<\/td>\n<td align=\"center\">\u30ce\u30fc\u30c8<\/td>\n<td align=\"right\">350<\/td>\n<\/tr>\n\n<tr>\n<td align=\"right\">2<\/td>\n<td align=\"center\">p002<\/td>\n<td align=\"center\">\u30dc\u30fc\u30eb\u30da\u30f3<\/td>\n<td align=\"right\">150<\/td>\n<\/tr>\n\n<tr>\n<td align=\"right\">3<\/td>\n<td align=\"center\">p002<\/td>\n<td align=\"center\">\u30dc\u30fc\u30eb\u30da\u30f3<\/td>\n<td align=\"right\">100<\/td>\n<\/tr>\n\n<tr>\n<td align=\"right\">4<\/td>\n<td align=\"center\">p003<\/td>\n<td align=\"center\">\u624b\u5e33<\/td>\n<td align=\"right\">1000<\/td>\n<\/tr>\n\n<tr>\n<td align=\"right\">5<\/td>\n<td align=\"center\">p003<\/td>\n<td align=\"center\">\u624b\u5e33<\/td>\n<td align=\"right\">500<\/td>\n<\/tr>\n\n<tr>\n<td align=\"right\">6<\/td>\n<td align=\"center\">p003<\/td>\n<td align=\"center\">\u624b\u5e33<\/td>\n<td align=\"right\">350<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n<h2>\u91cd\u8907\u30ec\u30b3\u30fc\u30c9\u30921\u884c\u306b\u307e\u3068\u3081\u3066\u62bd\u51fa<\/h2>\n\n<p>\u91cd\u8907\u3057\u3066\u3044\u308b\u30ec\u30b3\u30fc\u30c9\u3092<code>GROUP BY<\/code>\u3068<code>HAVING<\/code>\u53e5\u3092\u4f7f\u7528\u3057\u3066\u62bd\u51fa\u3057\u307e\u3059\u3002<\/p>\n\n<pre><code class=\"language-sql\">SELECT product_cd,\n       product_name,\n       COUNT(1)             -- \u91cd\u8907\u3057\u3066\u3044\u308b\u4ef6\u6570\nFROM  product_tbl\nGROUP BY product_cd, product_name\nHAVING COUNT(1) &gt; 1\n;\n<\/code><\/pre>\n\n<p>\u62bd\u51fa\u7d50\u679c\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n\n<table>\n<thead>\n<tr>\n<th align=\"center\">PRODUCT_CD<\/th>\n<th align=\"center\">PRODUCT_NAME<\/th>\n<th align=\"center\">COUNT(1)<\/th>\n<\/tr>\n<\/thead>\n\n<tbody>\n<tr>\n<td align=\"center\">p002<\/td>\n<td align=\"center\">\u30dc\u30fc\u30eb\u30da\u30f3<\/td>\n<td align=\"center\">2<\/td>\n<\/tr>\n\n<tr>\n<td align=\"center\">p003<\/td>\n<td align=\"center\">\u624b\u5e33<\/td>\n<td align=\"center\">3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n<h2>\u91cd\u8907\u30ec\u30b3\u30fc\u30c9\u3092\u5168\u4ef6\u62bd\u51fa<\/h2>\n\n<p>\u4e0a\u8a18SQL\u3067\u306f\u91cd\u8907\u30ec\u30b3\u30fc\u30c9\u3092\u307e\u3068\u3081\u3066\u62bd\u51fa\u3057\u307e\u3057\u305f\u304c\u3001\u91cd\u8907\u30ec\u30b3\u30fc\u30c9\u306e\u5185\u5bb9\u3092\u8a73\u3057\u304f\u78ba\u8a8d\u3057\u305f\u3044\u5834\u5408\u3082\u3042\u308a\u307e\u3059\u3002\n\u305d\u306e\u5834\u5408\u306b\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306b\u5bfe\u8c61\u30c7\u30fc\u30bf\u3092\u526f\u554f\u3044\u5408\u308f\u305b\u3092\u4f7f\u7528\u3057\u3066\u62bd\u51fa\u3057\u307e\u3059\u3002<\/p>\n\n<pre><code class=\"language-sql\">SELECT *\nFROM product_tbl\nWHERE (product_cd, product_name) \n      IN (\n         SELECT product_cd, product_name\n         FROM product_tbl\n         GROUP BY product_cd, product_name\n         HAVING COUNT(*) &gt; 1\n         )\nORDER BY product_cd\n;\n<\/code><\/pre>\n\n<p>\u62bd\u51fa\u7d50\u679c\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n\n<table>\n<thead>\n<tr>\n<th align=\"right\">ID<\/th>\n<th align=\"center\">PRODUCT_CD<\/th>\n<th align=\"center\">PRODUCT_NAME<\/th>\n<th align=\"right\">PRICE<\/th>\n<\/tr>\n<\/thead>\n\n<tbody>\n<tr>\n<td align=\"right\">2<\/td>\n<td align=\"center\">p002<\/td>\n<td align=\"center\">\u30dc\u30fc\u30eb\u30da\u30f3<\/td>\n<td align=\"right\">150<\/td>\n<\/tr>\n\n<tr>\n<td align=\"right\">3<\/td>\n<td align=\"center\">p002<\/td>\n<td align=\"center\">\u30dc\u30fc\u30eb\u30da\u30f3<\/td>\n<td align=\"right\">100<\/td>\n<\/tr>\n\n<tr>\n<td align=\"right\">4<\/td>\n<td align=\"center\">p003<\/td>\n<td align=\"center\">\u624b\u5e33<\/td>\n<td align=\"right\">1000<\/td>\n<\/tr>\n\n<tr>\n<td align=\"right\">5<\/td>\n<td align=\"center\">p003<\/td>\n<td align=\"center\">\u624b\u5e33<\/td>\n<td align=\"right\">500<\/td>\n<\/tr>\n\n<tr>\n<td align=\"right\">6<\/td>\n<td align=\"center\">p003<\/td>\n<td align=\"center\">\u624b\u5e33<\/td>\n<td align=\"right\">350<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n<div class=\"veu_socialSet veu_contentAddSection\"><script>window.twttr=(function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],t=window.twttr||{};if(d.getElementById(id))return t;js=d.createElement(s);js.id=id;js.src=\"https:\/\/platform.twitter.com\/widgets.js\";fjs.parentNode.insertBefore(js,fjs);t._e=[];t.ready=function(f){t._e.push(f);};return t;}(document,\"script\",\"twitter-wjs\"));<\/script><ul><li class=\"sb_twitter sb_icon\"><a href=\"\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Foutdoor-development.work%2Findex%2F2020%2F08%2F15%2F%25e3%2580%2590oracle%25e3%2580%2591%25e3%2580%2590pl-sql%25e3%2580%2591%25e9%2587%258d%25e8%25a4%2587%25e3%2583%25ac%25e3%2582%25b3%25e3%2583%25bc%25e3%2583%2589%25e3%2581%25ae%25e6%258a%25bd%25e5%2587%25ba%2F&amp;text=Outdoor+Development\" target=\"_blank\" ><span class=\"vk_icon_w_r_sns_twitter icon_sns\"><\/span><span class=\"sns_txt\">twitter<\/span><\/a><\/li><\/ul><\/div><!-- [ \/.socialSet ] -->","protected":false},"excerpt":{"rendered":"<p>\u91cd\u8907\u30ec\u30b3\u30fc\u30c9\u306e\u62bd\u51fa \u3084\u308a\u305f\u3044\u3053\u3068 \u4ee5\u4e0b\u306e\u3088\u3046\u306a\u30c6\u30fc\u30d6\u30eb\u304c\u5b58\u5728\u3057\u305f\u5834\u5408\u306b\u300cPRODUCT_CD\u300d\u3001\u300cPRODUCT_NAME\u300d\u304c\u91cd\u8907\u3057\u3066\u3044\u308b\u30ec\u30b3\u30fc\u30c9\u3092\u62bd\u51fa\u3057\u307e\u3059\u3002 ID PRODUCT_CD PRODUCT_NAME PR [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[44],"tags":[49,47,48,50,51],"_links":{"self":[{"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/posts\/407"}],"collection":[{"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/comments?post=407"}],"version-history":[{"count":1,"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/posts\/407\/revisions"}],"predecessor-version":[{"id":408,"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/posts\/407\/revisions\/408"}],"wp:attachment":[{"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/media?parent=407"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/categories?post=407"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/outdoor-development.work\/index\/wp-json\/wp\/v2\/tags?post=407"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}