我有以下表格:
name category posts
------------------------------
Client A 01 5348
Client A 05 2584
Client B 02 105
Client C 14 10558
Client C 16 511
Client D 01 4863
Client D 01 1823
现在我想选择那些行:
- 客户端只存在于一个类别中。这意味着客户B和客户D仍然是可能的。
和
此类别为"01"。所以最后期望的输出是:
Client D 01 4863 Client D 01 1823
我想要的查询:
SELECT name, category, posts
FROM exampletable
WHERE (count number of present distinct categories for each name = '1' AND category='01');
问题是我不知道如何将"计数每个名称的当前不同类别的数量"转换为正确的sql代码。有人能帮我一下吗? 您可以使用子查询来实现这一点:
Select e.name, e.category, e.posts from exampletable e
where e.name not in
(select e1.name from exampletable e1 where e.name = e1.name and e.category <> e1.category)
and e.category = '01'
解释:子查询将返回所有与没有一对一关系的名称,因此可以使用NOT IN
消除它们,并且可以使用AND category = '01'
应用进一步的过滤器
已经过测试和验证。
create table #t (
name varchar(100),
category varchar(2),
posts int
);
insert into #t
select
'Client A', '01', 5348
union all select
'Client A', '05', 2584
union all select
'Client B', '02', 105
union all select
'Client C', '14', 10558
union all select
'Client C', '16', 511
union all select
'Client D', '01', 4863
union all select
'Client D', '01', 1823;
select
name,
category,
posts
from
#t
where name in
(
select
name
from
#t
group by
name
having
1 = count(distinct category)
)
and '01' = category;
输出:Client D 01 4863
Client D 01 1823