假设有一张表:
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
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