SQL Server多表连接查询结果异常，求助修正以显示统一日期与正确计数

测试环境数据 #

declare @stay_days table
(on_date    datetime)

declare @occupancy table
(on_date    datetime,
 type_id    numeric(10,0),
 phase      char(1))

declare @rate_room_type_details table
(type_id    numeric(10,0)   null)

insert @stay_days
select '2023-06-01'

insert @occupancy
select '2023-05-31',8,'D'

insert @occupancy
select '2023-06-01',8,'D'

insert @occupancy
select '2023-06-02',5,'D'

insert @occupancy
select '2023-06-03',8,'D'

insert @rate_room_type_details
select 1

insert @rate_room_type_details
select 2

insert @rate_room_type_details
select 3

insert @rate_room_type_details
select 4

insert @rate_room_type_details
select 5

insert @rate_room_type_details
select 6

insert @rate_room_type_details
select 7

insert @rate_room_type_details
select 8

原查询语句 #

SELECT b.type_id,a.on_date,count(*)
FROM @occupancy a
RIGHT OUTER JOIN @stay_days c
ON a.on_date = c.on_date
RIGHT JOIN @rate_room_type_details b
ON a.type_id = b.type_id
AND a.phase IN ('I','D','A') 
GROUP BY b.type_id,a.on_date

原查询结果 #

type_id on_date (No column name)
1       NULL       1
2       NULL       1
3       NULL       1
4       NULL       1
5       NULL       1
6       NULL       1
7       NULL       1
8       2023-06-01 1

预期结果 #

type_id     on_date  (No column name)
    1       2023-06-01 0
    2       2023-06-01 0
    3       2023-06-01 0
    4       2023-06-01 0
    5       2023-06-01 0
    6       2023-06-01 0
    7       2023-06-01 0
    8       2023-06-01 1

修正后的查询语句 #

SELECT 
    b.type_id,
    c.on_date,
    COUNT(a.type_id) AS occupancy_count
FROM @rate_room_type_details b
CROSS JOIN @stay_days c
LEFT JOIN @occupancy a 
    ON a.type_id = b.type_id 
    AND a.on_date = c.on_date
    AND a.phase IN ('I','D','A')
GROUP BY b.type_id, c.on_date
ORDER BY b.type_id

修正说明 #

1. 调整表连接逻辑：先用CROSS JOIN关联所有房型和目标日期，确保每个房型都能绑定指定日期，解决日期为NULL的问题。
2. 改用LEFT JOIN匹配入住数据：从房型+日期的固定组合左连接入住表，仅筛选符合日期、房型和phase条件的记录。
3. 修正计数规则：使用COUNT(a.type_id)替代COUNT(*)，因为COUNT(*)会统计所有行（包括左连接后无匹配的NULL行），而COUNT(a.type_id)仅统计有有效匹配的记录，无匹配时自动返回0。

内容的提问来源于stack exchange，提问作者PanosPlat