为什么有时我在列中得到最后一个变量，有时在列中得到所有不同的变量？

Question

我在做YELP数据集工作.这件事很难解释，但我会尽力的。我正试着把所有的餐馆都从一个城市开进来--这里是凤凰城。当我在下面运行查询时

SELECT b.city
,c.category
,b.name
,b.review_count
,h.hours
,CASE 
    WHEN h.hours LIKE 'Monday%'
        THEN 1
    WHEN h.hours LIKE 'Tuesday%'
        THEN 2
    WHEN h.hours LIKE 'Wednesday%'
        THEN 3
    WHEN h.hours LIKE 'Thursday%'
        THEN 4
    WHEN h.hours LIKE 'Friday%'
        THEN 5
    WHEN h.hours LIKE 'Saturday%'
        THEN 6
    WHEN h.hours LIKE 'Sunday%'
        THEN 7
    END AS day
,CASE 
    WHEN b.stars between 2
        AND 3
        THEN '2-3 stars'
    WHEN b.stars between 4
        AND 5
        THEN '4-5 stars'
    END AS star
FROM business b
INNER JOIN category c ON b.id = c.business_id
INNER JOIN hours h ON b.id = h.business_id
WHERE TRIM(c.category) = 'Restaurants'
    AND TRIM(b.city) = 'Phoenix'
    AND star IS NOT NULL

我得到的结果是：

+---------+-------------+----------------------------------------+--------------+-----------------------+-----+-----------+
| Phoenix | Restaurants | McDonald's                             |            8 | Monday|5:00-23:00     |   1 | 2-3 stars |
| Phoenix | Restaurants | McDonald's                             |            8 | Tuesday|5:00-23:00    |   2 | 2-3 stars |
| Phoenix | Restaurants | McDonald's                             |            8 | Friday|5:00-0:00      |   5 | 2-3 stars |
| Phoenix | Restaurants | McDonald's                             |            8 | Wednesday|5:00-23:00  |   3 | 2-3 stars |
| Phoenix | Restaurants | McDonald's                             |            8 | Thursday|5:00-23:00   |   4 | 2-3 stars |
| Phoenix | Restaurants | McDonald's                             |            8 | Sunday|5:00-23:00     |   7 | 2-3 stars |
| Phoenix | Restaurants | McDonald's                             |            8 | Saturday|5:00-0:00    |   6 | 2-3 stars |
| Phoenix | Restaurants | Charlie D's Catfish & Chicken          |            7 | Monday|11:00-18:00    |   1 | 4-5 stars |
| Phoenix | Restaurants | Charlie D's Catfish & Chicken          |            7 | Tuesday|11:00-18:00   |   2 | 4-5 stars |
| Phoenix | Restaurants | Charlie D's Catfish & Chicken          |            7 | Friday|11:00-18:00    |   5 | 4-5 stars |
| Phoenix | Restaurants | Charlie D's Catfish & Chicken          |            7 | Wednesday|11:00-18:00 |   3 | 4-5 stars |
| Phoenix | Restaurants | Charlie D's Catfish & Chicken          |            7 | Thursday|11:00-18:00  |   4 | 4-5 stars |
| Phoenix | Restaurants | Charlie D's Catfish & Chicken          |            7 | Sunday|13:00-16:00    |   7 | 4-5 stars |
| Phoenix | Restaurants | Charlie D's Catfish & Chicken          |            7 | Saturday|11:00-18:00  |   6 | 4-5 stars |
| Phoenix | Restaurants | Gallagher's                            |           60 | Monday|11:00-0:00     |   1 | 2-3 stars |
| Phoenix | Restaurants | Gallagher's                            |           60 | Tuesday|11:00-0:00    |   2 | 2-3 stars |
| Phoenix | Restaurants | Gallagher's                            |           60 | Friday|11:00-2:00     |   5 | 2-3 stars |
| Phoenix | Restaurants | Gallagher's                            |           60 | Wednesday|11:00-0:00  |   3 | 2-3 stars |
| Phoenix | Restaurants | Gallagher's                            |           60 | Thursday|11:00-2:00   |   4 | 2-3 stars |
| Phoenix | Restaurants | Gallagher's                            |           60 | Sunday|9:00-0:00      |   7 | 2-3 stars |
| Phoenix | Restaurants | Gallagher's                            |           60 | Saturday|9:00-2:00    |   6 | 2-3 stars |
| Phoenix | Restaurants | Bootleggers Modern American Smokehouse |          431 | Monday|11:00-22:00    |   1 | 4-5 stars |
| Phoenix | Restaurants | Bootleggers Modern American Smokehouse |          431 | Tuesday|11:00-22:00   |   2 | 4-5 stars |
| Phoenix | Restaurants | Bootleggers Modern American Smokehouse |          431 | Friday|11:00-22:00    |   5 | 4-5 stars |
| Phoenix | Restaurants | Bootleggers Modern American Smokehouse |          431 | Wednesday|11:00-22:00 |   3 | 4-5 stars |
+---------+-------------+----------------------------------------+--------------+-----------------------+-----+-----------+
(Output limit exceeded, 25 of 28 total rows shown)

