检查不同列

h6my8fg2  于 2021-07-24  发布在  Java
关注(0)|答案(4)|浏览(282)

我有一个名为person的表,它有三列:name,designation,address。
我需要找到一个独特的名称和名称的组合,如果地址重复,如果是我需要得到马克作为 true 其他 false .
样品:

Name   Designation   Address
Alex     Manager     Houston
Alex     Manager     Houston
Bailey   Worker      Boston
Bailey   Worker      New York

O/P:
Name   Designation  Repeated 
Alex   Manager       true
Bailey Worker        false

我知道,直到我可以分组列的时候,我都是这样尝试的

select Name, Designation, Address
from Person 
group by Name, Designation, Address

编辑:如果alex有地址“phoenix”,那么结果也是一样的,即repeated列是“true”,因为alex已经在两行中重复了“houston”。

isr3a4wc

isr3a4wc1#

仅仅因为编写查询的方法总是不止一种:

WITH cte_Counts AS
(
    SELECT      [Name]
                , [Designation]
                , [Address]
                , COUNT(*) AS [Count]
    FROM        Person
    GROUP BY    [Name]
                , [Designation]
                , [Address]
)
SELECT      [Name]
            , [Designation]
            , CASE
                WHEN [Count] > 1
                    THEN    'true'
                ELSE        'false'
            END AS [Repeated]
FROM        cte_Counts ;
cnjp1d6j

cnjp1d6j2#

重复表示不同地址的数目与总数不匹配。所以:

select name, designation,
       (case when count(distinct Address) < count(*) then 'true' else 'false') as is_repeated
from person
group by name, designation;
qfe3c7zg

qfe3c7zg3#

你可以试试下面的方法-

select Name,Designation,case when count(distinct Address)=1 then 'true' else 'false' end as repeated 
from Person 
group by Name,Designation
7lrncoxx

7lrncoxx4#

这是一个简单的分组和计数:

select Name, Designation, case when count(*)>1 then 'true' else 'false' end as Repeated
from Person
group by Name, Designation

相关问题