查询不同消费大于2的客户

czyoooo 2015-11-08 06:35:07
数据:
ID TYPE CUSTOMER
1 A a
2 A b
3 B a
4 C c


要求类型 TYPE 同时消费大于两家客户的记录
查询结果:

ID TYPE CUSTOMER
1 A a
2 A b
...全文
95 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
shadowpj 2015-11-09
还在用sql2000中。 select * from tb where type in (select type from (select distinct type,customer from tb) a group by type having count(*)>=2)
回复
shadowpj 2015-11-09
还在用sql2000中。
select * from tb where type in
(select type from
(select distinct type,customer from tb) a
group by type having count(*)>=2)
回复
Tiger_Zhao 2015-11-09
    SELECT b.*
FROM (
SELECT TYPE
FROM tb
GROUP BY type
HAVING COUNT(DISTINCT CUSTOMER) >= 2
) a
JOIN tb b
ON a.TYPE = b.TYPE
ORDER BY ID

         ID TYPE CUSTOMER
----------- ---- --------
1 A a
2 A b
回复
yooq_csdn 2015-11-09


select * from tb
where type in (
select type from (select distinct TYPE,   CUSTOMER from tb) a group by type having count(*)>=2
)
回复
comma212164 2015-11-09
select id,tb.type,customer from tb,
(select TYPE,COUNT(TYPE ) shl from tb group by TYPE having COUNT(TYPE ) >=2)a
where tb.TYPE =a.TYPE
回复
czyoooo 2015-11-08
with tb (ID, TYPE, CUSTOMER)as (select 1, 'A', 'a' union all select 3, 'B', 'a' union all select 4, 'C', 'c' union all select 5, 'A', 'a' ) select * from tb where Type in( select TYPE from tb group by TYPE having COUNT(CUSTOMER)>1) 结果: 1 A a 5 A a 这个不是我要的效果,我要的是都销售类型 A的不同客户大于2的。
回复
shoppo0505 2015-11-08
with tb (ID, TYPE, CUSTOMER)as (select 1, 'A', 'a' union all select 2, 'A', 'b' union all select 3, 'B', 'a' union all select 4, 'C', 'c' ) select * from tb where Type in( select TYPE from tb group by TYPE having COUNT(CUSTOMER)>1)
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-11-08 06:35
社区公告
暂无公告