mysql - 在 Material list 中插入父项目的销售价格而不重复

Question

我有 Material list 表，我希望根据 ParentItemCode 在其中插入销售价格(仅在第一行一次)。

父商品X的价格为1735，Y为3000。

以下是数据:

CREATE TABLE mytable (
    LineNum INT NOT NULL PRIMARY KEY
    ,ParentPnxCode VARCHAR(1) NOT NULL
    ,ChildPnxCode VARCHAR(2) NOT NULL
    ,Unit VARCHAR(3) NOT NULL
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    10000
    ,'X'
    ,'x1'
    ,'PCS'
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    20000
    ,'X'
    ,'x2'
    ,'PCS'
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    30000
    ,'X'
    ,'x3'
    ,'PCS'
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    40000
    ,'X'
    ,'x4'
    ,'PCS'
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    50000
    ,'X'
    ,'x5'
    ,'PCS'
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    60000
    ,'X'
    ,'x6'
    ,'PCS'
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    70000
    ,'X'
    ,'x7'
    ,'PCS'
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    5000
    ,'Y'
    ,'y1'
    ,'PCS'
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    7500
    ,'Y'
    ,'y2'
    ,'PCS'
    );

INSERT INTO mytable (
    LineNum
    ,ParentPnxCode
    ,ChildPnxCode
    ,Unit
    )
VALUES (
    8750
    ,'Y'
    ,'y2'
    ,'PCS'
    );

以下是所需的输出:

+---------+---------------+--------------+------+-------------------+
| LineNum | ParentPnxCode | ChildPnxCode | Unit | Old Selling Price |
+---------+---------------+--------------+------+-------------------+
|   10000 | X             | x1           | PCS  |              1735 |
|   20000 | X             | x2           | PCS  |                 0 |
|   30000 | X             | x3           | PCS  |                 0 |
|   40000 | X             | x4           | PCS  |                 0 |
|   50000 | X             | x5           | PCS  |                 0 |
|   60000 | X             | x6           | PCS  |                 0 |
|   70000 | X             | x7           | PCS  |                 0 |
|    5000 | Y             | y1           | PCS  |              3000 |
|    7500 | Y             | y2           | PCS  |                 0 |
|    8750 | Y             | y2           | PCS  |                 0 |
+---------+---------------+--------------+------+-------------------+

你能帮我实现上述输出吗？感谢您提前的支持。

Answer 1

这个查询应该给你你想要的结果。它依靠 ROW_NUMBER() 来确定当前记录是否是组中的第一条记录，并相应地显示预期值:

SELECT 
    t.*,
    CASE 
        WHEN ROW_NUMBER() OVER(PARTITION BY ParentPnxCode ORDER BY LineNum) = 1
        THEN CASE ParentPnxCode
            WHEN 'X' THEN 1735
            WHEN 'Y' THEN 3000
        END
        ELSE 0
    END OldSellingPrice
FROM mytable t

<强> Demo on DB Fiddle :

LineNum | ParentPnxCode | ChildPnxCode | Unit | OldSellingPrice------: | :------------ | :----------- | :--- | --------------:  10000 | X             | x1           | PCS  |            1735  20000 | X             | x2           | PCS  |               0  30000 | X             | x3           | PCS  |               0  40000 | X             | x4           | PCS  |               0  50000 | X             | x5           | PCS  |               0  60000 | X             | x6           | PCS  |               0  70000 | X             | x7           | PCS  |               0   5000 | Y             | y1           | PCS  |            3000   7500 | Y             | y2           | PCS  |               0   8750 | Y             | y2           | PCS  |               0

---

If you are looking to actually create a new column in the table to store that information, you could go (ROW_NUMBER() is not allowed in SET clause I switched to a correlated subquery with a NOT EXISTS condition):

ALTER table mytable ADD OldSellingPrice int;
UPDATE t
SET t.OldSellingPrice = 
    CASE WHEN NOT EXISTS(SELECT 1 FROM mytable t1 WHERE t.ParentPnxCode = t1.ParentPnxCode AND t1.LineNum < t.LineNum)
        THEN CASE ParentPnxCode
            WHEN 'X' THEN 1735
            WHEN 'Y' THEN 3000
        END
        ELSE 0
    END
FROM mytable t

<强> Demo on DB Fiddle