mysql - SQL 子查询来计算有多少人按星期几和按楼层结账退房(SQLZoo)

Question

我正在研究 SQLZoo 的一个 hard assessment problems并使用 MySQL。可以找到预订数据和数据库描述here .

Check out per floor. The first digit of the room number indicates the floor – e.g. room 201 is on the 2nd floor. For each day of the week beginning 2016-11-14 show how many guests are checking out that day by floor number. Columns should be day (Monday, Tuesday ...), floor 1, floor 2, floor 3.

这里涉及到宾馆数据库:

预期结果，这并不完全正确，因为左侧列列出的是日期而不是天):

+------------+-----+-----+-----+
| i          | 1st | 2nd | 3rd |
+------------+-----+-----+-----+
| 2016-11-14 |   5 |   3 |   4 |
| 2016-11-15 |   6 |   4 |   1 |
| 2016-11-16 |   2 |   2 |   4 |
| 2016-11-17 |   5 |   3 |   6 |
| 2016-11-18 |   2 |   3 |   2 |
| 2016-11-19 |   5 |   5 |   1 |
| 2016-11-20 |   2 |   2 |   2 |
+------------+-----+-----+-----+

为了解决这个问题，我试图将它分解成多个子部分。现在，我想生成一个包含三列的表，例如:

+------------+-----+------------+
| Date       | Floor| Checkouts |
+------------+------+-----+-----+
| 2016-11-14 |   1  |     5     |
| 2016-11-14 |   2  |     3     | 
| 2016-11-14 |   3  |     4     |
|     .      |   .  |     .     |
|     .      |   .  |     .     |
|     .      |   .  |     .     |
| 2016-11-20 |   1  |     2     |
| 2016-11-20 |   2  |     2     | 
| 2016-11-20 |   3  |     2     |
+------------+------+-----+-----+

乍一看，这似乎很简单。 2016-11-14 的结账次数应该是 booking_day + nights = 2016-11-14 的所有入住者的总和。

我的方法:

SELECT     a.booking_date AS 'Date',
           LEFT(a.room_no, 1) AS 'Floor',

           /* Sum the number of checkouts on a.booking_date, using an alias: */
           (SELECT     SUM(b.occupants)
            FROM       booking b
           /* Compare b.booking_date of these occupants against a.booking_date: */
           WHERE      b.booking_date + INTERVAL nights DAY = a.booking_date) AS 'Checkouts'

FROM       booking a

/* Run through each date in the range */        
 WHERE     (a.booking_date >= '2016-11-14' AND 
            a.booking_date <= '2016-11-20')

/* Group checkouts by date and by floor */  
GROUP BY   a.booking_date, 
           LEFT(a.room_no, 1);

我的代码返回 (i) 错误的结帐计数和 (ii) 给定日期所有楼层的相同计数:

Date           Floor    Checkouts
2016-11-14       1         18
2016-11-14       2         18
2016-11-14       3         18
2016-11-15       1         17
2016-11-15       2         17
2016-11-15       3         17

最初，我想知道我的 LEFT(a.room_no, 1) 是否无法正常工作，它会向我提供给定日期所有楼层的结帐总数。但是，根据 SQLZoo，2016-11-14(所有楼层)的结帐总数为 5 + 3 + 4 = 12。我得到 18。

我的嵌套 SELECT 和 GROUP BY 类别背后的原因有什么问题？

Answer 1

SELECT DATEPART(WEEK, DATEADD(DAY, nights, booking_date)) AS week_number_checkout_date,
       DATENAME(WEEKDAY, DATEADD(DAY, nights, booking_date)) AS week_day_checkout_date,
       LEFT(room_no, 1) [floor],
       SUM(occupants) checkouts
  FROM booking
 WHERE booking_date >= CAST('2016-11-14' AS DATE)
   AND booking_date <= CAST('2016-11-20' AS DATE)
 GROUP BY DATEPART(WEEK, DATEADD(DAY, nights, booking_date)),
          DATENAME(WEEKDAY, DATEADD(DAY, nights, booking_date)),
          LEFT(room_no, 1)