在具有条件的表中查找副本

I have a query such as this:

我有一个这样的查询:

select a.id, a.color, a.shade from colors a where a.color = 'red'

which fetches

ID     | Color      | shade
-------|------------|---------
23     |red         | dark10
525    |red         | light-10

Question:

How can I find all records in table colors where there are multiple (two) occurrences of samecolor AND their corresponding shades are NOT identical

如何找到表格颜色中的所有记录,其中存在多个(两个)相同颜色的出现并且它们对应的阴影不相同

e.g. records like the following will not be considered

例如以下记录将不予考虑

ID     | Color      | shade
-------|------------|---------
23     |green       | light-10
324    |green       | light-10

1 个解决方案

#1

You can do this by utilizing a GROUP BY clause with a HAVING COUNT(*) = 1 condition:

您可以通过使用具有HAVING COUNT(*)= 1条件的GROUP BY子句来执行此操作:

declare @colors table (
    id int,
    color nvarchar(100),
    shade nvarchar(100)
)

insert into @colors
        select 23, 'red', 'dark10'
union all select 525, 'red', 'light-10'
union all select 23, 'green', 'light-10'
union all select 324, 'green', 'light-10'

select c.*
from @colors c
inner join (
    select color, shade
    from @colors 
    group by color, shade
    having COUNT(*) > 1
) x
    on x.color = c.color
    and x.shade <> c.shade