插入无重复MSSQL的选择中
因此，我有两个CTE，我想在表中插入值。 在P CTE中，我有50个注册，而在M cte中，我有8个。我想要的是在目标表中插入所有注册，只有p 一次 ，然后从M随机分配注册。
我写的是插入300行而不是50行。这是我的代码：
;WITH P
AS
(
SELECT [ID pacient]
FROM Pacienti
WHERE [ID pacient] NOT IN(SELECT [ID pacient] FROM [Pacient-Medic de familie])
GROUP BY [ID Pacient]
),
M AS
(
SELECT FunctiiMedici.[ID medic]
FROM FunctiiMedici
INNER JOIN Functii
ON Functii.[ID functie]=FunctiiMedici.[ID functie]
WHERE Functii.Denumire like 'Medic generalist'
GROUP BY [ID Medic]
)
INSERT INTO [Pacient-Medic de familie]([ID medic], [ID pacient])
SELECT [ID Medic], [ID Pacient] FROM M, P
So the thing is I have two CTEs from which I want to insert the values in a table.
In the P CTE I have 50 registrations, while in the M CTE I have 8. What I want is to insert in destination table all registrations from P only once and assign the registration from M randomly.
What I wrote insert 300 rows instead of 50. Here is my code:
;WITH P
AS
(
SELECT [ID pacient]
FROM Pacienti
WHERE [ID pacient] NOT IN(SELECT [ID pacient] FROM [Pacient-Medic de familie])
GROUP BY [ID Pacient]
),
M AS
(
SELECT FunctiiMedici.[ID medic]
FROM FunctiiMedici
INNER JOIN Functii
ON Functii.[ID functie]=FunctiiMedici.[ID functie]
WHERE Functii.Denumire like 'Medic generalist'
GROUP BY [ID Medic]
)
INSERT INTO [Pacient-Medic de familie]([ID medic], [ID pacient])
SELECT [ID Medic], [ID Pacient] FROM M, P
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
有几种可能性。
一个：将最后一个查询更改为类似的问题
，或者您可以设计一个仅返回所需数据而不是所有条目的交叉的查询。数据库结构还不够清晰，无法有指南
There are several possibilities.
One: change the last query to something like
Or you could design a query that returns only the data you need and not a cross of all entries. The database structure is not clear enough to have a guide
这将产生
交叉加入，这很可能是您所看到的。尝试明确的加入以减少结果，类似：您的密钥是
JOIN两个查询，并减少结果集，因为您期望它This will produce a
cross join, which is most likely what you're seeing. Try an explicitJOINto reduce the results, something like :Your key is what should
JOINthe two queries and reduce the resultset as your expecting it to