问用于查找最佳住宿的MySQL查询，以获得最佳价格
EN

Stack Overflow用户

提问于 2016-09-06 18:05:26

回答 2查看 49关注 0票数 0

我有一个这样的表"residence“：

CREATE TABLE residence (nameRoom varchar(100), idRoom int primary key,guest int, price int);

INSERT INTO residence (nameRoom, idRoom,guest,price) VALUES 
('camera 1', 1,2,200),
('camera 2', 2,2,200),
('camera 3', 3,3,300),
('camera 4', 4,2,400),
('camera 5', 5,4,200),
('camera 6', 6,6,500);

我希望写一个SQL，这将给我最好的住宿在最优惠的价格房间。

mysql

回答 2

Stack Overflow用户

发布于 2016-09-07 01:22:05

这是个subset sum problem。你找不到一个简单的查询来解决它。这个问题是NP完全的。

票数 0

Stack Overflow用户

发布于 2016-09-07 01:34:49

这里有一个想法..。

 SELECT CONCAT_WS(', ',r1.nameroom,r2.nameroom,r3.nameroom) rooms
      , r1.price+COALESCE(r2.price,0)+COALESCE(r3.price,0) total_cost
      , r1.guest+COALESCE(r2.guest,0)+COALESCE(r3.guest,0) guests
   FROM residence r1 
   LEFT 
   JOIN residence r2 
     ON r2.idroom < r1.idroom 
   LEFT 
   JOIN residence r3 
     ON r3.idroom < r2.idroom
 HAVING  guests >= 5
  ORDER
     BY total_cost;

+------------------------------+------------+--------+
| rooms                        | total_cost | guests |
+------------------------------+------------+--------+
| camera 5, camera 1           |        400 |      6 |
| camera 3, camera 1           |        500 |      5 |
| camera 5, camera 2, camera 1 |        600 |      8 |
| camera 3, camera 2, camera 1 |        700 |      7 |
| camera 5, camera 3, camera 2 |        700 |      9 |
| camera 6, camera 1           |        700 |      8 |
| camera 5, camera 3, camera 1 |        700 |      9 |
| camera 5, camera 4, camera 1 |        800 |      8 |
| camera 4, camera 2, camera 1 |        800 |      6 |
| camera 5, camera 4, camera 2 |        800 |      8 |
| camera 5, camera 4, camera 3 |        900 |      9 |
| camera 6, camera 5, camera 1 |        900 |     12 |
| camera 4, camera 3, camera 2 |        900 |      7 |
| camera 6, camera 5, camera 2 |        900 |     12 |
| camera 6, camera 2, camera 1 |        900 |     10 |
| camera 4, camera 3, camera 1 |        900 |      7 |
| camera 6, camera 3, camera 2 |       1000 |     11 |
| camera 6, camera 5, camera 3 |       1000 |     13 |
| camera 6, camera 3, camera 1 |       1000 |     11 |
| camera 6, camera 4, camera 1 |       1100 |     10 |
| camera 6, camera 4, camera 2 |       1100 |     10 |
| camera 6, camera 5, camera 4 |       1100 |     12 |
| camera 6, camera 4, camera 3 |       1200 |     11 |
+------------------------------+------------+--------+
23 rows in set (0.00 sec)

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/39346249

复制

相似问题

问用于查找最佳住宿的MySQL查询，以获得最佳价格
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用于查找最佳住宿的MySQL查询，以获得最佳价格EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用于查找最佳住宿的MySQL查询，以获得最佳价格
EN