SQL:选择特定的值输出
问题描述:
我对mySQL相当陌生,但真的很绝望。请尽量让我容易。SQL:选择特定的值输出
我想创建一个关于酒店的报告。现在,我的查询可以告诉我,有多少次预订了多少次不同的旅程。
举个例子:
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
预先感谢您! :)
答
您可以使用整数除法的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
固定范围或者根据用户的输入而改变? –
他们是固定的,但最终我需要五个不同的范围。 – Vivess