mysql - 是否可以为 MySQL 查询自定义 GROUP BY？

Question

我一直在努力寻找最简单的方法来对查询中的两个年龄组进行分组。这样的事情有可能奏效吗？

SELECT age,
sum(case when age < '20' then 1 else 0 end),
sum(case when age > '20' then 1 else 0 end)
FROM Contact
GROUP BY ...."custom group one"......"custom group one".....??

我知道你通常应该在一个列上分组，但在我的情况下，我不这样做。有什么建议么？谢谢!

Table:            Desired Query Result:
Name    Age       0           1
John    18        Under 20    2
Harry   22        Over 20     2
Mary    17
Megan   27

---

已解决:

SELECT CASE
  WHEN age = '21' THEN 'young'
  WHEN  age BETWEEN '22' AND '60' THEN 'middle'                          
  ELSE 'old'
END, Count(id)
FROM  Contact
GROUP BY CASE
  WHEN age = '21' THEN 'young'
  WHEN  age BETWEEN '22' AND '60' THEN 'middle'                       
  ELSE 'old'
END

注意:AS 可用于为SELECT 语句中的分组条件分配别名，从而避免重复条件两次，即

SELECT CASE
  WHEN age = '21' THEN 'young'
  WHEN  age BETWEEN '22' AND '60' THEN 'middle'                          
  ELSE 'old'
END AS age_range, Count(id)
FROM  Contact
GROUP BY age_range

Answer 1

那就是:

SELECT 
  COUNT(1), 
  age>20 AS Above20 
FROM t 
GROUP BY age>20

-检查这个fiddle .

或者，使用 SUM() 和列 View :

SELECT
  SUM(IF(age>20, 1, 0)) AS Above20,
  SUM(IF(age<=20, 1, 0)) AS Below20
FROM 
  t

-检查这个fiddle .