请教一下sql语句

[zephyr_liyo]

如果我有两张表，A表有（人员编号XYBH，姓名XM，建档日期JDRQ，注销日期ZXRQ，学科名称XKDM），B表有（换科日期HKRQ，人员编号XYBH，换前学科HQXKDM，换后学科HHXKDM），两个表的学员编号关联，可能进行不止一次换科，也可能没有换课记录
我现在要统计3月份每一天每个学科的原有人数，新学员数，退学员数，转入数量，转出数量，当天留有人数的数据

能不能通过sql语句一次性统计出来这个数据？

[ghx287524027]

能提供一些测试数据吗？

[卖水果的net]

可以实现的，你有测试数据吗，提供一些；

[超叔csdn]

我弄了点测试数据试了一下是OK的。 create table aa( xybh varchar2(20), xm varchar2(20), jdrq number(8), zxrq number(8), xkdm varchar2(20) ); create table bb( hkrq number(8), xybh varchar2(20), hqxkdm varchar2(20), hhxkdm varchar2(20) ); insert into aa values('zhangsan','张三',20160101,20160801,'shuxue'); insert into aa values('lisi','李四',20160105,20160601,'shuxue'); insert into aa values('wangwu','王五',20160201,20160501,'yuwen'); insert into aa values('liuer','六二',20160108,20160301,'huaxue'); insert into aa values('dahai','大海',20160101,20160801,'huaxue'); insert into bb values(20160201,'zhangsan','shuxue','yuwen'); insert into bb values(20160301,'zhangsan','yuwen','huaxue'); insert into bb values(20160401,'zhangsan','huaxue','shuxue'); insert into bb values(20160205,'dahai','huaxue','yuwen'); insert into bb values(20160205,'wangwu','yuwen','shuxue'); commit; select n1.tjrq 统计日期, n1.cur_xkdm 学科, n1.org_cnt 原有人数, n1.xy_cnt 当填留有人数, n1.new_cnt 新学员数, n1.leave_cnt 退学员数, nvl(n2.zr_cnt, 0) 转入学员数, nvl(n3.zc_cnt, 0) 转出学员数 from (select tjrq, cur_xkdm, count(case when k.stsrq<k.tjrq and k.zxrq > k.tjrq then 1 end) org_cnt, count(case when k.zxrq > k.tjrq then 1 end) xy_cnt, count(case when k.jdrq = k.tjrq then 1 end) new_cnt, count(case when k.zxrq = k.tjrq then 1 end) leave_cnt from (select s.xybh, s.stsrq, c.tjrq, s.cur_xkdm, s.zxrq, s.jdrq, row_number() over(partition by s.xybh, c.tjrq order by s.stsrq desc) rn from (select a.xybh, a.jdrq stsrq, a.xkdm cur_xkdm, a.zxrq, a.jdrq from aa a union select a.xybh, b.hkrq stsrq, b.HHXKDM cur_xkdm, a.zxrq, a.jdrq from aa a, bb b where a.xybh = b.xybh) s, (select to_char(to_date('20160101', 'YYYYMMDD') + rownum - 1, 'YYYYMMDD') tjrq from dual connect by rownum <= 365) c where s.stsrq(+) <= c.tjrq) k where k.rn = 1 group by tjrq, cur_xkdm) n1 left join (select hkrq, hhxkdm, count(*) zr_cnt from bb group by hkrq, hhxkdm) n2 on n1.tjrq = n2.hkrq and n1.cur_xkdm = n2.hhxkdm left join (select hkrq, hqxkdm, count(*) zc_cnt from bb group by hkrq, hqxkdm) n3 on n1.tjrq = n3.hkrq and n1.cur_xkdm = n3.hqxkdm order by 1, 2;