所以我有两个表
Table A
hotelNo | name
H1 |Okto
H2 |Petro
H3 |Duke
H5 |Caesar
H6 |Kirby
Table B
hotelNo | type
H1 |Single
H1 |Single
H1 |Double
H1 |Self
H1 |Single
H2 |Single
H2 |Single
H2 |Double
H2 |Self
H2 |Deluxe
H3 |Single
H3 |Single
H3 |Double
H3 |Self
H3 |Deluxe
H5 |Single
H5 |Single
H5 |Double
H5 |Double
H5 |Double
H6 |Single
H6 |Double
现在,我需要列出有两间以上双人间的酒店。意思是H#="双精度">2.期望的结果应该是这样的:hotelNo|type|countH5|双|3
我花了更多的24小时试图解决这个问题,但这是我得到的更远:
SELECT a.hotelNo,b.type,count(b.type='Double'(>2.自表a a、表b b其中b.type="双">
--但它不起作用。相反,它显示:hotelNo|type|count(b.type='Double'(H1|双|1
救命!!
你似乎走在了正确的轨道上。您可以限制为仅双人间,然后按酒店汇总并断言计数:
SELECT
a.hotelNo,
a.name
FROM TableA a
INNER JOIN TableB b
ON a.hotelNo = b.hotelNo
WHERE
b.type = 'Double'
GROUP BY
a.hotelNo,
a.name
HAVING
COUNT(*) > 2;