我有以下查询：
select
C.ClientID,C.FirstName + ' ' + C.LastName as ClientName,CAST(V.StartDate as date) as VisitDate,count(*) as 'Number of Visits'
from
Visit V
Inner Join Client C on
V.ClientID = C.ClientID
group by
C.ClientID,C.FirstName + ' ' + C.LastName,CAST(V.StartDate as date)
having
count(*) > 3
order by
C.ClientID,CAST(V.StartDate as date)
这给出以下结果(名称是假的,万一有人想知道)
ClientID ClientName VisitDate Number of Visits 75 Kay Taylor 2016-06-07 4 372 Moses Mcgowan 2016-09-03 4 422 Raven Mckay 2016-03-11 4 422 Raven Mckay 2016-06-14 4 679 Ulysses Booker 2016-01-09 4 696 Timon Turner 2016-07-06 4 1063 Quyn Wall 2016-06-25 4 1142 Garth Moran 2016-11-20 4 1142 Garth Moran 2016-11-21 4 1563 Hedley Gutierrez 2016-01-07 4 1563 Hedley Gutierrez 2016-01-17 4 1563 Hedley Gutierrez 2016-01-21 4 1563 Hedley Gutierrez 2016-01-27 4 1563 Hedley Gutierrez 2016-01-28 4 1563 Hedley Gutierrez 2016-01-30 4 1563 Hedley Gutierrez 2016-02-27 4 1563 Hedley Gutierrez 2016-03-26 4 1563 Hedley Gutierrez 2016-04-06 4 1563 Hedley Gutierrez 2016-04-09 4 1563 Hedley Gutierrez 2016-04-22 4 1563 Hedley Gutierrez 2016-05-06 4 1563 Hedley Gutierrez 2016-05-26 4 1563 Hedley Gutierrez 2016-06-02 4 1563 Hedley Gutierrez 2016-07-14 4 1563 Hedley Gutierrez 2016-07-29 4 1563 Hedley Gutierrez 2016-08-09 7 1563 Hedley Gutierrez 2016-09-01 4 1563 Hedley Gutierrez 2016-09-23 4 1563 Hedley Gutierrez 2016-12-07 4 1636 Kiara Lowery 2016-01-12 4 2917 Cynthia Carr 2016-06-21 4 2917 Cynthia Carr 2016-10-21 4 3219 Alan Monroe 2016-01-02 4 3219 Alan Monroe 016-02-27 4 3219 Alan Monroe 2016-09-01 5 4288 Natalie Mitchell 2016-03-19 4
如何获取结果只显示ClientID和ClientName一次,结果是这样的？
ClientID ClientName VisitDate Number of Visits
75 Kay Taylor 2016-06-07 4
372 Moses Mcgowan 2016-09-03 4
422 Raven Mckay 2016-03-11 4
2016-06-14 4
679 Ulysses Booker 2016-01-09 4
696 Timon Turner 2016-07-06 4
1063 Quyn Wall 2016-06-25 4
1142 Garth Moran 2016-11-20 4
2016-11-21 4
1563 Hedley Gutierrez 2016-01-07 4
2016-01-17 4
2016-01-21 4
2016-01-27 4
2016-01-28 4
2016-01-30 4
2016-02-27 4
2016-03-26 4
2016-04-06 4
2016-04-09 4
2016-04-22 4
2016-05-06 4
2016-05-26 4
2016-06-02 4
2016-07-14 4
2016-07-29 4
2016-08-09 7
2016-09-01 4
2016-09-23 4
2016-12-07 4
1636 Kiara Lowery 2016-01-12 4
2917 Cynthia Carr 2016-06-21 4
2016-10-21 4
3219 Alan Monroe 2016-01-02 4
3219 016-02-27 4
2016-09-01 5
4288 Natalie Mitchell 2016-03-19 4
解决方法
其实,你想要的不是删除重复,而是不显示它们.
为此,您可以使用带有ROW_NUMBER()的CASE语句,并在第一行显示值,并在ELSE分支(其他行)上显示NULL或“’：
select
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY C.ClientID ORDER BY CAST(V.StartDate as date) ASC) = 1
THEN C.ClientID
ELSE NULL
END as ClientID,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY C.ClientID ORDER BY CAST(V.StartDate as date) ASC) = 1
THEN C.FirstName + ' ' + C.LastName
ELSE NULL
END as ClientName,count(*) as 'Number of Visits'
from
Visit V
Inner Join Client C on
V.ClientID = C.ClientID
group by
C.ClientID,CAST(V.StartDate as date)
having
count(*) > 3
order by
C.ClientID,CAST(V.StartDate as date)
