比方现在统计一种工单。在生成工单时,包含了工单类型,工单序号,工单名称,和支援厂商
对应M表的 (form_type,flow_id,flow_name,custom_id),工单类型表 S有 (form_type,form_name)其中 form_type与 M表的 form_type 对应,当form_type 为1时,存在厂商表A (ID,VALUE)2时存在表B ,3是无对应厂商表,ID与M.custom_id 对应 现在要统计出全部工单数据
对应M表的 (form_type,flow_id,flow_name,custom_id),工单类型表 S有 (form_type,form_name)其中 form_type与 M表的 form_type 对应,当form_type 为1时,存在厂商表A (ID,VALUE)2时存在表B ,3是无对应厂商表,ID与M.custom_id 对应 现在要统计出全部工单数据
from M,S,A where M.form_type=S.form_type and M.custom_id=A.ID and M.form_type=1
group by S.form_type,S.form_name,M.flow_id,M.flow_name
union all
select S.form_type,S.form_name,M.flow_id,M.flow_name,SUM(B.VALUE) as total
from M,S,B where M.form_type=S.form_type and M.custom_id=B.ID and M.form_type=2
group by S.form_type,S.form_name,M.flow_id,M.flow_name
union all
select S.form_type,S.form_name,M.flow_id,M.flow_name,0 as total
from M,S,A where M.form_type=S.form_type and M.custom_id=A.ID and M.form_type=3
t.form_type,t.form_name,t.flow_id,t.flow_name,sum(t.value) as total
from
(select S.form_type,S.form_name,M.flow_id,M.flow_name,A.VALUE
from M,S,A where M.form_type=S.form_type and M.custom_id=A.ID and M.form_type=1
union all
select S.form_type,S.form_name,M.flow_id,M.flow_name,B.VALUE
from M,S,B where M.form_type=S.form_type and M.custom_id=B.ID and M.form_type=2
union all
select S.form_type,S.form_name,M.flow_id,M.flow_name,0 as VALUE
from M,S,A where M.form_type=S.form_type and M.custom_id=A.ID and M.form_type=3) t
group by
t.form_type,t.form_name,t.flow_id,t.flow_name
t.form_type,t.form_name,t.flow_id,t.flow_name,sum(t.VALUE) as VALUE
from
(select
S.form_type,
S.form_name,
M.flow_id ,
M.flow_name,
M.custom_id,
(case S.form_type
when 1 then (select SUM(VALUE) from A where ID=M.custom_id)
when 2 then (select SUM(VALUE) from B where ID=M.custom_id)
else 0
end) as VALUE
from
M,S
where
M.form_type=S.form_type) t
group by
t.form_type,t.form_name,t.flow_id,t.flow_name
t.manu as manufacture,
a4.deptname as fill_dept_id,a3.deptname as accept_dept_id,a0.acpt_overtime_sign as accept_overtime,
a0.deal_overtime_sign as end_overtime,a0.lastedit_time,a0.time_lmt
from ( SELECT distinct a0.flow_id,a0.form_type,a0.form_id,a0.form_name,a2.form_status_name as form_status,
a5.label as manu,a4.deptname as fill_dept_id,a3.deptname as accept_dept_id,a0.acpt_overtime_sign as accept_overtime,
a0.deal_overtime_sign as end_overtime,a0.lastedit_time,a0.time_lmt
from c_form_status a2,a_form_lst a0
inner JOIN a_hr_deptinfo a4 ON a4.deptid=a0.fill_dept_id left OUTER JOIN
left outer join a_formdesign_data180 d1 on d1.id =a0.custom_id left outer join a_datadict_data a5 on a5.value =d1.vendor_type
a_form_proc_detail a1 ON a1.form_flow_id=a0.flow_id and a1.proc_class=1
left OUTER JOIN a_hr_deptinfo a3 ON a3.deptid=a1.accept_dept_id where 1=1 and a0.form_status=a2.form_status_id
and a0.has_child<>1 and a0.form_type in(92,93,109,111,112) and a0.fill_day<'2007-09-19' uion all
SELECT distinct a0.flow_id,a0.form_type,a0.form_id,a0.form_name,a2.form_status_name as form_status,a5.label as manu,
a4.deptname as fill_dept_id,a3.deptname as accept_dept_id,a0.acpt_overtime_sign as accept_overtime,
a0.deal_overtime_sign as end_overtime,a0.lastedit_time,a0.time_lmt
from c_form_status a2,a_form_lst a0
inner JOIN a_hr_deptinfo a4 ON a4.deptid=a0.fill_dept_id left OUTER JOIN
left outer join a_formdesign_data180 d1 on d1.id =a0.custom_id left outer join a_datadict_data a5 on a5.value =d1.vendor_type
a_form_proc_detail a1 ON a1.form_flow_id=a0.flow_id and a1.proc_class=1
left OUTER JOIN a_hr_deptinfo a3 ON a3.deptid=a1.accept_dept_id where 1=1 and a0.form_status=a2.form_status_id
and a0.has_child<>1 and a0.form_type in(92,93,109,111,112) and a0.fill_day<'2007-09-19' )t
order by a0.flow_id desc
我的SQL 报错了 头看大了```
SELECT M.form_type,S.form_name,M.flow_id,M.flow_name,M.custom_id,
SUM(CASE M.form_type
WHEN 1 THEN SELECT SUM(VALUE)FROM A WHERE A.ID=M.custom_id
WHEN 2 THEN SELECT SUM(VALUE)FROM B WHERE B.ID=M.custom_id
WHEN 3 THEN 0 END)AS Total
FROM M INNER JOIN S ON M.form_type=S.form_type
GROUP BY M.form_type,S.form_name,M.flow_id,M.flow_name,M.custom_id
ORDER BY M.form_type,S.form_name,M.flow_id,M.flow_name,M.custom_id
SELECT M.form_type,S.form_name,M.flow_id,M.flow_name,M.custom_id,
SUM(CASE M.form_type
WHEN 1 THEN (SELECT SUM(VALUE)FROM A WHERE A.ID=M.custom_id)
WHEN 2 THEN (SELECT SUM(VALUE)FROM B WHERE B.ID=M.custom_id)
WHEN 3 THEN 0 END)AS Total
FROM M INNER JOIN S ON M.form_type=S.form_type
GROUP BY M.form_type,S.form_name,M.flow_id,M.flow_name,M.custom_id
ORDER BY M.form_type,S.form_name,M.flow_id,M.flow_name,M.custom_id
t.manu as manufacture,
a4.deptname as fill_dept_id,a3.deptname as accept_dept_id,a0.acpt_overtime_sign as accept_overtime,
a0.deal_overtime_sign as end_overtime,a0.lastedit_time,a0.time_lmt
from
( SELECT distinct a0.flow_id,a0.form_type,a0.form_id,a0.form_name,a2.form_status_name as form_status,
a5.label as manu,a4.deptname as fill_dept_id,a3.deptname as accept_dept_id,a0.acpt_overtime_sign as accept_overtime,
a0.deal_overtime_sign as end_overtime,a0.lastedit_time,a0.time_lmt
from c_form_status a2,a_form_lst a0
inner JOIN a_hr_deptinfo a4 ON a4.deptid=a0.fill_dept_id left OUTER JOIN
left outer join a_formdesign_data180 d1 on d1.id =a0.custom_id left outer join a_datadict_data a5
on a5.value =d1.vendor_type
--这里好像少了东西了吧
a_form_proc_detail a1 ON a1.form_flow_id=a0.flow_id and a1.proc_class=1
left OUTER JOIN a_hr_deptinfo a3 ON a3.deptid=a1.accept_dept_id where 1=1 and a0.form_status=a2.form_status_id
and a0.has_child<>1 and a0.form_type in(92,93,109,111,112) and a0.fill_day<'2007-09-19'
--union写错了,汗~~~
uion all
SELECT distinct a0.flow_id,a0.form_type,a0.form_id,a0.form_name,a2.form_status_name as form_status,a5.label as manu,
a4.deptname as fill_dept_id,a3.deptname as accept_dept_id,a0.acpt_overtime_sign as accept_overtime,
a0.deal_overtime_sign as end_overtime,a0.lastedit_time,a0.time_lmt
from c_form_status a2,a_form_lst a0
inner JOIN a_hr_deptinfo a4 ON a4.deptid=a0.fill_dept_id left OUTER JOIN
--为什么会有连着两个“left OUTER JOIN ”
left outer join a_formdesign_data180 d1 on d1.id =a0.custom_id left outer join a_datadict_data a5
on a5.value =d1.vendor_type
--这里好像又少了东西。
a_form_proc_detail a1
ON a1.form_flow_id=a0.flow_id and a1.proc_class=1
left OUTER JOIN a_hr_deptinfo a3 ON a3.deptid=a1.accept_dept_id
where 1=1 and a0.form_status=a2.form_status_id
and a0.has_child<>1 and a0.form_type in(92,93,109,111,112) and a0.fill_day<'2007-09-19' )t
order by a0.flow_id desc
SELECT distinct a0.flow_id,a0.form_type,a0.form_id,a0.form_name,a2.form_status_name as form_status,
t.manu as manufacture,
a4.deptname as fill_dept_id,a3.deptname as accept_dept_id,a0.acpt_overtime_sign as accept_overtime,
a0.deal_overtime_sign as end_overtime,a0.lastedit_time,a0.time_lmt
from
( SELECT distinct a0.flow_id,a0.form_type,a0.form_id,a0.form_name,a2.form_status_name as form_status,
a5.label as manu,a4.deptname as fill_dept_id,a3.deptname as accept_dept_id,a0.acpt_overtime_sign as accept_overtime,
a0.deal_overtime_sign as end_overtime,a0.lastedit_time,a0.time_lmt
from c_form_status a2,a_form_lst a0
inner JOIN a_hr_deptinfo a4 ON a4.deptid=a0.fill_dept_id
left OUTER JOIN
--为什么会有连着两个“left OUTER JOIN ”
left outer join a_formdesign_data180 d1 on d1.id =a0.custom_id left outer join a_datadict_data a5
on a5.value =d1.vendor_type
--这里好像少了东西了吧
a_form_proc_detail a1 ON a1.form_flow_id=a0.flow_id and a1.proc_class=1
left OUTER JOIN a_hr_deptinfo a3 ON a3.deptid=a1.accept_dept_id where 1=1 and a0.form_status=a2.form_status_id
and a0.has_child<>1 and a0.form_type in(92,93,109,111,112) and a0.fill_day<'2007-09-19'
--union写错了,汗~~~
uion all
SELECT distinct a0.flow_id,a0.form_type,a0.form_id,a0.form_name,a2.form_status_name as form_status,a5.label as manu,
a4.deptname as fill_dept_id,a3.deptname as accept_dept_id,a0.acpt_overtime_sign as accept_overtime,
a0.deal_overtime_sign as end_overtime,a0.lastedit_time,a0.time_lmt
from c_form_status a2,a_form_lst a0
inner JOIN a_hr_deptinfo a4 ON a4.deptid=a0.fill_dept_id left OUTER JOIN
--为什么会有连着两个“left OUTER JOIN ”
left outer join a_formdesign_data180 d1 on d1.id =a0.custom_id left outer join a_datadict_data a5
on a5.value =d1.vendor_type
--这里好像又少了东西。
a_form_proc_detail a1
ON a1.form_flow_id=a0.flow_id and a1.proc_class=1
left OUTER JOIN a_hr_deptinfo a3 ON a3.deptid=a1.accept_dept_id
where 1=1 and a0.form_status=a2.form_status_id
and a0.has_child<>1 and a0.form_type in(92,93,109,111,112) and a0.fill_day<'2007-09-19' )t
order by a0.flow_id desc