检查不同列

h6my8fg2 于 2021-07-24 发布在 Java

关注(0)|答案(4)|浏览(283)

我有一个名为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”。

sql sql-server tsql

来源：https://stackoverflow.com/questions/62809031/check-for-distinct-column

4条答案

按热度按时间

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 ;

赞(0）回复(0）举报 2021-07-24

cnjp1d6j2#

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

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

赞(0）回复(0）举报 2021-07-24

qfe3c7zg3#

你可以试试下面的方法-

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

赞(0）回复(0）举报 2021-07-24

7lrncoxx4#

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

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

赞(0）回复(0）举报 2021-07-24

我来回答

检查不同列

4条答案

相关问题

热门标签

最新问答