t-sql中case的用法-优快云博客

本文通过创建示例表并插入数据，介绍了SQL中CASE表达式的使用方法，包括基本使用、结合GROUP BY进行分组统计及排序操作。这些技巧有助于更好地理解和运用CASE语句解决实际问题。

假设有一张表：
CREATE TABLE a
(
id INT,
name VARCHAR(20),
)
INSERT INTO a VALUES(1,'a')
INSERT INTO a VALUES(2,'b')
INSERT INTO a VALUES(3,'c')
INSERT INTO a VALUES(4,'d')
INSERT INTO a VALUES(5,'e')
INSERT INTO a VALUES(6,'a')
INSERT INTO a VALUES(7,'b')
INSERT INTO a VALUES(8,'c')
INSERT INTO a VALUES(9,'d')
INSERT INTO a VALUES(10,'e')

最常用的用法：
SELECT
CASE
WHEN id < 3 THEN '<3'
WHEN id >=3 AND id<6 THEN '>3 <6'
WHEN id >=6 THEN '>6'
ELSE 'none'
END AS [RANGE],name FROM a

来点有意义的用法：
SELECT
CASE
WHEN id < 3 THEN '<3'
WHEN id >=3 AND id<6 THEN '>3 <6'
WHEN id >=6 THEN '>6'
ELSE 'none'
END AS [RANGE]
, count(*) AS [num] FROM a
GROUP BY
CASE
WHEN id < 3 THEN '<3'
WHEN id >=3 AND id<6 THEN '>3 <6'
WHEN id >=6 THEN '>6'
ELSE 'none'
END
切记：为了在 GROUP BY 块中使用 CASE，查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。
加上排序：
SELECT
CASE
WHEN id < 3 THEN '<3'
WHEN id >=3 AND id<6 THEN '>3 <6'
WHEN id >=6 THEN '>6'
ELSE 'none'
END AS [RANGE]
, count(*) AS [num] FROM a
GROUP BY
CASE
WHEN id < 3 THEN '<3'
WHEN id >=3 AND id<6 THEN '>3 <6'
WHEN id >=6 THEN '>6'
ELSE 'none'
END
ORDER BY num desc