如何从表中复制特定行

Question

我有下表

CREATE TABLE DMS
(
KTOV char(10),
NDM int,
KOL decimal(13,2) NOT NULL,
CENA decimal(13,2) NOT NULL,
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');

我正在尝试编写一个查询，以便在这个表中插入一个新行。我希望它以下一种方式工作:假设它是2对行，首先是NDM为MIN的行(在我的例子中是NDM = 1，3行)，第二对是NDM是MAX的行(在我的例子中，NDM = 3，1行)。然后，我想检查第一对中是否有KTOV没有出现在第二对中的行(在我的例子中，这是两行，KTOV = 101，KTOV = 103)。然后，我想在表中插入这两行，从第一对复制它们的所有值，但NDM除外，它应该采用MAX 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;

作为执行的结果，它应该添加两个新行，类似于(因为'102‘KTOV出现在具有MAX NDM的行中)

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

P.S:我要用它作为重新排序的功能

Answer 1

你可以这样做：

with mx as 
  (select * from DMS where NDM in (select max(NDM) from DMS)),
  mn as
  (select * from DMS where NDM in (select min(NDM) from DMS))
  insert into dms (KTOV,NDM,KOL,CENA,SORT)
select KTOV,NDM,KOL,CENA,SORT from mx where not exists (select * from mn where mn.KTOV = mx.KTOV)
union 
select KTOV,(select max(NDM) from DMS),KOL,CENA,SORT from mn where not exists (select * from mx where mn.KTOV = mx.KTOV);

DBFiddle演示