日期:2014-05-17 浏览次数:20926 次
select b.depemp_id 部门ID, a.zc_id 职称ID, nvl(count(case when(a.zc_id=b.zcemp_id) then 1 end),0) 人数
from abc_zc0000 a, abc_emp000 b
where 1=1
group by a.zc_id, b.depemp_id
order by b.depemp_id, a.zc_id
11G 可以考虑用PIVOT
http://www.cnblogs.com/zlja/archive/2011/12/10/2449515.html
7、动态行转不定列
----------------新建测试表
CREATE TABLE TEST(
WL VARCHAR2(10),
XYSL INTEGER,
XYCK VARCHAR2(10),
XCLCK VARCHAR2(10),
XCLCKSL INTEGER,
PC INTEGER
);
----------------第一部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211);