获取结果的MSSQL查询-编程知识-白鹭情

我有以下 sql 查询

SELECT Stk_Conv_Id,Created_Date,DC_Code,SKU_Name,Grade,Stock_Qty,Stock_Conv_Ref_Code
  FROM [LEAFDB].[dbo].[Stock_Convertion_Tracking] a
  where a.Created_Date between '2021-12-31' and '2021-12-31 23:59:59'
  and DC_Code ='BNG'
  order by DC_Code, Stock_Conv_Ref_Code

下面给出的 SQL 查询结果

Stk_Conv_Id Created_Date    DC_Code SKU_Name              Grade Stock_Qty   Stock_Conv_Ref_Code
143590        2021-12-31    BNG     CARROT OOTY (PREMIUM)   A   995         STKC/BNG/00150
143591        2021-12-31    BNG     CARROT OOTY (RP)        A   25          STKC/BNG/00150
143592        2021-12-31    BNG     CARROT OOTY (PREMIUM)   A   970         STKC/BNG/00150
143593        2021-12-31    BNG     CARROT OOTY (BABY)      A   1.3         STKC/BNG/00151
143594        2021-12-31    BNG     CARROT OOTY (RP)        A   1.3         STKC/BNG/00151

我需要如下结果

Created_Date    DC_Code From_SKU              From_Grade From_Stk_Qty To_SKU              To_Grade To_Stk_Qty Final_Stock
2021-12-31      BNG     CARROT OOTY (PREMIUM) A          995          CARROT OOTY (RP)    A        25         970
2021-12-31      BNG     CARROT OOTY (BABY)    A          1.3          CARROT OOTY (RP)    A        1.3        0

uj5u.com热心网友回复：

您可以尝试使用带有ROW_NUMBER视窗函式的子查询或 cte row number来获取您的逻辑，SKU_Name然后使用条件聚合函式来获取结果集。

查询 1：

;with cte as (
  SELECT *,
         ROW_NUMBER () OVER(PARTITION BY Created_Date,DC_Code,Stock_Conv_Ref_Code,SKU_Name ORDER BY Stk_Conv_Id) rn
  FROM T1
), cte1 as (
  SELECT *,
         ROW_NUMBER () OVER(PARTITION BY Created_Date,DC_Code,Stock_Conv_Ref_Code ORDER BY Stk_Conv_Id)  grp
  FROM CTE 
  WHERE rn = 1
)
SELECT Created_Date,
       DC_Code, 
       max(case when grp = 1 then SKU_Name end) 'From_SKU',
       max(case when grp = 1 then Grade end) 'From_Grade',
       max(case when grp = 1 then Stock_Qty end) 'From_Stk_Qty',
       max(case when grp = 2 then SKU_Name end) 'To_SKU',
       max(case when grp = 2 then Grade end) 'To_Grade',
       max(case when grp = 2 then Stock_Qty end) 'To_Stk_Qty',
       max(case when grp = 1 then Stock_Qty end) - max(case when grp = 2 then Stock_Qty end) 'Final_Stock'
FROM cte1 
GROUP BY Created_Date,
       DC_Code,
       Stock_Conv_Ref_Code

结果：

| Created_Date | DC_Code |              From_SKU | From_Grade | From_Stk_Qty |           To_SKU | To_Grade | To_Stk_Qty | Final_Stock |
|--------------|---------|-----------------------|------------|--------------|------------------|----------|------------|-------------|
|   2021-12-31 |     BNG | CARROT OOTY (PREMIUM) |          A |          995 | CARROT OOTY (RP) |        A |         25 |         970 |
|   2021-12-31 |     BNG |    CARROT OOTY (BABY) |          A |          1.3 | CARROT OOTY (RP) |        A |        1.3 |           0 |

uj5u.com热心网友回复：

with cte as(select *,
       row_number()over(partition by Stock_Conv_Ref_Code order by Stk_Conv_Id) seq
      from tb
      
)
select distinct Created_Date,DC_Code,

      (select SKU_Name
       from cte t2 
       where t2.seq = 1 and t1.Stock_Conv_Ref_Code = t2.Stock_Conv_Ref_Code) as From_SKU,
       
      (select Grade
       from cte t2 
       where t2.seq = 1 and t1.Stock_Conv_Ref_Code = t2.Stock_Conv_Ref_Code) as From_Grade,
       
        (select Stock_Qty
       from cte t2 
       where t2.seq = 1 and t1.Stock_Conv_Ref_Code = t2.Stock_Conv_Ref_Code) as From_Stk_Qty,
      
        (select SKU_Name
       from cte t2 
       where t2.seq = 2 and t1.Stock_Conv_Ref_Code = t2.Stock_Conv_Ref_Code) as To_SKU,
      
      (select Grade
       from cte t2 
       where t2.seq = 2 and t1.Stock_Conv_Ref_Code = t2.Stock_Conv_Ref_Code) as To_Grade,
      
      
      (select Stock_Qty
       from cte t2 
       where t2.seq = 2 and t1.Stock_Conv_Ref_Code = t2.Stock_Conv_Ref_Code) as To_Stk_Qty,
        (select Stock_Qty
       from cte t2 
       where t2.seq = 3 and t1.Stock_Conv_Ref_Code = t2.Stock_Conv_Ref_Code) as Final_Stock
from cte t1