【MySQL】使用 UNION ALL 合并多个 SQL 查询出来的字段（思路）_union后根据指定字段合并

模拟表（canteen）

编写 SQL 求出早餐、午餐、晚餐用餐人数，以及用餐总人数和用餐总金额

已知

查询的字段名

SQL 编写（开始时间和结束时间格式yyyy-MM-dd）

SELECT SUM(amNum) AS amNum,
	   SUM(noonNum) AS noonNum,
	   SUM(pmNum) AS pmNum,
	   SUM(diningNum) AS diningNum,
	   SUM(diningAmount) AS diningAmount
FROM (
	SELECT COUNT(*) AS amNum,
    	   NULL AS noonNum,
    	   NULL AS pmNum,
	   	   NULL AS diningNum,
	   	   NULL AS diningAmount
    FROM canteen
    WHERE consume_time >= 开始时间 AND consume <= DATEADD(DAY, 1, 结束时间)
      AND CONVERT(VARCHAR, consume_time, 8) BETWEEN '00:00:00' AND '09:00:00'
    
    UNION ALL
    
    SELECT NULL AS amNum,
    	   COUNT(*) AS noonNum,
    	   NULL AS pmNum,
	   	   NULL AS diningNum,
	   	   NULL AS diningAmount
    FROM canteen
    WHERE consume_time >= 开始时间 AND consume <= DATEADD(DAY, 1, 结束时间)
      AND CONVERT(VARCHAR, consume_time, 8) BETWEEN '09:00:00' AND '16:00:00'
    
    UNION ALL
    
    SELECT NULL AS amNum,
    	   NULL AS noonNum,
    	   COUNT(*) AS pmNum,
	   	   NULL AS diningNum,
	   	   NULL AS diningAmount
    FROM canteen
    WHERE consume_time >= 开始时间 AND consume <= DATEADD(DAY, 1, 结束时间)
      AND CONVERT(VARCHAR, consume_time, 8) BETWEEN '16:00:00' AND '23:59:59'
    
    UNION ALL
    
    SELECT NULL AS amNum,
    	   NULL AS noonNum,
    	   NULL AS pmNum,
	   	   COUNT(*) AS diningNum,
	   	   ISNULL(ABS(SUM(amount), 0)) AS diningAmount
    FROM canteen
    WHERE consume_time >= 开始时间 AND consume <= DATEADD(DAY, 1, 结束时间)
) AS canteenBoard
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47