sql选择

anauzrmj 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(307)

我正在用一些sql查询和它背后的逻辑来敲我的头。
假设我们有这些表：
餐桌酒店

+----+---------+
| id | name    |
+----+---------+
| 1  | Hotel A |
+----+---------+

餐桌酒店客房

+----+----------+-----------+
| id | hotel_id | room_type |
+----+----------+-----------+
| 1  | 1        | dbl       | <- can be used as A,B,C,D,E,F
| 2  | 1        | dbl       | <- can be used as B,C,D,E,F
| 3  | 1        | sng       | <- can be used as A
| 4  | 1        | trp       | <- can be used as D,E,F
+----+----------+-----------+

餐桌酒店房间用途

+----+---------+-------+
| id | room_id | usage |
+----+---------+-------+
| 1  | 1       | B     |
| 2  | 1       | C     |
| 3  | 1       | A     |
| 4  | 1       | D     |
| 5  | 1       | E     |
| 6  | 1       | F     |
| 7  | 2       | B     |
| 8  | 2       | C     |
| 9  | 2       | D     |
| 10 | 2       | E     |
| 11 | 2       | F     |
| 12 | 3       | A     |
| 13 | 4       | D     |
| 14 | 4       | E     |
| 15 | 4       | F     |
+----+---------+-------+

如果我在单独的查询中搜索使用率为a的2个房间或使用率为d的3个房间，结果应该是带有相应房间ID的hotel a。
问题是，如果我同时搜索2个使用a的房间和3个使用d的房间，它也会返回酒店a，因为它不计算某些房间可以用作a和d。
房间应该是独一无二的/总共5个/。当前示例不应返回结果，因为酒店中总共有4个房间。

sql mysql

来源：https://stackoverflow.com/questions/52290397/sql-select-with-complicated-conditions

2条答案

按热度按时间

c8ib6hqw1#

这有用吗？
--两个房间，使用

select id from hotel_room_usages where usage = 'a'

--三个房间，d

select id from hotel_room_usages where usage = 'd'

--有两个房间的房间数

select count(distinct(room_id)) from hotel_room_usages where usage in ('a','d')

赞(0）回复(0）举报 2021-06-21

332nm8kg2#

SELECT h.name AS hotel_name
, q.*
FROM
(
  SELECT r.hotel_id
  , COUNT(DISTINCT CASE WHEN ruA.room_id IS NOT NULL AND ruD.room_id IS NULL THEN ruA.room_id END) AS TotalRoomsOnlyA
  , COUNT(DISTINCT CASE WHEN ruD.room_id IS NOT NULL AND ruA.room_id IS NULL THEN ruD.room_id END) AS TotalRoomsOnlyD
  , COUNT(DISTINCT CASE WHEN ruA.room_id IS NOT NULL AND ruD.room_id IS NOT NULL THEN r.id END) AS TotalRoomsAandD
  , COUNT(DISTINCT r.id) AS TotalRoomsAorD
  FROM hotel_rooms AS r
  LEFT JOIN hotel_room_usages AS ruA ON (ruA.room_id = r.id AND ruA.usage = 'A')
  LEFT JOIN hotel_room_usages AS ruD ON (ruD.room_id = r.id AND ruD.usage = 'D')
  WHERE (ruA.room_id IS NOT NULL OR ruD.room_id IS NOT NULL)
  GROUP BY r.hotel_id
) q
JOIN hotels AS h ON (h.id = q.hotel_id)
CROSS JOIN (SELECT 2 AS a, 3 AS d) AS n
WHERE TotalRoomsAorD >= (a+d)
  AND (
        ((TotalRoomsOnlyA + TotalRoomsAandD) >= a AND TotalRoomsOnlyD >= d) OR
        (TotalRoomsOnlyA >= d AND (TotalRoomsOnlyD + TotalRoomsAandD) >= d) OR
        ((TotalRoomsOnlyA + TotalRoomsAandD/2) >= a AND (TotalRoomsOnlyD + TotalRoomsAandD/2) >= d)
      )
ORDER BY h.name;

在db上测试<fiddle here

赞(0）回复(0）举报 2021-06-21

我来回答

sql选择

2条答案

相关问题

热门标签

最新问答