【MySQL】使用 UNION ALL 合并多个 SQL 查询出来的字段（思路）

模拟表（canteen）

字段	含义
consume_time	消费时间
name	姓名
amount	消费金额

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

已知

	开始时间	结束时间
上午	00:00:00	09:00:00
中午	09:00:00	16:00:00
下午	16:00:00	23:59:59

查询的字段名

字段	含义
amNum	早餐用餐人数
noonNum	中午用餐人数
pmNum	晚餐用餐人数
diningNum	用餐总人数
diningAmount	用餐总金额

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