时间戳

首页 > 解决方案 > 如何将以下一组查询的输出转换为 SQL Server 中的视图？

sql - 如何将以下一组查询的输出转换为 SQL Server 中的视图？

问题描述

我在 SQL Server 中有以下代码，我想要相同的输出但作为视图。如何编写视图以提供此输出？我想要第一个查询的结果以及每个占用 id 的不同人员 id 的计数，但我不能在该查询中使用 group by。

提前致谢！

SELECT DISTINCT
    pp.PeopleID,  
    od.OccupancyID,
    pp.Gender
INTO 
    t1
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 

SELECT
    OccupancyID, COUNT(DISTINCT peopleID) AS PeopleCount  
INTO
    t2 
FROM
    t1 
GROUP BY 
    OccupancyID

SELECT
    t1.*,
    t2.PeopleCount, t2.[HouseHold Person] 
FROM
    t1 
JOIN
    t2 ON t1.occupancyID = t2.OccupancyID 

DROP TABLE t1
DROP TABLE t2

标签： sqlsql-server

解决方案

如果这些查询以您想要的方式工作（剧透：它们没有 - 您t2.[HouseHold Person]在创建 t2 时没有定义），您可以将 2 个“into”表替换为 Common Table Expressions 并获得结果。

CREATE VIEW dbo.MyView AS
WITH t1 AS (
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 
), t2 as (
SELECT
    OccupancyID, COUNT(DISTINCT peopleID) AS PeopleCount  
FROM
    t1 
GROUP BY 
    OccupancyID
)
SELECT
    t1.PeopleID,
    t1.OccupancyID,
    t1.Gender,
    t2.PeopleCount
FROM
    t1 
JOIN
    t2 ON t1.occupancyID = t2.OccupancyID;

推荐阅读