想要將相同的科室進行合并，其余的資料也進行合并，最后每個科室只顯示一行資料？之前是用UNION ALL查找的資料，奈何不會合并。。。
uj5u.com熱心網友回復：
你的sql發出來看看uj5u.com熱心網友回復：
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 all
select 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 all
select 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 all
select 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_dept
union all
select 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
uj5u.com熱心網友回復：
上面是sql,我想把科室那部分進行合并，按照每個科室來顯示資料uj5u.com熱心網友回復：
上面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 all
select 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_dept
union 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 all
select 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 all
select 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_dept
union all
select 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
uj5u.com熱心網友回復：
KESHI1KESHI2
KESHI3
KESHI4
KESHI5
這五個欄位，對同一行記錄來說，是不是有且一個欄位有值，
如果是的話，不能用group by KESHI1 || KESHI2 || KESHI3 || KESHI4 || KESHI5 嗎？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/49674.html
標籤：開發
上一篇：資料庫