问SQL查询在时间范围内获取重复列值

EN

select * from
(
select b.order_no,b.dateordered,a.customer_id, C.FirstName, C.LastName, cd.nameoncard from order_no as a
left join order_no as b on a.customer_id=b.customer_id
inner join carddata as cd on b.customer_id=cd.customer_id
INNER JOIN Customers C ON b.customer_id = C.customer_id
where a.order_no < b.order_no
and datediff(hour,a.dateordered,b.dateordered) between 0 and 6
union
select b.order_no,b.dateordered,a.customer_id, C.FirstName, C.LastName, cd.nameoncard from order_no as a
left join order_no as b on a.IPAddress=b.IPAddress
inner join carddata as cd on b.customer_id=cd.customer_id
INNER JOIN Customers C ON b.customer_id = C.customer_id
where a.order_no < b.order_no
and datediff(hour,a.dateordered,b.dateordered) between 0 and 6
union
select a.order_no,a.dateordered,a.customer_id, C.FirstName, C.LastName, cd.nameoncard from order_no as a
inner join carddata as cd on a.customer_id=cd.customer_id
INNER JOIN Customers C ON a.customer_id = C.customer_id
where charindex(C.LastName,cd.nameoncard) = 0
) as abc

WITH Tmp (Order_No, Customer_id, DateOrdered, IPAddress, FirstName,    LastName, NameOnCard) 
AS
(
SELECT Ord.Order_No, Ord.Customer_Id, Ord.DateOrdered, Ord.IPAddress,
    Cust.FirstName, Cust.LastName, CustData.NameOnCard
FROM Order_No Ord
INNER JOIN Customers Cust 
ON 
Cust.Customer_Id = Ord.Customer_Id
INNER JOIN 
CardData CustData
ON CustData.Id = Ord.Card_Id
)
SELECT DISTINCT a.*
FROM Tmp a
INNER JOIN Tmp b 
ON a.Order_No <> b.Order_No 
AND a.Customer_Id = b.Customer_Id
WHERE DATEDIFF(hour, a.DateOrdered, b.DateOrdered) >= 6
UNION
SELECT DISTINCT c.*
FROM Tmp c
INNER JOIN Tmp d 
ON c.Order_No <> d.Order_No 
AND c.IPAddress = d.IPAddress
WHERE DATEDIFF(hour, c.DateOrdered, d.DateOrdered) >= 6
UNION
SELECT DISTINCT e.*
FROM Tmp e
WHERE ISNULL(e.NameOnCard,'') = ''