with t as
( select productid, sum( case when type = 'A' then 1 else 0 end ) A,
sum( case when type = 'B' the 1 else 0 end ) B from tablename group by productid )
select productid,
case when A > 0 and B > 0 then 'A and B'
when A > 0 and B = 0 then 'A only'
when A = 0 and B > 0 then 'B only'
end
from t
( select productid, sum( case when type = 'A' then 1 else 0 end ) A,
sum( case when type = 'B' the 1 else 0 end ) B from tablename group by productid )
select productid,
case when A > 0 and B > 0 then 'A and B'
when A > 0 and B = 0 then 'A only'
when A = 0 and B > 0 then 'B only'
end
from t
productID a_count b_count
1 1 0
2 1 1
3 0 1
4 1 0
5 1 1
select '001' as id,1 as productid, 'A' as type from dual
union
select '002' as id,2 as productid, 'A' as type from dual
union
select '003' as id,2 as productid, 'B' as type from dual
union
select '004' as id,3 as productid, 'B' as type from dual
union
select '005' as id,4 as productid, 'A' as type from dual
union
select '006' as id,5 as productid, 'A' as type from dual
union
select '007' as id,5 as productid, 'B' as type from dual)
select t.id,t.productid,t.type,
sum(case when type='A' then 1 else 0 end) over(partition by productid,type) as aorb,--1代表A,0代表B
sum(case when type='A' then 1 else 0 end) over(partition by productid) as acount,
sum(case when type='B' then 1 else 0 end) over(partition by productid) as bcount from t
依据几个标示 应该可以判定了吧
。。
SELECT PRODUCTID, SUM(DECODE(TYPE, 'A', 1, 2)) "A=1 B=2 AB=3" FROM T GROUP BY PRODUCTID;