sql:如何选择至少有2个特定值的另一列的所有列[使用mariadb]

l2osamch  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(420)

所以我有一张这样的table,一直在想办法选择所有房间类型大于1的旅馆。我还没有找到一个方法来感谢你的帮助。

MariaDB [hotel]> SELECT * FROM room;

+--------+---------+--------+-------+
| roomNo | hotelNo | type   | price |
+--------+---------+--------+-------+
|      1 |       1 | single |    80 |
|      1 |       2 | single |    90 |
|      1 |       3 | single |    80 |
|      1 |       4 | single |    80 |
|      1 |       5 | single |    80 |
|      1 |       6 | double |   100 |
|      1 |       7 | single |    80 |
|      2 |       1 | single |    80 |
|      2 |       2 | single |    80 |
|      2 |       3 | single |    80 |
|      2 |       4 | single |    80 |
|      2 |       5 | single |    80 |
|      2 |       6 | single |    80 |
|      2 |       7 | single |    80 |
|      3 |       1 | double |    95 |
|      3 |       2 | double |   100 |
|      3 |       3 | double |    95 |
|      3 |       4 | double |    95 |
|      3 |       5 | double |    95 |
|      3 |       7 | double |    95 |
|      4 |       1 | vip    |   120 |
|      4 |       2 | vip    |   120 |
|      4 |       3 | vip    |   120 |
|      4 |       4 | vip    |   120 |
|      4 |       5 | double |   100 |
|      5 |       1 | single |    85 |
|      5 |       2 | other  |   120 |
|      5 |       3 | other  |   110 |
|      5 |       4 | double |    95 |
|      5 |       5 | double |   100 |
+--------+---------+--------+-------+

30行(0.001秒)
我有:

MariaDB [hotel]> SELECT hotelNo, type, COUNT(*) FROM room GROUP BY type HAVING COUNT(*) > 2;

但这只给了我每个房间类型的总数:

+---------+--------+----------+
| hotelNo | type   | COUNT(*) |
+---------+--------+----------+
|       1 | single |       14 |
|       6 | double |       10 |
|       1 | vip    |        4 |
+---------+--------+----------+

3行(0.001秒)
我想得到一个输出,显示哪个酒店有两个以上的双人房。

quhf5bfb

quhf5bfb1#

您希望酒店有一个以上的双人房。我建议过滤一下 type = 'double' ,聚合方式 hotelno ,并使用 having 子句以确保组中有多个记录:

select hotelno
from room
where type = 'double'
group by hotelno
having count(*) > 1

相关问题