代码之家  ›  专栏  ›  技术社区  ›  Wei Lin

如何删除重复的组键值并显示详细信息项(多个组)

  •  2
  • Wei Lin  · 技术社区  · 6 年前

    源数据和DDL

    CREATE TABLE T
        ([Order_NO] varchar(10), [MO_NO] varchar(10), [DEP] varchar(3), [PRD_NO] varchar(12), [QTY] int, [STD_QTY] numeric, [QTY_DIF] numeric);
    
    INSERT INTO T
        ([Order_NO], [MO_NO], [DEP], [PRD_NO], [QTY], [STD_QTY], [QTY_DIF])
    VALUES
        ('SO17110026', 'MO17110824', 'MO2', '3CA002-00120', 40681, 40380.48, 300.52),
        ('SO17110026', 'MO17110824', 'MO2', '3CA002A00110', 41125, 40380.48, 744.52),
        ('SO17110026', 'MO17110825', 'MO1', '403010060002', 40381, 40380.48, 0.52),
        ('SO17110026', 'MO17110825', 'MO1', '601001000734', 69, 68.102, 0.898),
        ('SO17110026', 'MO17110826', 'MO1', '601002000004', 2862, 2861.4858, 0.5142),
        ('SO17110026', 'MO17110826', 'MO1', '601005000127', 1499, 1498.6446, 0.3554),
        ('SO17110026', 'MO17110826', 'MO1', '601007000001', 69, 68.102, 0.898),
        ('SO17110029', 'MO17110825', 'MO1', '40107008CM01', 238010, 237658.3374, 351.6626),
        ('SO17110029', 'MO17110825', 'MO1', '40107008CM33', 110000, 109149.843, 850.157)
    ;
    
    Order_NO   | MO_NO      | DEP | PRD_NO       |    QTY | STD_QTY | QTY_DIF
    :--------- | :--------- | :-- | :----------- | -----: | :------ | :------
    SO17110026 | MO17110824 | MO2 | 3CA002-00120 |  40681 | 40380   | 301    
    SO17110026 | MO17110824 | MO2 | 3CA002A00110 |  41125 | 40380   | 745    
    SO17110026 | MO17110825 | MO1 | 403010060002 |  40381 | 40380   | 1      
    SO17110026 | MO17110825 | MO1 | 601001000734 |     69 | 68      | 1      
    SO17110026 | MO17110826 | MO1 | 601002000004 |   2862 | 2861    | 1      
    SO17110026 | MO17110826 | MO1 | 601005000127 |   1499 | 1499    | 0      
    SO17110026 | MO17110826 | MO1 | 601007000001 |     69 | 68      | 1      
    SO17110029 | MO17110825 | MO1 | 40107008CM01 | 238010 | 237658  | 352    
    SO17110029 | MO17110825 | MO1 | 40107008CM33 | 110000 | 109150  | 850    
    

    预期结果

    Order_NO   | MO_NO      | DEP  | PRD_NO       |    QTY | STD_QTY | QTY_DIF
    :--------- | :--------- | :--- | :----------- | -----: | :------ | :------
    SO17110026 | MO17110824 | MO2  | 3CA002-00120 |  40681 | 40380   | 301    
    null       | null       | null | 3CA002A00110 |  41125 | 40380   | 745    
    null       | MO17110825 | MO1  | 403010060002 |  40381 | 40380   | 1      
    null       | null       | null | 601001000734 |     69 | 68      | 1      
    null       | MO17110826 | MO1  | 601002000004 |   2862 | 2861    | 1      
    null       | null       | null | 601005000127 |   1499 | 1499    | 0      
    null       | null       | null | 601007000001 |     69 | 68      | 1      
    SO17110029 | MO17110825 | MO1  | 40107008CM01 | 238010 | 237658  | 352    
    null       | null       | null | 40107008CM33 | 110000 | 109150  | 850    
    

    演示测试链接: db<>fiddle

    2 回复  |  直到 6 年前
        1
  •  3
  •   Tim Biegeleisen    6 年前

    我们可以用 ROW_NUMBER :

    SELECT
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY Order_NO ORDER BY MO_NO, PRD_NO) = 1
             THEN Order_NO END AS Order_NO,
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY Order_NO, MO_NO ORDER BY PRD_NO) = 1
             THEN MO_NO END AS MO_NO,
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY Order_NO, MO_NO ORDER BY PRD_NO) = 1
             THEN DEP END AS DEP,
        PRD_NO,
        QTY,
        STD_QTY,
        QTY_DIF
    FROM T
    ORDER BY
        T.Order_NO,
        T.MO_NO,
        T.PRD_NO;
    

    enter image description here

    Demo

        2
  •  4
  •   GMB    6 年前

    使用 LAG :

    SELECT
        IIF(Order_NO = LAG(Order_NO) OVER(ORDER BY Order_NO),  NULL, Order_NO) Order_NO,
        IIF(MO_NO = LAG(MO_NO) OVER(PARTITION BY Order_NO ORDER BY MO_NO), NULL, MO_NO) Order_NO,
        IIF(MO_NO = LAG(MO_NO)  OVER(PARTITION BY Order_NO, MO_NO ORDER BY DEP), NULL, DEP) DEP,
        PRD_NO,
        QTY,
        STD_QTY,
        QTY_DIF
    FROM t
    ORDER BY 
        t.Order_NO, 
        t.MO_NO,
        t.DEP
    

    Demo on DB Fiddle :

    Order_NO   | Order_NO   | DEP  | PRD_NO       |    QTY | STD_QTY | QTY_DIF
    :--------- | :--------- | :--- | :----------- | -----: | :------ | :------
    SO17110026 | MO17110824 | MO2  | 3CA002-00120 |  40681 | 40380   | 301    
    null       | null       | null | 3CA002A00110 |  41125 | 40380   | 745    
    null       | MO17110825 | MO1  | 403010060002 |  40381 | 40380   | 1      
    null       | null       | null | 601001000734 |     69 | 68      | 1      
    null       | MO17110826 | MO1  | 601002000004 |   2862 | 2861    | 1      
    null       | null       | null | 601005000127 |   1499 | 1499    | 0      
    null       | null       | null | 601007000001 |     69 | 68      | 1      
    SO17110029 | MO17110825 | MO1  | 40107008CM01 | 238010 | 237658  | 352    
    null       | null       | null | 40107008CM33 | 110000 | 109150  | 850