比较同一列中的日期SQL Server

Question

这是我的查询:-

select 
item.HeadID as headId,
item.STARTDate as itemStartDate,
item.ENDDate as itemEndDate,
from 
serv.HEAD head
inner join 
serv.ITEM item on item.HeadID = head.HeadID

我得到的结果集是:

tbody> <<tr>

headId	itemStartDate	itemEndDate
197418	2022-10-01	2027-09-30
197418	2022-10-01	2027-09-30
297419	2022-11-11	2027-05-20
297419	2022-11-11	2027-05-20

你可以这样做:

首先，我们添加DISTINCT来接收唯一的行。

select DISTINCT 
item.HeadID as headId,
item.STARTDate as itemStartDate,
item.ENDDate as itemEndDate,
from serv.HEAD head
inner join serv.ITEM item
on item.HeadID = head.HeadID

其次，我们在FROM中使用这个查询来查找重复的HeadID。

SELECT A.HeadID, COUNT(*) AS No_Rows
FROM (
select distinct 
item.HeadID as headId,
item.STARTDate as itemStartDate,
item.ENDDate as itemEndDate,
from serv.HEAD head
inner join serv.ITEM item
on item.HeadID = head.HeadID
) A
GROUP BY A.HeadID
ORDER BY 2 DESC

如果有No_Rows>1、您的结果中有不同日期的headad。

我们可以使用您的查询作为CTE，然后我们进行自连接以获得预期的输出:

WITH CTE AS (
select 
item.HeadID as headId,
item.STARTDate as itemStartDate,
item.ENDDate as itemEndDate,
from 
serv.HEAD head
inner join 
serv.ITEM item on item.HeadID = head.HeadID
)
select c.*
from CTE c
INNER JOIN CTE t ON t.headId = c.headId 
AND ( t.itemStartDate <> c.itemStartDate or t.itemEndDate <> c.itemEndDate)

您可以使用窗口函数。它只需要扫描一次基表，不需要连接.

select *
from (
select 
item.HeadID as headId,
item.STARTDate as itemStartDate,
item.ENDDate as itemEndDate,
minStart = min(item.STARTDate) over (partition by item.HeadID),
maxStart = max(item.STARTDate) over (partition by item.HeadID),
minEnd   = min(item.ENDDate  ) over (partition by item.HeadID),
maxEnd   = max(item.ENDDate  ) over (partition by item.HeadID)
from
serv.ITEM item
) item
where (
item.minStart <> item.maxStart
or item.minEnd   <> item.maxEnd
);