以下是我的表格结构:
___房间
|--------|-------------|------------|
| ROO_Id | ROO_HotelId | ROO_Status |
|--------|-------------|------------|
| 1 | 1 | active |
| 2 | 1 | active |
| 3 | 1 | inactive |
|--------|-------------|------------|
___订阅
|-------------|-----------|
| SUB_HotelId | SUB_Limit |
|-------------|-----------|
| 1 | 24 |
|-------------|-----------|
我要选择 SUB_Limit
并计算房间数(仅活动房间)。
所以查询应该会返回这样的结果:
|-----------|------------|
| SUB_Limit | ROO_Number |
|-----------|------------|
| 24 | 2 |
|-----------|------------|
为什么这个查询没有返回我想要的输出?
SELECT
SUB_Limit,
COUNT(ROO_Id) AS ROO_Number
FROM ___Rooms
LEFT JOIN ___Subscriptions
ON ___Rooms.ROO_HotelId = ___Subscriptions.SUB_HotelId
WHERE ROO_Status = 'active'
AND SUB_HotelId = 1
AND ROO_HotelId = 1
实际上,它给了我:
|-----------|------------|
| SUB_Limit | ROO_Number |
|-----------|------------|
| NULL | 0 |
|-----------|------------|
3条答案
按热度按时间r3i60tvu1#
请尝试下面的查询一次
0x6upsns2#
尝试以下查询:
eqqqjvef3#
你忘了
GROUP BY
```SELECT
S.SUB_Limit,
COUNT(*) AS ROO_Number
FROM ___Rooms AS R
RIGHT JOIN ___Subscriptions AS S
ON R.ROO_HotelId = S.SUB_HotelId
WHERE R.ROO_Status = 'active'
AND S.SUB_HotelId = 1
GROUP BY ROO_HotelId