mysql - SQL : selection of specific value outputs

Question

我对 mySQL 相当陌生，但真的很绝望。请尽量对我放松一点。

我想创建一份有关酒店的报告。现在，我的查询可以显示预订了多少个不同的旅程以及多少晚。

举个例子:

SELECT i.numberofnights,
    SUM(CASE WHEN i.nod >='1' THEN '1' ELSE '0' END) as journeyCount
    FROM itinerary i
    WHERE i.arrival BETWEEN '2015-01-01' AND '2015-01-31'
    GROUP BY i.numberofnights

我的输出:

NoN   |   journeyCount
1     |   18
2     |   6
3     |   4
4     |   13
5     |   12
6     |   5
7     |   9

它显示我有 18 个 1 晚的旅程，6 个 2 晚的旅程，依此类推。我的愿望是做出选择，因此将 1 至 3 晚和 4 至 7 晚的所有结果合并起来。请帮助我!

我想要的输出:

NoN   |  journeyCount
1-3   |  28
4-7   |  39

先谢谢你了!! :)

Answer 1

您可以在 GROUP BY 中使用整数除法子句:

SELECT (i.numberofnights-1) DIV 3,
       SUM(CASE WHEN i.nod >=1 THEN 1 ELSE 0 END) as journeyCount
FROM itinerary i
WHERE i.arrival BETWEEN '2015-01-01' AND '2015-01-31'
GROUP BY (i.numberofnights-1) DIV 3

如果您想要仅两个段，其中一个用于<=3另一个为 > 3 ，那么你可以使用:

SELECT IF(CASE WHEN i.numberofnights <= 3 THEN 0 ELSE 1 END = 0, '1-3', '4-7') AS NoN,
       SUM(CASE WHEN i.nod >=1 THEN 1 ELSE 0 END) as journeyCount
FROM itinerary i
WHERE i.arrival BETWEEN '2015-01-01' AND '2015-01-31'
GROUP BY CASE WHEN i.numberofnights <= 3 THEN 0 ELSE 1 END

编辑:

要获取“1-3”、“4-6”等存储桶标签，您可以使用以下查询:

SELECT CONCAT(((i.numberofnights-1) DIV 3 + 1) * 3 - 2, 
              '-', 
              ((i.numberofnights-1) DIV 3 + 1) * 3) AS NoN, 
       SUM(CASE WHEN i.nod >=1 THEN 1 ELSE 0 END) as journeyCount
FROM itinerary i
WHERE i.arrival BETWEEN '2015-01-01' AND '2015-01-31'
GROUP BY (i.numberofnights-1) DIV 3

Demo here