日期:2014-05-17 浏览次数:20821 次
insert into table1(id,code) values('11224','KYFAWT'); insert into table1(id,code) values('12215','KYFAHS'); insert into table1(id,code) values('12215','KYFAHS'); insert into table1(id,code) values('12220','KYFAWT'); insert into table1(id,code) values('12501','JHRWSXD'); insert into table1(id,code) values('12220','KYFAHS'); insert into table1(id,code) values('12210','KYFAHS'); insert into table1(id,code) values('12501','KYFAWT'); insert into table1(id,code) values('12501','KYFAWT'); insert into table1(id,code) values('11224','KYFAWT'); insert into table1(id,code) values('12220','KYFAWT'); commit; select distinct id,count(distinct code) over (partition by id) from table1 order by id; --结果 1 11224 1 2 12210 1 3 12215 1 4 12220 2 5 12501 2
------解决方案--------------------
SELECT ID, SUM(CASE CODE WHEN 'KYFAWT' THEN 1 ELSE 0 END) AS KYFAWT, SUM(CASE CODE WHEN 'KYFAHS' THEN 1 ELSE 0 END) AS KYFAHS, SUM(CASE CODE WHEN 'JHRWSXD' THEN 1 ELSE 0 END) AS JHRWSXD FROM TABLE1 GROUP BY ID ORDER BY ID;
------解决方案--------------------
1、如果code确定可以
select id,
sum(decode(code, 'KYFAWT', total, 0)) as code_A,
sum(decode(code, 'KYFAHS', total, 0)) as code_B
...
from (SELECT ID, code, COUNT(1) as total FROM YOUR_TABLE GROUP BY ID, code);
2、如果不确定 采用动态SQL了