select count(c.sick_id) qichushu, 0 ruyuanshu, 0 zhuanru, 0 zhuanchu, 0 chuyuanshu, 0 qimoshu, (select z.dept_name from department_dict z where z.dept_code = c.in_dept) keshi1, '' keshi2, '' keshi3, '' keshi4, '' keshi5, '' keshi6 from sick_visit_info c where c.admission_dept_time < to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and ((c.discharge_time is null and c.bed_no is not null) or c.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ ) group by c.in_dept union allselect 0 qichushu, count(c.sick_id) ruyuanshu, 0 zhuanru, 0 zhuanchu, 0 chuyuanshu, 0 qimoshu, '' keshi1, (select z.dept_name from department_dict z where z.dept_code = c.admission_dept) keshi2, '' keshi3, '' keshi4, '' keshi5, '' keshi6 from sick_visit_info c where c.admission_dept_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and c.admission_dept_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ group by c.admission_dept union all select 0 qichushu, 0 ruyuanshu, count(d.sick_id) zhuanru, 0 zhuanchu, 0 chuyuanshu, 0 qimoshu, '' keshi1, '' keshi2, (select z.dept_name from department_dict z where z.dept_code = d.transfered_dept) keshi3, '' keshi4, '' keshi5, '' keshi6 from sick_transfer_record d where d.type = '1' and d.event_state = '1' and d.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and d.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ group by d.transfered_dept union allselect 0 qichushu, 0 ruyuanshu, 0 zhuanru, count(d.sick_id) zhuanchu, 0 chuyuanshu, 0 qimoshu, '' keshi1, '' keshi2, '' keshi3, (select z.dept_name from department_dict z where z.dept_code = d.stayed_dept) keshi4, '' keshi5, '' keshi6 from sick_transfer_record d where d.type = '1' and d.event_state = '1' and d.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and d.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ group by d.stayed_dept union allselect 0 qichushu, 0 ruyuanshu, 0 zhuanru, 0 zhuanchu, count(c.sick_id) chuyuanshu, 0 qimoshu, '' keshi1, '' keshi2, '' keshi3, '' keshi4, (select z.dept_name from department_dict z where z.dept_code = c.discharge_dept) keshi5, '' keshi6 from sick_visit_info c where c.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and c.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ group by c.discharge_deptunion allselect 0 qichushu, 0 ruyuanshu, 0 zhuanru, 0 zhuanchu, 0 chuyuanshu, count(c.sick_id) qimoshu, '' keshi1, '' keshi2, '' keshi3, '' keshi4, '' keshi5, (select z.dept_name from department_dict z where z.dept_code = c.in_dept) keshi6 from sick_visit_info c where c.admission_dept_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ and ((c.discharge_time is null and c.bed_no is not null) or c.discharge_time >= to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ ) group by c.in_dept
上面是sql,我想把科室那部分进行合并,按照每个科室来显示数据
上面SQL的我做了一些改动,之显示成一列 select count(c.sick_id) qichushu, 0 ruyuanshu, 0 zhuanru, 0 zhuanchu, 0 chuyuanshu, 0 qimoshu, (select z.dept_name from department_dict z where z.dept_code = c.in_dept) keshi from sick_visit_info c where c.admission_dept_time < to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and ((c.discharge_time is null and c.bed_no is not null) or c.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ ) group by c.in_dept union allselect 0 qichushu, count(c.sick_id) ruyuanshu, 0 zhuanru, 0 zhuanchu, 0 chuyuanshu, 0 qimoshu, (select z.dept_name from department_dict z where z.dept_code = c.admission_dept) keshi from sick_visit_info c where c.admission_dept_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and c.admission_dept_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ group by c.admission_deptunion all select 0 qichushu, 0 ruyuanshu, count(d.sick_id) zhuanru, 0 zhuanchu, 0 chuyuanshu, 0 qimoshu, (select z.dept_name from department_dict z where z.dept_code = d.transfered_dept) keshi from sick_transfer_record d where d.type = '1' and d.event_state = '1' and d.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and d.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ group by d.transfered_dept union allselect 0 qichushu, 0 ruyuanshu, 0 zhuanru, count(d.sick_id) zhuanchu, 0 chuyuanshu, 0 qimoshu, (select z.dept_name from department_dict z where z.dept_code = d.stayed_dept) keshi4 from sick_transfer_record d where d.type = '1' and d.event_state = '1' and d.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and d.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ group by d.stayed_dept union allselect 0 qichushu, 0 ruyuanshu, 0 zhuanru, 0 zhuanchu, count(c.sick_id) chuyuanshu, 0 qimoshu, (select z.dept_name from department_dict z where z.dept_code = c.discharge_dept) keshi from sick_visit_info c where c.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/ and c.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ group by c.discharge_deptunion allselect 0 qichushu, 0 ruyuanshu, 0 zhuanru, 0 zhuanchu, 0 chuyuanshu, count(c.sick_id) qimoshu, (select z.dept_name from department_dict z where z.dept_code = c.in_dept) keshi from sick_visit_info c where c.admission_dept_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ and ((c.discharge_time is null and c.bed_no is not null) or c.discharge_time >= to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/ ) group by c.in_dept
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
(select z.dept_name
from department_dict z
where z.dept_code = c.in_dept) keshi1,
'' keshi2,
'' keshi3,
'' keshi4,
'' keshi5,
'' keshi6
from sick_visit_info c
where c.admission_dept_time < to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and ((c.discharge_time is null and c.bed_no is not null) or
c.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
)
group by c.in_dept
union allselect 0 qichushu,
count(c.sick_id) ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
'' keshi1,
(select z.dept_name
from department_dict z
where z.dept_code = c.admission_dept) keshi2,
'' keshi3,
'' keshi4,
'' keshi5,
'' keshi6
from sick_visit_info c
where c.admission_dept_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and c.admission_dept_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
group by c.admission_dept
union all
select 0 qichushu,
0 ruyuanshu,
count(d.sick_id) zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
'' keshi1,
'' keshi2,
(select z.dept_name
from department_dict z
where z.dept_code = d.transfered_dept) keshi3,
'' keshi4,
'' keshi5,
'' keshi6
from sick_transfer_record d
where d.type = '1'
and d.event_state = '1'
and d.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and d.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
group by d.transfered_dept
union allselect 0 qichushu,
0 ruyuanshu,
0 zhuanru,
count(d.sick_id) zhuanchu,
0 chuyuanshu,
0 qimoshu,
'' keshi1,
'' keshi2,
'' keshi3,
(select z.dept_name
from department_dict z
where z.dept_code = d.stayed_dept) keshi4,
'' keshi5,
'' keshi6
from sick_transfer_record d
where d.type = '1'
and d.event_state = '1'
and d.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and d.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
group by d.stayed_dept
union allselect 0 qichushu,
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
count(c.sick_id) chuyuanshu,
0 qimoshu,
'' keshi1,
'' keshi2,
'' keshi3,
'' keshi4,
(select z.dept_name
from department_dict z
where z.dept_code = c.discharge_dept) keshi5,
'' keshi6
from sick_visit_info c
where c.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and c.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
group by c.discharge_deptunion allselect 0 qichushu,
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
count(c.sick_id) qimoshu,
'' keshi1,
'' keshi2,
'' keshi3,
'' keshi4,
'' keshi5,
(select z.dept_name
from department_dict z
where z.dept_code = c.in_dept) keshi6
from sick_visit_info c
where c.admission_dept_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
and ((c.discharge_time is null and c.bed_no is not null) or
c.discharge_time >= to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
)
group by c.in_dept
select count(c.sick_id) qichushu,
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
(select z.dept_name
from department_dict z
where z.dept_code = c.in_dept) keshi
from sick_visit_info c
where c.admission_dept_time < to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and ((c.discharge_time is null and c.bed_no is not null) or
c.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
)
group by c.in_dept
union allselect 0 qichushu,
count(c.sick_id) ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
(select z.dept_name
from department_dict z
where z.dept_code = c.admission_dept) keshi
from sick_visit_info c
where c.admission_dept_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and c.admission_dept_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
group by c.admission_deptunion all
select 0 qichushu,
0 ruyuanshu,
count(d.sick_id) zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
(select z.dept_name
from department_dict z
where z.dept_code = d.transfered_dept) keshi
from sick_transfer_record d
where d.type = '1'
and d.event_state = '1'
and d.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and d.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
group by d.transfered_dept
union allselect 0 qichushu,
0 ruyuanshu,
0 zhuanru,
count(d.sick_id) zhuanchu,
0 chuyuanshu,
0 qimoshu,
(select z.dept_name
from department_dict z
where z.dept_code = d.stayed_dept) keshi4
from sick_transfer_record d
where d.type = '1'
and d.event_state = '1'
and d.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and d.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
group by d.stayed_dept
union allselect 0 qichushu,
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
count(c.sick_id) chuyuanshu,
0 qimoshu,
(select z.dept_name
from department_dict z
where z.dept_code = c.discharge_dept) keshi
from sick_visit_info c
where c.discharge_time >= to_date('2019-02-27', 'yyyy-mm-dd') /*:ad_start*/
and c.discharge_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
group by c.discharge_deptunion allselect 0 qichushu,
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
count(c.sick_id) qimoshu,
(select z.dept_name
from department_dict z
where z.dept_code = c.in_dept) keshi
from sick_visit_info c
where c.admission_dept_time < to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
and ((c.discharge_time is null and c.bed_no is not null) or
c.discharge_time >= to_date('2019-02-28', 'yyyy-mm-dd') /*:ad_end*/
)
group by c.in_dept
KESHI2
KESHI3
KESHI4
KESHI5
这五个字段,对同一行记录来说,是不是有且一个字段有值,
如果是的话,不能用group by KESHI1 || KESHI2 || KESHI3 || KESHI4 || KESHI5 吗?