{"id":338,"date":"2018-07-18T18:33:56","date_gmt":"2018-07-18T09:33:56","guid":{"rendered":"http:\/\/www.fatware.jp\/?page_id=338"},"modified":"2019-12-13T14:47:21","modified_gmt":"2019-12-13T05:47:21","slug":"%e3%82%af%e3%83%ad%e3%82%b9%e9%9b%86%e8%a8%88","status":"publish","type":"page","link":"https:\/\/www.fatware.jp\/?page_id=338","title":{"rendered":"[SQL]\u5e74\u5ea6\u5bfe\u5fdc\u30af\u30ed\u30b9\u96c6\u8a08"},"content":{"rendered":"\n<p>\u3053\u306e\u4f8b\u3067\u306f\u4f1a\u793e\u306e\u7d4c\u7406\u3092\u4f8b\u3068\u3057\u3066\u3044\u307e\u3059\u3002\u7d4c\u7406\u3067\u306f\u4f1a\u793e\u3078\u306e\u5165\u51fa\u91d1\u3092\u4ed5\u8a33\u4f1d\u7968\u3068\u8a00\u3046\u5358\u4f4d\u3067\u7ba1\u7406\u3057\u3066\u3044\u307e\u3059\u3002SQL\u4e0a\u3067\u306f\u3053\u306e\u4f1d\u7968\u3092\u5358\u4f4d\u3068\u3057\u3066\u30bf\u30d7\u30eb\u3067\u8a18\u9332\u3057\u3066\u3044\u308b\u4f8b\u3067\u3059\u3002\u3064\u307e\u308a\u300cx\u6708\uff59\u65e5\u306b\u4ea4\u901a\u8cbb\u306e\u7d4c\u8cbb\u652f\u6255\u3044\u304c\u884c\u308f\u308c\u305f\u300d\u3068\u3044\u3046\u3088\u3046\u306a\u60c5\u5831\u3092\u8a18\u9332\u3057\u3066\u3044\u307e\u3059\u3002\u3053\u306e\u72b6\u614b\u30671\u6708\u304b\u308912\u6708\u306e\u4ea4\u901a\u8cbb\u306e\u6708\u6b21\u5408\u8a08\u304c\u77e5\u308a\u305f\u3044\u5834\u5408\u306b\u4eca\u56de\u306e\u30af\u30ed\u30b9\u96c6\u8a08\u3092\u884c\u3044\u307e\u3059\u3002<\/p>\n\n\n\n<p>\u3064\u307e\u308a\u5e74\u5ea6\u306e\u6708\u3054\u3068\u3001\u79d1\u76ee\u3054\u3068\u306b\u7e26\u6a2a\u3092\u5909\u63db\u3057\u3066\u51fa\u3059\u3088\u3046\u306a\u3053\u3068\u3002<\/p>\n\n\n\n<p>\u305f\u3068\u3048\u3070\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u3001\u30ab\u30e9\u30e0\u3067\u30c7\u30fc\u30bf\u304c\u5165\u3063\u3066\u3044\u305f\u3068\u3059\u308b\u3002<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>ID<\/th><th>\u65e5\u4ed8<\/th><th>\u91d1\u984d<\/th><th>\u79d1\u76ee<\/th><\/tr><\/thead><\/table><\/figure>\n\n\n\n<p>\u3053\u308c\u3092<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\u79d1\u76ee\u540d<\/th><th>4\u6708<\/th><th>5\u6708<\/th><th>6\u6708<\/th><\/tr><\/thead><\/table><\/figure>\n\n\n\n<p>\u3068\u3044\u3046\u3088\u3046\u306b\u51fa\u3057\u305f\u3044\u5834\u5408\u304c\u30af\u30ed\u30b9\u96c6\u8a08\u3067\u3042\u308b\u3002<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"head2\">\u5e74\u5ea6\u306e\u8868\u73fe<\/h1>\n\n\n\n<p>\u6982\u5ff5\u306f\u8aac\u660e\u3059\u308b\u307e\u3067\u3082\u306a\u3044\u304c\u3001\u65e5\u672c\u3067\u306f1,2,3\u6708\u306f\u524d\u306e\u5e74\u5ea6\u306b\u542b\u307e\u308c\u308b\u305f\u3081\u3001\u3053\u308c\u3092SQL\u3067\u66f8\u304f\u3002\u30ab\u30e9\u30e0\u5185\u306bcase\u3067\u66f8\u3051\u3070\u3088\u304f\u3001\u305d\u308c\u306bas\u3067alias\u540d\u3092\u3064\u3051\u3066Having\u3067\u7d5e\u308a\u8fbc\u3080\u3002<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nselect \n (\n   case when\n     month(datecol) &lt; 4\n   then\n     year(datecol)-1\n   else\n     year(datecol) end\n ) as nenndo\nfrom\n shiwakelist\nhaving nenndo=2014\n<\/pre><\/div>\n\n\n<p>alias \u540d\u306f\u3001where\u3092\u8a55\u4fa1\u3059\u308b\u969b\u306b\u751f\u6210\u3055\u308c\u3066\u3044\u306a\u3044\u305f\u3081\u3001having\u3067\u7d5e\u308a\u8fbc\u3080\u3002\u3042\u3068\u306f\u3001\u5fc5\u8981\u306b\u5fdc\u3058\u3066\u4fa1\u683c\u3084\u5e74\u5ea6\u3001\u6708\u3092\u51fa\u529b\u3057\u3066\u3084\u308c\u3070\u3088\u3044\u3002\u6708\u3084\u4fa1\u683c\u306f\u3001\u305d\u306e\u5f8c\u306eQuery\u306b\u4f7f\u3046\u306e\u3067\u8ffd\u52a0\u3057\u3066\u304a\u3051\u3070\u3088\u3044\u3002<\/p>\n\n\n\n<p>case\u306e\u66f8\u5f0f\u306f\u4ee5\u4e0b\u3002<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncase when \u6761\u4ef6 then \u52d5\u4f5c\uff11 else \u52d5\u4f5c\uff12\n<\/pre><\/div>\n\n\n<p>\u6761\u4ef6\u304c\u771f\u306a\u3089\u52d5\u4f5c\uff11\u3092\u5b9f\u884c\u3057\u3001\u507d\u306a\u3089\u6761\u4ef62\u3092\u5b9f\u884c\u3059\u308b\u3002<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">\u96c6\u8a08\u7d50\u679c<\/h1>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nselect\n kname,\n sum(case when m=&#039;04&#039; then price else 0 end) as 4\u6708,\n sum(case when m=&#039;05&#039; then price else 0 end) as 5\u6708,\n sum(case when m=&#039;06&#039; then price else 0 end) as 6\u6708,\n sum(case when m=&#039;07&#039; then price else 0 end) as 7\u6708,\n sum(case when m=&#039;08&#039; then price else 0 end) as 8\u6708,\n sum(case when m=&#039;09&#039; then price else 0 end) as 9\u6708,\n sum(case when m=&#039;10&#039; then price else 0 end) as 10\u6708,\n sum(case when m=&#039;11&#039; then price else 0 end) as 11\u6708,\n sum(case when m=&#039;12&#039; then price else 0 end) as 12\u6708,\n sum(case when m=&#039;01&#039; then price else 0 end) as 1\u6708,\n sum(case when m=&#039;02&#039; then price else 0 end) as 2\u6708,\n sum(case when m=&#039;03&#039; then price else 0 end) as 3\u6708,\n sum(price) as price\nfrom\n(\n  SELECT\n   (\n     case when\n       month(f.date) &lt; 4\n     then\n       year(f.date)-1\n     else\n       year(f.date) end\n   ) as nenndo,\n   month(f.date) as m,\n   f.price,\n   f.kname\n  FROM\n   vhrfurikae as f\n  where\n   f.isKashi=0\n   having nenndo=2014\n   order by year(f.date),m\n ) as s\n\ngroup by kname;\n<\/pre><\/div>\n\n\n<p>\u3000\u7d50\u679c\u304c\u3053\u308c\u3002\u30b5\u30d6\u30af\u30a8\u30ea\u304c\u3001\u5e74\u5ea6\u3067\u7d5e\u308a\u8fbc\u3080\u305f\u3081\u306e\u5e74\u5ea6\u3092\u542b\u3080\u5143\u30c7\u30fc\u30bf\u3092\u751f\u6210\u3059\u308b\u3002\u305d\u308c\u3092kname\uff08\u79d1\u76ee\u540d\uff09\u3067Group\u3059\u308b\u3053\u3068\u3067\u4e00\u3064\u306e\u30ab\u30e9\u30e0\u306b\u540c\u4e00\u79d1\u76ee\u540d\u306e\u30ab\u30e9\u30e0\u304c\u96c6\u5408\u3059\u308b\u3002\u305d\u308c\u3092\u5404\u30ab\u30e9\u30e0\u306b\u632f\u308a\u5206\u3051\u3059\u308b\u305f\u3081\u306b\u3001\u30b5\u30d6\u30af\u30a8\u30ea\u4ee5\u5916\u306e\u90e8\u5206\u3067\u306ecase\u3092\u66f8\u3044\u3066\u3044\u308b\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u30b5\u30d6\u30af\u30a8\u30ea\u306e\u8aac\u660e<\/h2>\n\n\n\n<p>vhrfurikae\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u30ab\u30e9\u30e0\u3092\u542b\u3093\u3067\u3044\u308b<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>ID<\/td><td>isKashi(\u8cb8\u65b9\u30d5\u30e9\u30b0)<\/td><td>price\uff08\u91d1\u984d\uff09<\/td><td>date\uff08\u65e5\u4ed8\uff09<\/td><td>kname\uff08\u79d1\u76ee\u540d\uff09<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>\u3053\u306e\u72b6\u614b\u3067\u3001\u65e5\u4ed8\u3088\u308acase\u3092\u3064\u304b\u3063\u3066\u5e74\u5ea6\u3092\u751f\u6210\u3059\u308b\u3002\u3053\u308c\u3092\u5143\u306b\u7d5e\u308a\u8fbc\u307f\u3057\u305f\u3002\u66f8\u3044\u3066\u3044\u308b\u6642\u70b9\u3067\u306f\u3001\u65e5\u4ed8\u3092between\u3067\u6307\u5b9a\u3057\u3066\u3082\u826f\u3044\u6c17\u304c\u3059\u308b\u304c\u3001\u5f53\u6642\u306a\u305c\u5e74\u5ea6\u3092\u308f\u3056\u308f\u3056\u751f\u6210\u3057\u305f\u306e\u304b\u601d\u3044\u51fa\u305b\u306a\u3044\u3002<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nwhere dttm between &#039;2017-04-01&#039; and &#039;2018-03-31&#039;\n<\/pre><\/div>\n\n\n<p>\u6700\u7d42\u7684\u306b\u306f\u3001\u6307\u5b9a\u5e74\u5ea6\u306e\u60c5\u5831\u3092\u53d6\u308a\u51fa\u3059\u3053\u3068\u3092\u5148\u306b\u30b5\u30d6\u30af\u30a8\u30ea\u3067\u884c\u3063\u3066\u3044\u308b\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u30af\u30ed\u30b9\u96c6\u8a08\u306e\u51fa\u529b<\/h2>\n\n\n\n<p>\u6700\u7d42\u7684\u306b\u3001\u30ab\u30e9\u30e0\u3068\u3057\u3066\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u3057\u305f\u3044\u5834\u5408<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\u79d1\u76ee\u540d<\/th><th>4\u6708<\/th><th>5\u6708<\/th><th>6\u6708<\/th><\/tr><\/thead><\/table><\/figure>\n\n\n\n<p>SQL\u306f\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u64ec\u4f3c\u7684\u306aSQL\u306b\u306a\u308b\u3079\u304d\u3067\u3042\u308b<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nselect\n  \u79d1\u76ee\u540d,\n\u30004\u6708\u306e\u5408\u8a08\u5024,\n\u30005\u6708\u306e\u5408\u8a08\u5024,\n\u3000\u3000\u3000\uff1a\n\u300012\u6708\u306e\u5408\u8a08\u5024\nfrom\n  table\n<\/pre><\/div>\n\n\n<p>\u3059\u306a\u308f\u3061\u30b5\u30d6\u30af\u30a8\u30ea\u304b\u3089\u53d6\u308a\u51fa\u3057\u305f1\u30641\u3064\u306e\u30a8\u30f3\u30c8\u30ea\u3092\u5404\u6708\u306e\u30ab\u30e9\u30e0\u306b\u96c6\u8a08\u3057\u3066\u3044\u304f\u5fc5\u8981\u304c\u3042\u308b\u3002<\/p>\n\n\n\n<p>\u305d\u308c\u304c\u4ee5\u4e0b\u306e\u884c\u3067\u3042\u308b<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nsum(case when m=&#039;04&#039; then price else 0 end) as 4\u6708,\n<\/pre><\/div>\n\n\n<p>\u3053\u308c\u306f\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u610f\u5473\u3092\u6301\u3064\u3002<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>1\u3064\u306e\u30a8\u30f3\u30c8\u30ea\u306e\u6708\u304c4\u6708\u3060\u3063\u305f\u5834\u5408\u306b\u306f\u305d\u306eprice\u5024\u3092\u52a0\u7b97\u3057\u305d\u308c\u30924\u6708\u3068\u3044\u3046\u30ab\u30e9\u30e0\u3067\u5229\u7528\u3059\u308b\u3002\u307e\u305f\u6708\u304c4\u6708\u3067\u306a\u3044\u5834\u5408\u306b\u306f\u3001\u5024\u3092\uff10\u3068\u3057\u3066\u52a0\u7b97\u3059\u308b\u3002<\/p><\/blockquote>\n\n\n\n<p>\u3053\u308c\u309212\u30f6\u6708\u5206\u66f8\u3051\u3070\u30af\u30ed\u30b9\u96c6\u8a08\u3067\u304d\u308b\u3002\u30a4\u30e1\u30fc\u30b8\u3068\u3057\u3066\u306f\u3001\u30b5\u30d6\u30af\u30a8\u30ea\u3067\u53d6\u308a\u51fa\u3055\u308c\u305f\u5358\u4e00\u5e74\u5ea6\u306e\u30c7\u30fc\u30bf\u306e\u3046\u3061\u306e1\u884c\u304ccase\u6587\u306e\u6761\u4ef6\u306b\u5f15\u3063\u304b\u304b\u308a\u305d\u3053\u306b\u5408\u7b97\u3055\u308c\u3066\u3044\u304f\u3088\u3046\u306a\u611f\u3058\u3067\u3042\u308b\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3053\u306e\u4f8b\u3067\u306f\u4f1a\u793e\u306e\u7d4c\u7406\u3092\u4f8b\u3068\u3057\u3066\u3044\u307e\u3059\u3002\u7d4c\u7406\u3067\u306f\u4f1a\u793e\u3078\u306e\u5165\u51fa\u91d1\u3092\u4ed5\u8a33\u4f1d\u7968\u3068\u8a00\u3046\u5358\u4f4d <a href=\"https:\/\/www.fatware.jp\/?page_id=338\" class=\"read-more\">\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":363,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-338","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.fatware.jp\/index.php?rest_route=\/wp\/v2\/pages\/338","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fatware.jp\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.fatware.jp\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.fatware.jp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fatware.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=338"}],"version-history":[{"count":10,"href":"https:\/\/www.fatware.jp\/index.php?rest_route=\/wp\/v2\/pages\/338\/revisions"}],"predecessor-version":[{"id":531,"href":"https:\/\/www.fatware.jp\/index.php?rest_route=\/wp\/v2\/pages\/338\/revisions\/531"}],"up":[{"embeddable":true,"href":"https:\/\/www.fatware.jp\/index.php?rest_route=\/wp\/v2\/pages\/363"}],"wp:attachment":[{"href":"https:\/\/www.fatware.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=338"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}