mysql - SQL - 查询每个酒店的不同成人/ child 的多个房间

Question

我有一个简单的查询，我在其中为匹配日期范围的每家酒店选择可用的 x 个房间，有 x 个成人 + x 个 child ，但我很难弄清楚如何查询每家酒店的房间列表如下:

• 1 间客房，2 名成人/0 名 child
• 1 间客房，可容纳 4 名成人/2 名 child
• 1 间客房，2 名成人/1 名 child

这是我的查询:

SELECT COUNT(pl.day) AS Days,
       p.property_ID AS Hotel_ID,
       p.name AS Hotel_Name,
       r.room_name AS Room_Name,
       r.room_type_ID AS Room_ID
FROM property p
INNER JOIN room_type r ON p.property_ID=r.property_ID
AND (r.max_adults >= 3
     AND r.max_children >= 0)
INNER JOIN plan pl ON pl.room_type_ID=r.room_type_ID
AND (pl.day >= "2014-07-07"
     AND pl.day <= "2014-07-11")
GROUP BY Room_ID,
         Hotel_ID HAVING Days = 4

编辑

如何在 SELECT 中添加 'No_of_Room' 通过房间号区分 room_types，单个房间的示例结果:

Array
(
    [Room_Price] => 160.00
    [Days] => 4
    [Hotel_ID] => 1
    [Hotel_Name] => Hotel Alfa
    [Room_Name] => Room type C
    [Room_ID] => 3
    [Max_Adults] => 3
    [Max_Children] => 1
    [No_of_Room] => 1 // What number of room does this room_type belongs to
)

然后我可以显示如下结果:

编辑

房间表

Rooms(
 ID,
 hotel_id
 room_name,
 max_Adults,
 max_Children
);

-- Populate
INSERT INTO Rooms VALUES (1,1,"Room A",2,1),(2,1,"Room B",2,5),(3,1,"Room C",3,0);
INSERT INTO Rooms VALUES (1,2,"Room A",2,1),(2,2,"Room B",2,5),(3,3,"Room C",3,4);

Answer 1

使用VIEW让事情变得更美好的例子。

对于这个项目，作者可能有别名，例如一本书的作者可能是“S. Lang”，另一本书的作者可能是“Serge Lang”，主要作者是主要形式 (Serge Lang)，次要作者是事物比如“S. Lang”。

将这些关联起来很重要，理想情况下我想要一个以“AuthorId”和“PrimaryAuthorId”作为列的表，这样我就可以在 AuthorId 等于某物时从中选择 PrimaryAuthorId。

为此， View 定义为:

select
  `BookSystem_AuthorList`.`AuthorId` AS `AuthorId`,
  if((`BookSystem_AuthorList`.`duplicateOf` = 0),
    `BookSystem_AuthorList`.`AuthorId`,
    `BookSystem_AuthorList`.`duplicateOf`
  ) AS `PrimaryAuthorId`
from `BookSystem_AuthorList`;

然后

SELECT PrimaryAuthorId FROM BookSystem_PrimaryAuthorId WHERE AuthorId=10;

给出:

7

加入哪个更好!

然后我使用此 View 定义另一个 View (EditionAuthorsWithPrimaryId) - 这会获取一个版本的作者 - 以及主要作者(然后我可以根据需要加入以获取姓名)

select
  `BookSystem_EditionAuthors`.`BindingId` AS `BindingId`,
  `BookSystem_EditionAuthors`.`EditionId` AS `EditionId`,
  `BookSystem_EditionAuthors`.`AuthorId` AS `AuthorId`,
  `BookSystem_EditionAuthors`.`Position` AS `Position`,
  (select
    `BookSystem_PrimaryAuthorId`.`PrimaryAuthorId`
  from `BookSystem_PrimaryAuthorId`
  where (`BookSystem_PrimaryAuthorId`.`AuthorId` = `BookSystem_EditionAuthors`.`AuthorId`)
  ) AS `PrimaryAuthorId`
from `BookSystem_EditionAuthors`;

现在我可以:

SELECT * FROM BookSystem_EditionAuthorsWithPrimary WHERE EditionId=10;

BindingId, EditionId, AuthorId, Position, PrimaryAuthorId
10,         10,         10,         0,          7

好多了!

下一个查询就是一个很好的例子

select
  `BookSystem_BookList`.`BookId` AS `Id`,
  `BookSystem_BookList`.`Title` AS `Name`,
  `BookSystem_BookList`.`UserId` AS `UserId`,
  `BookSystem_BookList`.`BookType` AS `Subtype`,
  1 AS `IsBook`,0 AS `IsSeries`,
  0 AS `IsAuthor`
