如何基于NDM最值从DMS表复制特定行并插入新行？

问题背景 #

现有DMS表结构 #

CREATE TABLE DMS
(
KTOV char(10),
NDM int,
KOL decimal(13,2) NOT NULL,
CENA decimal(13,2) NOT NULL,
SORT varchar(20), -- 注：原建表语句未包含该字段，但插入数据中存在，实际表结构需补充
CONSTRAINT FK_KTOV FOREIGN KEY(KTOV)
REFERENCES TOV(KTOV),
CONSTRAINT FK_NDM FOREIGN KEY(NDM)
REFERENCES DMZ(NDM)
);

已插入数据 #

INSERT INTO DMS VALUES('101', 1, 20, 10.00, 'Light');
INSERT INTO DMS VALUES('102', 1, 10, 8.00, 'Dark');
INSERT INTO DMS VALUES('103', 1, 30, 5.00, 'Paprika');
INSERT INTO DMS VALUES('101', 2, 10, 6.00, 'Light');
INSERT INTO DMS VALUES('102', 3, 20, 8.00, 'Dark');

需求逻辑 #

• 筛选出NDM为最小值（当前为1）和最大值（当前为3）的行集合；
• 找出最小值集合中KTOV未出现在最大值集合中的行（即KTOV为101和103的行）；
• 将这些行复制插入到DMS表中，仅将NDM改为最大值，其余字段保留原值。

未完成的尝试代码 #

INSERT DMS(KTOV, NDM, KOL, CENA, SORT)
SELECT KTOV, a.NDM as NDM, KOL, CENA, SORT
FROM 
(SELECT 
MAX(NDM) AS NDM,
KTOV AS KTOV,
KOL AS KOL,
CENA AS CENA,
SORT AS SORT
FROM DMS) a
WHERE // I'm not sure how to continue;

期望插入结果 #

INSERT INTO DMS VALUES('101', 3, 20, 10.00, 'Light');
INSERT INTO DMS VALUES('103', 3, 30, 5.00, 'Paprika');

解决方案 #

通过嵌套子查询动态获取关键值并完成筛选，最终实现插入逻辑：

INSERT INTO DMS(KTOV, NDM, KOL, CENA, SORT)
SELECT 
    d.KTOV,
    (SELECT MAX(NDM) FROM DMS) AS NDM,
    d.KOL,
    d.CENA,
    d.SORT
FROM DMS d
WHERE 
    d.NDM = (SELECT MIN(NDM) FROM DMS)
    AND d.KTOV NOT IN (SELECT KTOV FROM DMS WHERE NDM = (SELECT MAX(NDM) FROM DMS));

逻辑说明 #

1. (SELECT MIN(NDM) FROM DMS)：动态获取当前表中NDM的最小值，避免硬编码；
2. (SELECT MAX(NDM) FROM DMS)：动态获取当前表中NDM的最大值，用于替换新插入行的NDM字段；
3. (SELECT KTOV FROM DMS WHERE NDM = (SELECT MAX(NDM) FROM DMS))：提取NDM最大值集合中的所有KTOV值；
4. 外层查询筛选出NDM为最小值且KTOV不在最大值集合中的行，替换NDM后插入表内。

该查询无需依赖固定数值，后续NDM的最小/最大值变化时仍可正常执行。

内容的提问来源于stack exchange，提问作者Palamar66