遍历游标 想取 2行或3行 数据

[快乐大法师]

一个游标如下
ID productID type
001 1 A
002 2 A
003 2 B
004 3 B
005 4 A
006 5 A
007 5 B

现在想遍历游标 然后判断 同一个 productID

IF type A
。。。。

IF type B
。。。。

IF type A and B
。。。


想了半天 想不明白怎么实现 请大家帮忙 遍历只是一行一行的 但是 如果 A B 都有的情况 就是两行

[guostong]

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

[CT_LXL]

引用 楼主 kkxxllasasas 的回复:

一个游标如下 ID productID type 001 1 A 002 2 A 003 2 B 004 3 B 005 4 A 006 5 A 007 5 B 现在想遍历游标 然后判断 同一个 productID IF type A 。。。。 IF type B 。。。。 IF type A and B 。。。 想了半天 想不明白怎么实现 请大家帮忙 遍历只是一行一行的 但是 如果 A B 都有的情况 就是两行

把你游标更改一下，让取出来的数据如下： productID a_count b_count 1 1 0 2 1 1 3 0 1 4 1 0 5 1 1

[kingkingzhu]

with t as ( 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;