比较两行SQL Server

I am trying to build a query that takes a bunch of data and finds duplicates of a single column (PHONE), and then outputs the entire row. I have been sucessful in narrowing down my pool of data to 4 results (which is correct). Now I am trying to display the duplicated rows as well, so for example I want to see something like the following:

我正在尝试构建一个查询,该查询获取一堆数据并查找单个列(PHONE)的重复项,然后输出整行。我已经成功地将我的数据池缩小到4个结果(这是正确的)。现在我也试图显示重复的行,所以例如我想看到如下内容:

+--------+-------+--------------+
|  Name  |  Age  |  Phone       |
|  Abby  |  20   | 123-456-7890 |
|  Mike  |  42   | 123-456-7890 |
|  Abel  |  32   | 123-555-0000 |
|  John  |  24   | 123-555-0000 |
+--------+-------+--------------+

But my query is outputting just the following:

但我的查询只输出以下内容:

+--------+-------+--------------+
|  Name  |  Age  |  Phone       |
|  Abby  |  20   | 123-456-7890 |
|  Abel  |  32   | 123-555-0000 |
+--------+-------+--------------+

How do I show Mike and John as well as Abby and Abel?

我如何展示Mike和John以及Abby和Abel?

My query as it is currently:

我目前的查询是:

SELECT DISTINCT C.ACCOUNT,
                C.ADATE,
                C.ATIME,
                C.PHONE,
                C.PATIENTID,
                C.RN
FROM   (SELECT A.ACCOUNT,
               A.ADATE,
               A.ATIME,
               M.HPHONE,
               Row_number()
                 OVER (
                   PARTITION BY M.HPHONE
                   ORDER BY M.HPHONE) AS RN
        FROM   MWAPPTS A
               JOIN CLMASTER M
                 ON A.ACCOUNT = M.ACCOUNT
        WHERE  ( ADATE >= '2012-11-30 00:00:00.000'
                 AND ADATE <= '2012-12-03 00:00:00.000' )
               AND DEPARTMENT LIKE '%bowie%'
               AND A.USERFLAG IN ( 'U' )) RESULTS(ACCOUNT, ADATE, ATIME, PHONE, RN)
       JOIN (SELECT A.ACCOUNT,
                    A.ADATE,
                    A.ATIME,
                    M.HPHONE,
                    A.PATIENTID,
                    Row_number()
                      OVER (
                        PARTITION BY M.HPHONE
                        ORDER BY M.HPHONE) AS RN
             FROM   MWAPPTS A
                    JOIN CLMASTER M
                      ON A.ACCOUNT = M.ACCOUNT
             WHERE  ( ADATE >= '2012-11-30 00:00:00.000'
                      AND ADATE <= '2012-12-03 00:00:00.000' )
                    AND DEPARTMENT LIKE '%bowie%'
                    AND A.USERFLAG IN ( 'U' )) C(ACCOUNT, ADATE, ATIME, PHONE, PATIENTID, RN)
         ON RESULTS.ACCOUNT = C.ACCOUNT
WHERE C.RN <> 1
ORDER  BY C.PHONE,
          C.RN

Thanks in advance for any assistance that can be given.

提前感谢您提供的任何帮助。

2 个解决方案

#1

You have a lot of fields in your sample code that aren't referenced in your example output, so I'm just going to start from scratch, only using the fields in your examples:

您的示例代码中有很多字段未在示例输出中引用,因此我只是从头开始,仅使用示例中的字段:

select
    Name, Age, Phone
from
    _yourtable_
where
    Phone in

        (select
            Phone
        from
            _yourtable_
        group by
            Phone
        having
            count(*) > 1
        )

#2

Something like this should work:

像这样的东西应该工作:

With r as (
  Select
    a.Account,
    a.ADate,
    a.Atime,
    m.HPhone,
    a.PatientID
  From
    MwAppts a
      Inner Join
    ClMaster m
      On a.Account = m.Account
  Where
    ADate >= '2012-11-30' And
    ADate <= '2012-12-03' And -- it's generally considered better to do < endday + 1, as this works if you have datetime fields too
    Department Like '%bowie%' And
    a.UserFlag In ( 'U' )
)

Select Distinct -- Might not need distinct
  r.Account,
  r.ADate,
  r.ATime,
  r.HPhone,
  r.PatientID
From (
    Select
      HPhone
    From
      r
    Group By
      HPhone
    Having
      Count(*) > 1
    ) dupPhones
    Inner Join
  r 
    On dupPhones.HPhone = r.HPhone
Order By
  r.HPhone

#1