{"id":881,"date":"2021-10-13T18:13:13","date_gmt":"2021-10-13T09:13:13","guid":{"rendered":"https:\/\/suzutukiblog.com\/?p=881"},"modified":"2022-10-08T00:30:45","modified_gmt":"2022-10-07T15:30:45","slug":"mysql16","status":"publish","type":"post","link":"https:\/\/suzutukiblog.com\/index.php\/2021\/10\/13\/mysql16\/","title":{"rendered":"MySQL(\u305d\u306e16 \u6761\u4ef6\u5206\u5c90(case))"},"content":{"rendered":"

15\u304b\u3089\u7d9a\u304d\u307e\u3059\u3002<\/a><\/p>\n

1:\u6761\u4ef6\u5206\u5c90case\u3068\u306f\uff1f<\/h3>\n

SQL\u3067\u5834\u5408\u5206\u3051\u3092\u8a18\u8ff0\u3059\u308b\u969b\u306b\u4f7f\u3046\u3002\u5834\u5408\u5206\u3051\u3092\u3001\u6761\u4ef6\u5206\u5c90\u3068\u3088\u3076\u3002<\/p>\n

\u4f8b\uff1a\u30e6\u30fc\u30b6\u30fc\u306e\u30a2\u30af\u30c6\u30a3\u30d3\u30c6\u30a3\u5ea6\u5408\u3044\u306b\u3088\u3063\u3066\u65bd\u7b56\u3092\u5909\u3048\u305f\u3044\u306e\u3067
\n\u30e6\u30fc\u30b6\u30fc\u3092\u7d2f\u8a08\u6ce8\u6587\u56de\u6570\u3067\u30e9\u30f3\u30af\u5206\u3051\u3057\u3066\u30e9\u30f3\u30af\u304c\u9ad8\u3044\u9806\u306b\u3057\u305f\u3044\uff01
\nA 5\u56de\u4ee5\u4e0a B 2\u56de\u4ee5\u4e0a C 1\u56de<\/h3>\n

<\/p>\n

\u88dc\u8db3\uff1a\u6ce8\u6587\u56de\u65700\u56de\u306e\u30e6\u30fc\u30b6\u30fc\u306f\u51fa\u529b\u4e0d\u8981\u3002\u5fc5\u8981\u306a\u60c5\u5831\u306f\u30e6\u30fc\u30b6\u30fcid,\u7d2f\u8a08\u6ce8\u6587\u56de\u6570,\u30e6\u30fc\u30b6\u30fc\u30e9\u30f3\u30af(A or B or C)<\/p>\n

select\r\nu.id,\r\ncount(*) as num,\r\ncase<\/strong>\r\nwhen count(*)>= 5 then 'A'<\/strong>\r\nwhen count(*)>= 2 then 'B'<\/strong>\r\nelse'C'<\/strong>\r\nend as user_rank\r\nfrom users as u\r\ninner join\r\norders as o\r\non u.id = o.user_id\r\ngroup by\r\nu.id\r\norder by user_rank;<\/pre>\n
\"\"<\/p>\n
\u203b\u30dd\u30a4\u30f3\u30c8<\/h3>\n
\u7d2f\u8a08\u6ce8\u6587\u56de\u6570\u3092num\u3068\u3057\u305f\u3002group by\u3067\u6ce8\u6587\u56de\u6570\u3092\u96c6\u8a08<\/p>\n
order by\u3067\u30e6\u30fc\u30b6\u30fc\u30e9\u30f3\u30af\u304c\u9ad8\u3044\u9806\u306b<\/p>\n
\u53d6\u5f97\u5024null\u30920\u306b\u7f6e\u304d\u63db\u3048\u308b<\/h3>\n
select\r\np.id,p.name,\r\nsum(od.product_qty)as num\r\nfrom\r\nproducts p left outer join order_details od\r\non p.id = od.product_id\r\ngroup by p.id;<\/pre>\n
\"\"<\/p>\n
null\u3092\uff10\u306b\u7f6e\u304d\u63db\u3048\u305f\u3044\uff01<\/p>\n
select\r\np.id,p.name,\r\ncase<\/strong>\r\nwhen sum(od.product_qty)is null then 0<\/strong>\r\nelse sum(od.product_qty)<\/strong>\r\nend as num<\/strong>\r\nfrom\r\nproducts p\r\nleft outer join\r\norder_details od\r\non p.id = od.product_id\r\ngroup by p.id;<\/pre>\n
\"\"<\/p>\n
0\u306b\u7f6e\u304d\u63db\u308f\u3063\u305f\uff01<\/p>\n
\u5fdc\u75281)\u5168\u5546\u54c1\u3092\u7d2f\u8a08\u8ca9\u58f2\u500b\u6570\u3067\u30e9\u30f3\u30af\u5206\u3051\u3057\u305f\u3044\uff01<\/h3>\n
\u88dc\u8db3\uff1aA 20\u500b\u4ee5\u4e0a B 10\u500b\u4ee5\u4e0a C 10\u500b\u672a\u6e80 \u30e9\u30f3\u30af\u304c\u9ad8\u3044\u9806\u306b\u4e26\u3073\u66ff\u3048\u308b
\n\u5fc5\u8981\u306a\u5217\u306f\u5546\u54c1 ID \u5546\u54c1\u540d \u8ca9\u58f2\u500b\u6570 \u30e9\u30f3\u30afER\u56f3<\/a>\u3092\u53c2\u8003\u306b\u3059\u308b<\/p>\n
select\r\np.id,p.name,sum(product_qty),\r\ncase<\/strong>\r\nwhen sum(product_qty) >= 20 then 'A'<\/strong>\r\nwhen sum(product_qty) >= 10 then 'B'<\/strong>\r\nelse 'C'<\/strong>\r\nend as r<\/strong>\r\nfrom\r\nproducts as p\r\nleft outer join\r\norder_details as od\r\non p.id = od.product_id\r\ngroup by p.id\r\norder by r;<\/pre>\n
\"\"<\/p>\n
\u203b\u30dd\u30a4\u30f3\u30c8<\/h3>\n
Rank\u306f\u4e88\u7d04\u8a9e\u3067\u4f7f\u3048\u306a\u3044\u305f\u3081r\u3068\u3057\u305f\u3002product_qty\u306f\u8ca9\u58f2\u500b\u6570\u3067\u3001group by\u3067product_id\u3092\u30b0\u30eb\u30fc\u30d7\u5316\u3057\u3066\u3001sum\u3067\u7d2f\u8a08\u8ca9\u58f2\u500b\u6570\u3092\u5c0e\u304d\u51fa\u3057\u305f\u3002<\/p>\n
\u305d\u306e17\u306b\u7d9a\u304d\u307e\u3059\u3002<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
15\u304b\u3089\u7d9a\u304d\u307e\u3059\u3002 1:\u6761\u4ef6\u5206\u5c90case\u3068\u306f\uff1f SQL\u3067\u5834\u5408\u5206\u3051\u3092\u8a18\u8ff0\u3059\u308b\u969b\u306b\u4f7f\u3046\u3002\u5834\u5408\u5206\u3051\u3092\u3001\u6761\u4ef6\u5206\u5c90\u3068\u3088\u3076\u3002 \u4f8b\uff1a\u30e6\u30fc\u30b6\u30fc\u306e\u30a2\u30af\u30c6\u30a3\u30d3\u30c6\u30a3\u5ea6\u5408\u3044\u306b\u3088\u3063\u3066\u65bd\u7b56\u3092\u5909\u3048\u305f\u3044\u306e\u3067 \u30e6\u30fc\u30b6\u30fc\u3092\u7d2f\u8a08\u6ce8\u6587\u56de\u6570\u3067\u30e9\u30f3\u30af\u5206\u3051\u3057\u3066\u30e9\u30f3\u30af […]<\/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":[5,27],"tags":[],"class_list":["post-881","post","type-post","status-publish","format-standard","hentry","category-mysql","category-programming-note"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t