Want to the same department to merge, the rest of the data is merged, the each department only shows a row? Before is to use UNION ALL data to find out, do not merge,,,

CodePudding user response:

You look at the SQL

CodePudding user response:

Select count (c.s. ick_id) qichushu,
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
(select z.d ept_name
The from department_dict z
Where z.d ept_code=c.i keshi1 n_dept),
"' keshi2,
"' keshi3,
"' keshi4,
"' keshi5,
"' keshi6
The from sick_visit_info c
Where c.a. dmission_dept_time & lt; To_date (' 2019-02-27 ', '- dd yyyy - mm)/* : ad_start */
And ((c.d ischarge_time is null and mount ed_no is not null) or
C.d ischarge_time & gt;=to_date (' 2019-02-27 ', '- dd yyyy - mm)/* : ad_start */
)
Group by c.i n_dept


Union all

Select 0 qichushu,
Count (c.s. ick_id) ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
"' keshi1,
(select z.d ept_name
The from department_dict z
Where z.d ept_code=c.a. keshi2 dmission_dept),
"' keshi3,
"' keshi4,
"' keshi5,
"' keshi6
The from sick_visit_info c
Where c.a. dmission_dept_time & gt;=to_date (' 2019-02-27 ', '- dd yyyy - mm)/* : ad_start */
And c.a. dmission_dept_time & lt; To_date (' 2019-02-28 ', '- dd yyyy - mm)/* : ad_end */
Group by c.a. dmission_dept


Union all
Select 0 qichushu,
0 ruyuanshu,
Count (d.s ick_id) zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
"' keshi1,
"' keshi2,
(select z.d ept_name
The from department_dict z
Where z.d ept_code=which keshi3 ransfered_dept),
"' keshi4,
"' keshi5,
"' keshi6
The from sick_transfer_record d
Where which ype='1'
And d.e vent_state='1'
And d.d ischarge_time & gt;=to_date (' 2019-02-27 ', '- dd yyyy - mm)/* : ad_start */
And d.d ischarge_time & lt; To_date (' 2019-02-28 ', '- dd yyyy - mm)/* : ad_end */
Group by which ransfered_dept


Union all

Select 0 qichushu,
0 ruyuanshu,
0 zhuanru,
Count (d.s ick_id) zhuanchu,
0 chuyuanshu,
0 qimoshu,
"' keshi1,
"' keshi2,
"' keshi3,
(select z.d ept_name
The from department_dict z
Where z.d ept_code=d.s keshi4 tayed_dept),
"' keshi5,
"' keshi6
The from sick_transfer_record d
Where which ype='1'
And d.e vent_state='1'
And d.d ischarge_time & gt;=to_date (' 2019-02-27 ', '- dd yyyy - mm)/* : ad_start */
And d.d ischarge_time & lt; To_date (' 2019-02-28 ', '- dd yyyy - mm)/* : ad_end */
Group by d.s tayed_dept


Union all

Select 0 qichushu,
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
Count (c.s. ick_id) chuyuanshu,
0 qimoshu,
"' keshi1,
"' keshi2,
"' keshi3,
"' keshi4,
(select z.d ept_name
The from department_dict z
Where z.d ept_code=c.d keshi5 ischarge_dept),
"' keshi6
The from sick_visit_info c
Where c.d ischarge_time & gt;=to_date (' 2019-02-27 ', '- dd yyyy - mm)/* : ad_start */
And c.d ischarge_time & lt; To_date (' 2019-02-28 ', '- dd yyyy - mm)/* : ad_end */
Group by c.d ischarge_dept

Union all

Select 0 qichushu,
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
Count (c.s. ick_id) qimoshu,
"' keshi1,
"' keshi2,
"' keshi3,
"' keshi4,
"' keshi5,
(select z.d ept_name
The from department_dict z
Where z.d ept_code=c.i n_dept) keshi6
The from sick_visit_info c
Where c.a. dmission_dept_time & lt; To_date (' 2019-02-28 ', '- dd yyyy - mm)/* : ad_end */
And ((c.d ischarge_time is null and mount ed_no is not null) or
C.d ischarge_time & gt;=to_date (' 2019-02-28 ', '- dd yyyy - mm)/* : ad_end */
)
Group by c.i n_dept

CodePudding user response:

The SQL is above, I want to have that part of the department to merge, according to each department to display the data

CodePudding user response:

I made some changes, SQL above to display a list of
Select count (c.s. ick_id) qichushu,
0 ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
(select z.d ept_name
The from department_dict z
Where z.d ept_code=c.i n_dept) keshi
The from sick_visit_info c
Where c.a. dmission_dept_time & lt; To_date (' 2019-02-27 ', '- dd yyyy - mm)/* : ad_start */
And ((c.d ischarge_time is null and mount ed_no is not null) or
C.d ischarge_time & gt;=to_date (' 2019-02-27 ', '- dd yyyy - mm)/* : ad_start */
)
Group by c.i n_dept
Union all

Select 0 qichushu,
Count (c.s. ick_id) ruyuanshu,
0 zhuanru,
0 zhuanchu,
0 chuyuanshu,
0 qimoshu,
(select z.d ept_name
The from department_dict z
Where z.d ept_code=c.a. dmission_dept) keshi
The from sick_visit_info c
Where c.a. dmission_dept_time & gt;=to_date (' 2019-02-27 ', '- dd yyyy - mm)/* : ad_start */
And c.a. dmission_dept_time & lt; nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull