sum的高級使用

[billlyh]

SQL語句如下：
select
t.s_date
,t.dept
,t.model
,0 "QC_QTY"
,SUM(nvl(t.QC_QTY,0)-nvl(T.TURN_NEXT,0)+nvl(t.PRE_COME,0)+nvl(t.NEXT_BACK,0)+nvl(t.REPAIR,0)-nvl(t.PICKING,0)-nvl(t.SCRAP,0)-nvl(t.BACK_PRE,0)) "sum_qty"
from
wip.xwp_xwpa136l_qc t
where
t.s_date between '20110701' and '20110701'
GROUP BY
t.s_date
,t.dept
,t.model

union

select
t.s_date
,t.dept
,t.model
,SUM(nvl(t.QC_QTY,0)) "QC_QTY"
,0 "sum_qty"
from
wip.xwp_xwpa136l_qc t
where
t.s_date between '20110701' and '20110701'
GROUP BY
t.s_date
,t.dept
,t.model
結果如下：
S_DATE DEPT MODEL QC_QTY sum_qty
20110701 EP1110 10320 0 591
20110701 EP1110 10320 320 0
20110701 EP1110 40059 0 53
20110701 EP1110 40059 60 0
20110701 EP1110 40059LH 0 41
20110701 EP1110 40059LH 28 0
20110701 EP1110 40568 0 145
20110701 EP1110 40568 33 0
如何改寫我的sql語句，或用其它方法
好讓上面的結果合並成下面的,就是把型號MODEL相同的QC_QTY、sum_qty合並
S_DATE DEPT MODEL QC_QTY sum_qty
20110701 EP1110 10320 320 591
20110701 EP1110 40059 60 53
20110701 EP1110 40059LH 28 41
20110701 EP1110 40568 33 145

[hanzs]

--外层再嵌套一个group by
select s_date,dept,model,sum(qc_qty) as qc_qty,sum(sum_qty) as sum_qty
from 
(
select  
t.s_date
,t.dept
,t.model
,0 "QC_QTY"
,SUM(nvl(t.QC_QTY,0)-nvl(T.TURN_NEXT,0)+nvl(t.PRE_COME,0)+nvl(t.NEXT_BACK,0)+nvl(t.REPAIR,0)-nvl(t.PICKING,0)-nvl(t.SCRAP,0)-nvl(t.BACK_PRE,0)) "sum_qty"
from
 wip.xwp_xwpa136l_qc t
where   
 t.s_date between '20110701' and '20110701'
GROUP BY
t.s_date
,t.dept
,t.model
 
union

select  
t.s_date
,t.dept
,t.model
,SUM(nvl(t.QC_QTY,0)) "QC_QTY"
,0 "sum_qty"
from
 wip.xwp_xwpa136l_qc t
where   
 t.s_date between '20110701' and '20110701'
GROUP BY
t.s_date
,t.dept
,t.model
) 
group by s_date,dept,model

[boyofcity]

外面再执行一个查询
select decode(a,0,b,a) from (你的查询)

[ylp198296]

把查询出来的数据完全再当成一个表进行查询，也叫视图查询