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

[czyoooo]

数据：
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

[shoppo0505]

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)

[czyoooo]

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的。

[comma212164]

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 

[yooq_csdn]

select * from tb
where type in (
select type from (select distinct TYPE,   CUSTOMER from tb) a group by type having count(*)>=2
)

[Tiger_Zhao]

    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

[shadowpj]

还在用sql2000中。
select * from tb where type in
(select type from
(select distinct type,customer from tb) a
group by type having count(*)>=2)

[shadowpj]

还在用sql2000中。 select * from tb where type in (select type from (select distinct type,customer from tb) a group by type having count(*)>=2)