如图,请问SQL语句该怎么写?

图片说明

sql
0

2个回答

;WITH temp_table AS(
     SELECT 'A0001' Number,'AA1' PID,160 QTY,100 DeQty,21 Days
     UNION ALL
     SELECT 'A0001' Number,'AA1' PID,80 QTY,100 DeQty,21 Days
     UNION ALL
     SELECT 'A0001' Number,'AA1' PID,100 QTY,100 DeQty,19 
     UNION ALL
     SELECT 'A0001' Number,'AA1' PID,10 QTY,100 DeQty,17 Days
     UNION ALL
     SELECT 'A0001' Number,'AA1' PID,180 QTY,100 DeQty,15 Days

),temp_table1 AS(
SELECT
     *,
     ROW_NUMBER() OVER(ORDER BY temp_table.Days) Num,
     SUM(temp_table.QTY) OVER() Sum_Qty
FROM temp_table
),temp_table2 AS(
SELECT
    *
FROM(
SELECT
     MAX(a.Number) Number,
     MAX(a.QTY) QTY,
     MAX(a.DeQty) DeQty,
     MAX(a.Days) Days,
     SUM(b.QTY) C_Qty,
     MAX(a.Sum_Qty) Sum_Qty,
     a.num
FROM temp_table1 a
JOIN temp_table1 b ON  a.Num>=b.Num
GROUP BY a.Num
)t
OUTER APPLY(
     SELECT SUM(c.QTY) O_Qty FROM temp_table1 c WHERE c.Num<t.Num
)g
)
SELECT
     temp_table2.Number,
     temp_table2.QTY,
     temp_table2.DeQty,
     temp_table2.Days,
     CASE WHEN temp_table2.DeQty>=temp_table2.C_Qty
            THEN temp_table2.Qty 
          WHEN temp_table2.DeQty<temp_table2.QTY AND temp_table2.O_Qty IS NULL 
            THEN temp_table2.DeQty
          ELSE CASE WHEN temp_table2.C_Qty<=temp_table2.Sum_Qty 
                      THEN CASE WHEN temp_table2.DeQty<temp_table2.O_Qty 
                                  THEN 0
                                ELSE temp_table2.DeQty-temp_table2.O_Qty
                           END
                    ELSE 0
               END 
     END result
FROM temp_table2;
1

select sum(a) from (
select (case when datediff(dd,时间,getdate())<=20 then 0.8*入库数量 else 0.9*入库数量 end) as a
) as tab

1
xiaoxiaotuotuodada
noeternal 谢谢您
4 个月之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!