SQL。如何在副本中忽略重复项和更改外键的情况下将一个select插入到另一个select中
SQL。如何在副本中忽略重复项和更改外键的情况下将一个select插入到另一个select中
提问于 2017-09-10 21:02:50
原始任务:将行从一个文档复制到另一个文档，忽略重复项。
从编号最小的文档复制到编号最大的文档。假设标头中至少有一个条目。添加时，跳过(不添加)已存在的产品行。下面是完整的代码：
CREATE TABLE TOV
(
KTOV INT PRIMARY KEY NOT NULL,
NTOV VARCHAR(MAX) NOT NULL,
SORT VARCHAR(MAX) NOT NULL
);
GO
CREATE TABLE DMZ
(
DDM DATE NOT NULL,
NDM INT PRIMARY KEY NOT NULL,
PR INT NOT NULL
);
GO
CREATE TABLE DMS
(
KTOV INT NOT NULL
FOREIGN KEY REFERENCES TOV(KTOV),
NDM INT NOT NULL
FOREIGN KEY REFERENCES DMZ(NDM),
KOL INT NOT NULL,
CENA DECIMAL(13,2) NOT NULL,
SORT VARCHAR(MAX) NOT NULL
);
GO
INSERT TOV
VALUES
(101, 'Beer', 'Light'),
(102, 'Beer', 'Dark'),
(103, 'Chips', 'With paprika');
go
INSERT DMZ
VALUES
('01.05.2014', 2, 1),
('01.05.2104', 3, 2),
('02.05.2014', 5, 2);
GO
INSERT DMS
VALUES
(101, 2, 100, 8.00, 'Light'),
(102, 3, 80, 9.50, 'Dark'),
(103, 5, 50, 6.50, 'With paprika'),
(101, 2, 1, 10.00, 'Light'),
(103, 3, 1, 8.50, 'With paprika'),
(101, 5, 2, 10, 'Light'),
(102, 3, 1, 11.50, 'Dark'),
(101, 2, 2, 10.50, 'Light'),
(103, 5, 1, 8.60, 'With paprika');
GO我被卡住了，我找不到解决方案。有这样一个伪查询：
INSERT INTO (select * from DMS WHERE NDM = (SELECT MIN(NDM) FROM DMS))
FROM (select * from DMS WHERE NDM = (SELECT MAX(NDM) FROM DMS))
ON DUPLICATE KEY UPDATE 该查询还有这样一种变体：
CREATE VIEW MINDMS1
AS SELECT * FROM DMS1 WHERE NDM = (SELECT MIN(NDM) FROM DMS1);
CREATE VIEW MAXDMS1
AS SELECT * FROM DMS1 WHERE NDM = (SELECT MAX(NDM) FROM DMS1);
MERGE MAXDMS1 AS MAXD
USING MINDMS1 AS MIND
ON (MAXD.KTOV = MIND.KTOV AND MAXD.NDM > MIND.NDM)
WHEN NOT MATCHED THEN
INSERT (KTOV, NDM, KOL, CENA, SORT)
VALUES (MIND.KTOV, MIND.NDM, MIND.KOL, MIND.CENA, MIND.SORT);但它的工作方式是错误的。数据被复制到MINDMS1上。但它需要复制到MAXDMS1中。我不知道如何将副本中的ndm (它是外键)更改为MAXDMS1.NDM
回答 1
Stack Overflow用户
发布于 2017-09-10 22:28:58
如果此操作仅为insert，则不需要merge：
根据确定duplicate的列，在not exists()子句的where中对它们进行比较。在本例中，我比较了ktov和kol (单独使用ktov不会插入任何行)
declare @min_ndm int, @max_ndm int;
select @min_ndm = min(ndm), @max_ndm = max(ndm) from DMS;
insert into dms (ktov, ndm, kol, cena, sort)
output inserted.*
select o.ktov, ndm=@max_ndm, o.kol, o.cena, o.sort
from dms o
where o.ndm = @min_ndm
and not exists (
select 1
from dms i
where i.ndm = @max_ndm
and i.ktov = o.ktov
and i.kol = o.kol
)dbfiddle.uk demo
插入以下行：
+------+-----+-----+-------+-------+
| KTOV | NDM | KOL | CENA | SORT |
+------+-----+-----+-------+-------+
| 101 | 5 | 100 | 8.00 | Light |
| 101 | 5 | 1 | 10.00 | Light |
+------+-----+-----+-------+-------+如果你真的想使用merge，那么你可以对你的目标和源使用common table expressions：
declare @min_ndm int, @max_ndm int;
select @min_ndm = min(ndm), @max_ndm = max(ndm) from DMS;
;with mindms as (select * from dms where ndm = @min_ndm)
, maxdms as (select * from dms where ndm = @max_ndm)
merge into maxdms as t
using mindms as s
on (t.ktov = s.ktov and t.kol = s.kol)
when not matched then
insert values (s.ktov, @max_ndm, s.kol, s.cena, s.sort)
output $action, inserted.*;dbfiddle.uk demo
返回：
+---------+------+-----+-----+-------+-------+
| $action | KTOV | NDM | KOL | CENA | SORT |
+---------+------+-----+-----+-------+-------+
| INSERT | 101 | 5 | 100 | 8.00 | Light |
| INSERT | 101 | 5 | 1 | 10.00 | Light |
+---------+------+-----+-----+-------+-------+需要注意的一些merge问题：
- Use Caution with SQL Server''s
MERGEStatement - Aaron Bertrand - An Interesting
MERGEBug - Paul White
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/46140987
复制相关文章
相似问题
腾讯云开发者
