SQL中級CASE文
13500 ワード
CASE
CASE
WHEN 조건1 THEN 결과값1
WHEN 조건2 THEN 결과값2
WHEN 조건N THEN 결과값N
ELSE 결과값
END
例1:
SELECT CASE
WHEN categoryid =1 AND SupplierID = 1 THEN '음료'
WHEN categoryid =2 THEN '조미료'
ELSE '기타'
END AS 'categoryName', *
FROM Products
結果:Pivoting:
例2:
カテゴリ1,2,3回の平均価格を見たいだけです.SELECT AVG(CASE WHEN categoryid =1 THEN price ELSE NULL END) AS category1_price,
AVG(CASE WHEN categoryid =2 THEN price ELSE NULL END) AS category2_price,
AVG(CASE WHEN categoryid =3 THEN price ELSE NULL END) AS category3_price
FROM Products
SELECT AVG(CASE WHEN categoryid =1 THEN price ELSE NULL END) AS category1_price,
AVG(CASE WHEN categoryid =2 THEN price ELSE NULL END) AS category2_price,
AVG(CASE WHEN categoryid =3 THEN price ELSE NULL END) AS category3_price
FROM Products
ハッカーランキング問題:Triangleタイプ
-三角形の条件:2つのエッジの長さの和>別のエッジの長さ
-正三角形条件:3辺がそれぞれA、B、Cの場合、A=BおよびD B=C.
-等辺三角形条件:A=BまたはB=CまたはA=C
SELECT CASE WHEN A=B AND B=C THEN 'Equilateral'
WHEN A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'
WHEN A=B OR B=C OR A=C THEN 'Isosceles'
ELSE 'Scalene'
END
FROM triangles
結果:Letcode問題:1179。Reformat Department Table
https://leetcode.com/problems/reformat-department-table/
考慮すべき事項:
-select文(表示印刷)id、Jan Revenue、...、Dec Revenueに入ります.
-ここで、Jan RevenueはAS関数でランダムに作成されたようです.
-CASE文:when=「毎月」THEN収入.
-SUMを使用:1番部門(id)の毎月の収益を表示したいので、case文をSUMにバインドします.
-GROUP BY:各部署にあるのでGROUP BY IDを書きます.
SELECT id,
SUM(CASE WHEN month='Jan' THEN revenue ELSE NULL END) AS Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue ELSE NULL END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue ELSE NULL END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue ELSE NULL END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue ELSE NULL END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue ELSE NULL END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue ELSE NULL END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue ELSE NULL END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue ELSE NULL END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
FROM department
GROUP BY id
Reference
この問題について(SQL中級CASE文), 我々は、より多くの情報をここで見つけました https://velog.io/@aza425/SQL-중급CASE-문テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol