ID CODE11224 KYFAWT
12215 KYFAHS
12215 KYFAHS
12220 KYFAWT
12501 JHRWSXD
12220 KYFAHS
12210 KYFAHS
12501 KYFAWT
11224 KYFAWT
12220 KYFAWT写段sql,求每个ID对应不同code的个数
数据库初学者,各位帮帮我啊
12215 KYFAHS
12215 KYFAHS
12220 KYFAWT
12501 JHRWSXD
12220 KYFAHS
12210 KYFAHS
12501 KYFAWT
11224 KYFAWT
12220 KYFAWT写段sql,求每个ID对应不同code的个数
数据库初学者,各位帮帮我啊
SELECT ID,COUNT(DISTINCT CODE) FROM YOUR_TABLE GROUP BY ID ORDER BY ID;
COUNT(DISTINCT CODE) 列出的是所有code的总数吧,我要的是每个code的个数咩
-- Create table
create table table1
(
id nvarchar2(20),
code nvarchar2(20)
)
;
select distinct id,count(distinct code) over (partition by id) from table1 order by id;
我验证了,应该符合你的要求
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
不是的咩,结果行的参数应该是这样的:ID | CODE_A(个数) |CODE_B(个数)|。
12215 KYFAHS例如这样?
这样的结果就是:
id KYFAWT_num KYFAHS_num11224 1 0
12215 1 1
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;
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了