WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH AS短语,则只要执行一遍即可。
其实就是把一大堆重复用到的SQL语句放在with as 里面,取一个别名,后面的查询就可以用它。
with lines as
(SELECT t.line
FROM m_line t
where 1 = 1 and t.enabled = 'Y'
order by t.line),
a as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 08:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
b as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 09:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
c as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 10:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
d as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 11:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
e as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 12:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
f as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 13:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
g as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 14:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
h as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 15:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
i as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 16:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
j as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 17:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
k as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 18:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
l as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 19:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
m as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 20:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line),
n as
(select t.line,round((count(t.serial_number) - sum(case when c.errcode!='PASS' then 1 else 0 end))/count(t.serial_number)*100,2) || '%' as con
from tableA t
left join tableB c on c.serial_number=t.serial_number
where t.LINE_TIME >=
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 07:30:00',
'yyyy-MM-dd HH24:MI:SS')
and t.LINE_TIME <
TO_DATE(to_char(sysdate, 'yyyy-mm-dd') ||
' 21:30:00',
'yyyy-MM-dd HH24:MI:SS')
group by t.line)
SELECT lines.line,
a.con as "07:30~08:30",
b.con as "07:30~09:30",
c.con as "07:30~10:30",
d.con as "07:30~11:30",
e.con as "07:30~12:30",
f.con as "07:30~13:30",
g.con as "07:30~14:30",
h.con as "07:30~15:30",
i.con as "07:30~16:30",
j.con as "07:30~17:30",
k.con as "07:30~18:30",
l.con as "07:30~19:30",
m.con as "07:30~20:30",
n.con as "SUMMARY"
FROM lines
left join a
on a.line = lines.line
left join b
on b.line = lines.line
left join c
on c.line = lines.line
left join d
on d.line = lines.line
left join e
on e.line = lines.line
left join f
on f.line = lines.line
left join g
on g.line = lines.line
left join h
on h.line = lines.line
left join i
on i.line = lines.line
left join j
on j.line = lines.line
left join k
on k.line = lines.line
left join l
on l.line = lines.line
left join m
on m.line = lines.line
left join n
on n.line = lines.line
where length(n.con)>0 order by lines.line asc;