时间戳

首页 > 解决方案 > 在 SQL Server 中按查询从组中选择一列

sql - 在 SQL Server 中按查询从组中选择一列

问题描述

我在 SQL Server 中有这个查询，它具有我需要的所有条件和分组：

SELECT 
    PersonnelID, Name, EmpStartCalc,
    MAX(PositionDetailsValidFromCalc) PD , 
    MAX(PositionHierValidFromCalc) PH, 
    MAX(PWAValidFromCalc) 
    PWA, 
    MAX(RowId) AS RowId
FROM 
    TV_IAMintegration_VW 
WHERE
    EmpStartCalc >= 20200101 
    AND EmpStartCalc <= 20200131 
    AND ((20200131 > PositionHierValidFromCalc     
GROUP BY 
    PersonnelID, Name, EmpStartCalc
ORDER BY 
    PersonnelID Asc

我得到的结果也有一些其他列也很重要，但我需要单独的 Rowids 才能从其他类似的表中再次获取完整的记录

SELECT *
FROM Tv_IAM_NumSequence
WHERE RowId IN ("Row Ids returned from the above query")

标签： sqlsql-servergroup-bysubquery

解决方案

-- Possibility 1
SELECT *
FROM Tv_IAM_NumSequence f1
inner join 
(
SELECT 
    PersonnelID, Name, EmpStartCalc,
    MAX(PositionDetailsValidFromCalc) PD , 
    MAX(PositionHierValidFromCalc) PH, 
    MAX(PWAValidFromCalc) PWA, 
    MAX(RowId) AS RowId
FROM TV_IAMintegration_VW 
WHERE
    EmpStartCalc between 20200101 AND 20200131 
    AND PositionHierValidFromCalc < 20200131    
GROUP BY PersonnelID, Name, EmpStartCalc
) f2
on f1.RowId =f2.RowId 

-- Possibility 2
with f2 as (
SELECT 
    PersonnelID, Name, EmpStartCalc,
    MAX(PositionDetailsValidFromCalc) PD , 
    MAX(PositionHierValidFromCalc) PH, 
    MAX(PWAValidFromCalc) PWA, 
    MAX(RowId) AS RowId
FROM TV_IAMintegration_VW 
WHERE
    EmpStartCalc between 20200101 AND 20200131 
    AND PositionHierValidFromCalc < 20200131    
GROUP BY PersonnelID, Name, EmpStartCalc
)
SELECT *
FROM Tv_IAM_NumSequence f1
inner join f2 on f1.RowId =f2.RowId

推荐阅读