嘿，我明白你的需求啦——要把原来用临时表实现的逻辑改成视图，还要保留第一个查询不使用GROUP BY的要求，同时加上每个OccupancyID对应的去重PeopleID计数对吧？

其实不用临时表的话，我们有两种简洁的实现方式，都能达到和你原来代码一模一样的输出效果：

方式一：子查询关联（和原逻辑最贴近） #

这种写法完全复刻你原来的临时表思路，只是把临时表换成了子查询，直接嵌入视图中：

CREATE VIEW vw_OccupancyPeopleDetails AS
SELECT 
    t1.PeopleID,
    t1.OccupancyID,
    t1.Gender,
    t2.PeopleCount
FROM 
    (
        -- 原t1的逻辑，保持DISTINCT不使用GROUP BY
        SELECT DISTINCT 
            pp.PeopleID, 
            od.OccupancyID, 
            pp.Gender
        FROM dimPeople AS pp 
        LEFT JOIN OccupanciesPeople AS op 
            ON op.PeopleID = pp.PeopleID AND op.CompanyID = pp.CompanyID 
        LEFT JOIN OccupancyDetail AS od 
            ON op.OccupancyID = od.OccupancyID AND op.CompanyID = od.CompanyID 
        WHERE od.OccupancyEndDate IS NULL 
          AND op.DateLeftOccupancy IS NULL 
          AND pp.DateOfDeath IS NULL 
    ) AS t1
JOIN 
    (
        -- 原t2的统计逻辑，复用t1的过滤条件保证数据一致性
        SELECT 
            OccupancyID, 
            COUNT(DISTINCT PeopleID) AS PeopleCount
        FROM 
            (
                SELECT DISTINCT 
                    pp.PeopleID, 
                    od.OccupancyID
                FROM dimPeople AS pp 
                LEFT JOIN OccupanciesPeople AS op 
                    ON op.PeopleID = pp.PeopleID AND op.CompanyID = pp.CompanyID 
                LEFT JOIN OccupancyDetail AS od 
                    ON op.OccupancyID = od.OccupancyID AND op.CompanyID = od.CompanyID 
                WHERE od.OccupancyEndDate IS NULL 
                  AND op.DateLeftOccupancy IS NULL 
                  AND pp.DateOfDeath IS NULL 
            ) AS t1_stats
        GROUP BY OccupancyID
    ) AS t2
    ON t1.OccupancyID = t2.OccupancyID;

方式二：窗口函数（更高效简洁） #

如果想让代码更精简，同时减少数据扫描次数，用窗口函数是更好的选择——它能直接在获取基础数据的同时，计算出每个OccupancyID的去重人数，完全不需要额外的关联操作：

CREATE VIEW vw_OccupancyPeopleDetails AS
SELECT DISTINCT
    pp.PeopleID,
    od.OccupancyID,
    pp.Gender,
    COUNT(DISTINCT pp.PeopleID) OVER (PARTITION BY od.OccupancyID) AS PeopleCount
FROM dimPeople AS pp 
LEFT JOIN OccupanciesPeople AS op 
    ON op.PeopleID = pp.PeopleID AND op.CompanyID = pp.CompanyID 
LEFT JOIN OccupancyDetail AS od 
    ON op.OccupancyID = od.OccupancyID AND op.CompanyID = od.CompanyID 
WHERE od.OccupancyEndDate IS NULL 
  AND op.DateLeftOccupancy IS NULL 
  AND pp.DateOfDeath IS NULL;

效果说明 #

两种方式都严格满足你的要求：

• 对应原t1的主查询只用了DISTINCT，没有使用GROUP BY
• 输出结果和你原来的代码完全一致：包含去重的PeopleID、OccupancyID、Gender，以及每个OccupancyID对应的去重PeopleID计数

创建视图后，直接执行SELECT * FROM vw_OccupancyPeopleDetails就能得到想要的结果啦！

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