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
3
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
0
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
3
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
0
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