从查询结果中删除重复数据 [英] Remove duplicate data from query results

问题描述

我有以下查询:

 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)

给出以下结果(名字是假的，以防有人想知道)

which gives the following results (names are fake in case anyone is wondering)

 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，结果是这样的?

How can I get the results to show only the ClientID and ClientName once so the results are like this?

 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

推荐答案

其实你想要的不是删除重复，而是不显示.

Actually, what you want is not to remove duplicates, but not display them.

为了做到这一点，您可以使用带有 ROW_NUMBER() 的 CASE 语句并在第一行显示值并显示 NULL> 或 '' 在 ELSE 分支(其他行)上:

In order to do this you can use a CASE statement with ROW_NUMBER() and show the value on the 1st row and display either NULL or '' on the ELSE branch (the other rows):

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,
   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)

这篇关于从查询结果中删除重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！