一条集锦计算的SQL

2013-06-19

一条汇总计算的SQL有两个表一个是产品生产情况，还有一个损耗。想计算一下损耗T1item，WO_qtyA,100B,200T2ite

一条汇总计算的SQL
有两个表一个是产品生产情况，还有一个损耗。想计算一下损耗

T1
item，WO_qty
A,100
B,200

T2
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
汇总：数据不对

[解决办法]
with
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
这样试试
[解决办法]
给T2  group下，然后两表再合并
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(+)
[解决办法]
对1楼的做个改写
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> with aa as(
  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
[解决办法]
我主要用了分析函数，sql语句如下所示：

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)

热点排行

oracle

一条集锦计算的SQL