因此，为了很好地看到开放时间，我将为每天创建新的列，下面的查询如下：

SELECT b.city
    ,c.category
    ,b.name
    ,b.review_count
    ,MAX(CASE 
            WHEN H.hours LIKE "%monday%"
                THEN TRIM(H.hours, '%MondayTuesWednesThursFriSatSun|%')
            END) AS monday_hours
    ,MAX(CASE 
            WHEN H.hours LIKE "%tuesday%"
                THEN TRIM(H.hours, '%MondayTuesWednesThursFriSatSun|%')
            END) AS tuesday_hours
    ,MAX(CASE 
            WHEN H.hours LIKE "%wednesday%"
                THEN TRIM(H.hours, '%MondayTuesWednesThursFriSatSun|%')
            END) AS wednesday_hours
    ,MAX(CASE 
            WHEN H.hours LIKE "%thursday%"
                THEN TRIM(H.hours, '%MondayTuesWednesThursFriSatSun|%')
            END) AS thursday_hours
    ,MAX(CASE 
            WHEN H.hours LIKE "%friday%"
                THEN TRIM(H.hours, '%MondayTuesWednesThursFriSatSun|%')
            END) AS friday_hours
    ,MAX(CASE 
            WHEN H.hours LIKE "%saturday%"
                THEN TRIM(H.hours, '%MondayTuesWednesThursFriSatSun|%')
            END) AS saturday_hours
    ,MAX(CASE 
            WHEN H.hours LIKE "%sunday%"
                THEN TRIM(H.hours, '%MondayTuesWednesThursFriSatSun|%')
            END) AS sunday_hours
    ,CASE 
        WHEN b.stars BETWEEN 2
                AND 3
            THEN '2-3 stars'
        WHEN b.stars BETWEEN 4
                AND 5
            THEN '4-5 stars'
        END AS star
FROM business b
LEFT JOIN category c ON b.id = c.business_id
LEFT JOIN hours h ON b.id = h.business_id
WHERE TRIM(c.category) = 'Restaurants'
    AND TRIM(b.city) = 'Phoenix'

我得到了：

+---------+-------------+-------------+--------------+--------------+---------------+-----------------+----------------+--------------+----------------+--------------+-----------+
| city    | category    | name        | review_count | monday_hours | tuesday_hours | wednesday_hours | thursday_hours | friday_hours | saturday_hours | sunday_hours | star      |
+---------+-------------+-------------+--------------+--------------+---------------+-----------------+----------------+--------------+----------------+--------------+-----------+
| Phoenix | Restaurants | Gallagher's |           60 | 5:00-23:00   | 5:00-23:00    | 5:00-23:00      | 5:00-23:00     | 5:00-0:00    | 9:00-2:00      | 9:00-0:00    | 2-3 stars |
+---------+-------------+-------------+--------------+--------------+---------------+-----------------+----------------+--------------+----------------+--------------+-----------+

看起来我很想，但在某个地方，我失去了所有的餐馆。列出的可能是数据集中的最后一个。因此，计算机并没有让他们全部返回，而是覆盖了结果。我应该更改哪些代码以获得所有餐厅的营业时间列表？

Answer 1

代码的问题是没有GROUP BY子句的情况下进行聚合，所以结果只有1行。

添加到查询中：

GROUP BY b.city, c.category, b.name, b.review_count, b.stars