有两个表一个是产品生产情况,还有一个损耗。想计算一下损耗T1
item,WO_qty
A,100
B,200T2
item,Ascp_Qty
A,10
A,20
B,30
想合成一个表
ITEM,WO_QTY,ASCP_QTY,AS_QTY
A,100,(10+20),70
B,200,30,170
汇总:正确
用Group汇总还是用When Case处理,我试过直接关联是这样的item,WO_qty,ASCP_QTY,AS_QTY
A,100,10,90
A,100,20,80
B,200,30,170
汇总:数据不对
item,WO_qty
A,100
B,200T2
item,Ascp_Qty
A,10
A,20
B,30
想合成一个表
ITEM,WO_QTY,ASCP_QTY,AS_QTY
A,100,(10+20),70
B,200,30,170
汇总:正确
用Group汇总还是用When Case处理,我试过直接关联是这样的item,WO_qty,ASCP_QTY,AS_QTY
A,100,10,90
A,100,20,80
B,200,30,170
汇总:数据不对
a as
(select item,replace(wm_concat(ascp_qty),',','+') x,sum(ascp_qty) s from t2 group by item),
b as
(select item,wo_qty from t1)
select b.item,b.wo_qty,a.x ascp_qty,b.wo_qty-a.s as_qty from b left join a on b.item=a.item
这样试试
select t1.*,B.a,T1.wo_qty-b.a from t1,(select item,sum(Ascp_Qty) a from t2 group by item) B where t1.item=B.item(+)
with
a as
(select item,replace(wm_concat(ascp_qty),',','+') x,sum(ascp_qty) s from t2 group by item)
select b.item,b.wo_qty,'('||a.x||')' ascp_qty,b.wo_qty-a.s as_qty from t1 b left join a on b.item=a.item;
with a as
(
select 'A' item,100 WO_qty from dual
union all
select 'B' item,200 WO_qty from dual
) ,
b as
(
select 'A' item,10 Ascp_Qty from dual
union all
select 'A' item,20 Ascp_Qty from dual
union all
select 'B' item,30 Ascp_Qty from dual
)
select a.item,a.WO_qty,b.Ascp_Qty,a.WO_qty-b.Ascp_Qty AS_QTY
from a,(select item,sum(Ascp_Qty) Ascp_Qty from b group by item) b
where a.item=b.item(+)
有没有一段SQL语句可以做到这样?
如果ITEM是想同的,只显示一条,其它就只是显示损耗的数量,然后,我方便在下面做汇总item,WO_qty,ASCP_QTY,AS_QTY
A,100,10,90
, ,20,80
B,200,30,170
SUM,300,60,340(这个不需要显示出来)
2 select a.item,a.wo_qty,b.ascp_qty,a.wo_qty-b.ascp_qty wo_ascp from t1 a,t2 b where a.item=b.item
3 ),
4 bb as(
5 select item,wo_qty,ascp_qty,wo_ascp,row_number() over(partition by item,wo_qty order by wo_ascp) rn from aa
6 )
7 select case when rn=1 then item end item,case when rn=1 then wo_qty end,ascp_qty,wo_ascp from bb;ITEM CASEWHENRN=1THENWO_QTYEND ASCP_QTY WO_ASCP
---------- ------------------------- ---------- ----------
a 100 20 80
10 90
b 200 30 170
SELECT ITEM,
WO_QTY,
QTY3 ASCP_QTY,
TO_NUMBER(WO_QTY) - TO_NUMBER(QTY4) AS_QTY
FROM (SELECT T.ITEM,
T1.WO_QTY,
CASE
WHEN T.QTY2 IS NULL THEN
T.QTY1
ELSE
'(' || T.QTY1 || '+' || T.QTY2 || ')'
END QTY3,
TO_NUMBER(QTY1) + TO_NUMBER(NVL(QTY2, 0)) QTY4
FROM (SELECT T2.ITEM,
T2.ASCP_QTY QTY1,
LEAD(T2.ASCP_QTY) OVER(PARTITION BY T2.ITEM ORDER BY T2.ASCP_QTY) QTY2,
ROW_NUMBER() OVER(PARTITION BY T2.ITEM ORDER BY T2.ASCP_QTY) RN
FROM T2) T,
T1
WHERE T.ITEM = T1.ITEM
AND T.RN = 1)
请问这一段是什么意思?怎么提示报错?
'(' || T.QTY1 || '+' || T.QTY2 || ')'
请问这一段是什么意思?怎么提示报错?
主要用了连接符||,做连接括号用的。因为要求中是(10+20),必须用连接符连接起来。报什么错误了呀?我这是在实验得出来结果后,才把代码提问出来的。请把错误贴出来