mysql - SQL 查询没有结果

Question

我正在尝试计算一定范围内的客人入住天数。少于 7 晚、7 至 14 晚、最后超过 14 晚。

我希望输出看起来像这样:

Range         | Count
----------------------
<= 7 Nights   | 3
8 - 14 Nights | 2
15 Nights +   | 1

我正在使用MYSQL，并尝试了以下两种解决方案，但似乎没有结果出现。

SELECT  
CASE
     WHEN booking_num_nights <= 7 THEN '<= 7 Nights'
     WHEN booking_num_nights > 7 and booking_num_nights <= 14 THEN '8 - 14 Nights'
     WHEN booking_num_nights > 14 THEN '15 Nights +'
  END AS range, count(*) AS count_num
FROM BOOKING
    WHERE booking_property_id = :id
    GROUP BY range

另一种尝试如下:

SELECT booking_num_nights as range, count(*) as count_num
FROM (SELECT 
    CASE
        WHEN booking_num_nights <= 7 THEN '<= 7 Nights'
        WHEN booking_num_nights > 7 and booking_num_nights <= 14 THEN '8 - 14 Nights'
        WHEN booking_num_nights > 14 THEN '15 Nights +'
    END AS range
 FROM BOOKING) 
WHERE booking_property_id = :id
GROUP BY range

Answer 1

范围是 reserved word 。使用不同的名称或转义它:

SELECT  
    CASE
        WHEN booking_num_nights <= 7 THEN '<= 7 Nights'
        WHEN booking_num_nights > 7 and booking_num_nights <= 14 THEN '8 - 14 Nights'
        WHEN booking_num_nights > 14 THEN '15 Nights +'
    END AS `range`, count(*) AS count_num
FROM BOOKING
WHERE booking_property_id = :id
GROUP BY `range`

DEMO