from `BookSystem_BookList`

union

select
  `BookSystem_SeriesList`.`SeriesId` AS `Id`,
  `BookSystem_SeriesList`.`SeriesName` AS `Name`,
  `BookSystem_SeriesList`.`UserId` AS `UserId`,
  '' AS `Subtype`,
  0 AS `IsBook`,
  1 AS `IsSeries`,
  0 AS `IsAuthor`
from `BookSystem_SeriesList`

union

select
  `BookSystem_AuthorList`.`AuthorId` AS `Id`,
  concat(
    `BookSystem_AuthorList`.`AuthorSurname`,', ',`BookSystem_AuthorList`.`AuthorForename`,
    ifnull(
      (select concat(
        ' (AKA: ',
        group_concat(
          concat(
            `BookSystem_AuthorList`.`AuthorSurname`,
            ', ',
            `BookSystem_AuthorList`.`AuthorForename`
          ) separator '; '
        ),')'
      ) AS `AKA` from `BookSystem_AuthorList`
        where
          (`BookSystem_AuthorList`.`duplicateOf` = `Id`)
        group by (`BookSystem_AuthorList`.`duplicateOf` = `Id`)
    ),'')) AS `Name`,
    `BookSystem_AuthorList`.`UserId` AS `UserId`,
    '' AS `SubType`,
    0 AS `IsBook`,
    0 AS `IsSeries`,
    1 AS `IsAuthor`
from `BookSystem_AuthorList`
where (`BookSystem_AuthorList`.`duplicateOf` = 0) order by `Name`;

是巨大的!

但现在我可以轻松获取 UserId=1 的所有内容:

mysql> SELECT * FROM BookSystem_Index WHERE UserId = 1;
+----+----------------------------------------+--------+-------------+--------+----------+----------+
| Id | Name                                   | UserId | Subtype     | IsBook | IsSeries | IsAuthor |
+----+----------------------------------------+--------+-------------+--------+----------+----------+
|  4 | A First Course in Calculus             |      1 | Normal      |      1 |        0 |        0 |
|  2 | A First Course in Real Analysis        |      1 | Normal      |      1 |        0 |        0 |
|  2 | Algebra                                |      1 |             |      0 |        1 |        0 |
| 13 | Analysis II assignments                |      1 | Assignments |      1 |        0 |        0 |
| 14 | Author Test                            |      1 | Normal      |      1 |        0 |        0 |
|  8 | b, g                                   |      1 |             |      0 |        0 |        1 |
|  7 | b, g (AKA: t, lll; Teal, lll)          |      1 |             |      0 |        0 |        1 |
|  1 | Calculus of Several Variables          |      1 | Normal      |      1 |        0 |        0 |
|  4 | DuBois, Paul                           |      1 |             |      0 |        0 |        1 |
|  1 | Lang, Serge (AKA: Lang, S. E. R. G. E) |      1 |             |      0 |        0 |        1 |
|  5 | Linear Algebra                         |      1 | Normal      |      1 |        0 |        0 |
|  3 | Morrey, C. B.                          |      1 |             |      0 |        0 |        1 |
|  6 | MySQL                                  |      1 | Normal      |      1 |        0 |        0 |
|  7 | Principles of Mathematical Analysis    |      1 | Normal      |      1 |        0 |        0 |
|  2 | Protter, M. H.                         |      1 |             |      0 |        0 |        1 |
|  5 | Rudin, Walter                          |      1 |             |      0 |        0 |        1 |
| 10 | t                                      |      1 | Normal      |      1 |        0 |        0 |
|  3 | Test                                   |      1 |             |      0 |        1 |        0 |
| 12 | Test 1                                 |      1 | Normal      |      1 |        0 |        0 |
| 11 | Test 4.4.2014                          |      1 | Normal      |      1 |        0 |        0 |
|  8 | Topology and Analysis                  |      1 | Normal      |      1 |        0 |        0 |
|  3 | Undergraduate Algebra                  |      1 | Normal      |      1 |        0 |        0 |
|  1 | Undergraduate Texts in Mathematics     |      1 |             |      0 |        1 |        0 |
|  9 | w                                      |      1 | Normal      |      1 |        0 |        0 |
+----+----------------------------------------+--------+-------------+--------+----------+----------+
24 rows in set (0.00 sec)

优化器正确地查看了 View ，它不会生成完整 View ，它有效地替代了所需的选择。

(取自测试数据库，而不是生产数据库，因此名称很奇怪，例如“TESTING”)