选择未包含在分组依据中的列

Question

我有以下SQL Server脚本：

SELECT
    cicmpy.cmp_name as Naam
    ,c2.cmp_name as Arts
    ,cicmpy.textfield1 as Voornaam
    ,items.UserField_02 as Label
    ,absences.HID
FROM
    Absences
INNER JOIN 
    cicmpy ON Absences.CustomerID = cicmpy.cmp_wwn
INNER JOIN 
    cicmpy as c2 ON Absences.FreeGuidField_01 = c2.cmp_wwn
INNER JOIN 
    Items ON absences.ItemCode = items.ItemCode
WHERE 
    Absences.Type = 86

我需要SELECT语句中的所有列，但只需要唯一的列。实际上，列HID是一个惟一的列，因此对它进行分组将返回所有行。我试着用CTE实现我的目标，但没有成功。如何获取除HID列以外的所有基于列的唯一记录？

Answer 1

使用SELECT DISTINCT

SELECT DISTINCT
 cicmpy.cmp_name as Naam
,c2.cmp_name as Arts
  ,cicmpy.textfield1 as Voornaam
  ,items.UserField_02 as Label
FROM Absences
  INNER JOIN cicmpy
    ON Absences.CustomerID = cicmpy.cmp_wwn
inner join cicmpy as c2
   on Absences.FreeGuidField_01 = c2.cmp_wwn
inner join Items
   on absences.ItemCode = items.ItemCode
where Absences.Type = 86

或

如果您需要一些HID，则需要选择要使用的聚合类型(MIN或MAX)

SELECT 
   cicmpy.cmp_name as Naam
  ,c2.cmp_name as Arts
  ,cicmpy.textfield1 as Voornaam
  ,items.UserField_02 as Label
  , MAX(Absences.HID) as MAX_HID
  , MIN(Absences.HID) as MIN_HID
FROM Absences
  INNER JOIN cicmpy
    ON Absences.CustomerID = cicmpy.cmp_wwn
inner join cicmpy as c2
   on Absences.FreeGuidField_01 = c2.cmp_wwn
inner join Items
   on absences.ItemCode = items.ItemCode
where Absences.Type = 86
GROUP BY 
   cicmpy.cmp_name
  ,c2.cmp_name
  ,cicmpy.textfield1
  ,items.UserField_02

Answer 2

希望这就是你想要的：

;WITH cte AS
(
SELECT

 cicmpy.cmp_name as Naam
,c2.cmp_name as Arts
  ,cicmpy.textfield1 as Voornaam
  ,items.UserField_02 as Label
  ,absences.HID
  ,ROW_NUMBER() OVER (PARTITION BY cicmpy.cmp_name, c2.cmp_name, cicmpy.textfield1, items.UserField_02 ORDER BY absences.HID DESC) AS rn
FROM
  Absences
  INNER JOIN cicmpy
    ON Absences.CustomerID = cicmpy.cmp_wwn
inner join cicmpy as c2
on Absences.FreeGuidField_01 = c2.cmp_wwn
inner join Items
on absences.ItemCode = items.ItemCode
where Absences.Type = 86   
)
SELECT *
FROM cte
WHERE rn = 1