gpt4 book ai didi

SQL——对在多行键上连接的表使用 IN

转载 作者:行者123 更新时间:2023-12-01 11:58:09 25 4
gpt4 key购买 nike

好的。我已经设置了一小部分示例表。我只是举个例子,因为这是我交流问题的最佳方式。

证书表:

WorkerId    Name                             Version
----------- -------------------------------- -----------
1 Construction 1
1 Construction 2
1 Demolition 1
1 Fusion 1
5 Fusion 1
4 Demolition 1
4 Demolition 2

CertDesc 表(版本、名称形式的主键):

Name                             Version     Description
-------------------------------- ----------- -----------------------------------------------------------------------------------------
Construction 1 Basic Construction -- Required for all construction workers.
Construction 2 Full Construction -- Required for all construction managers.
Demolition 1 Demolition -- Explosives -- Required for demolition managers.
Fusion 1 Fusion System Control -- Includes catastrophic super-criticality recovery.
Demolition 2 Large Scale Demolition -- Basic fission knowledge with full chemical cert.

现在。我想获取所有 CertDesc 行的列表,这样 WorkerId 1 就没有该证书。对于 X = 1,我应该只获得 Demolition 2。

这是对我来说最好的近乎存在的查询:

Select Distinct d.Name, d.Version, d.Description
From CertDesc d join Certs c on d.Name = c.Name and d.Version = c.Version
Where d.Name NOT IN (Select c2.Name
From Certs c2
Where c2.WorkerId = 1)

此查询返回零行。问题是无论版本号如何,拆除行都会被排除在外。我想要的是将 IN 与元组一起使用:

Select Distinct d.Name, d.Version, d.Description
From CertDesc d join Certs c on d.Name = c.Name and d.Version = c.Version
Where (d.Name, d.Version) NOT IN (Select c2.Name, c2.Version)
From Certs c2
Where c2.WorkerId = 1)

不幸的是,这在 SQL Server 中是无效的。有人知道解决这个问题的好方法吗?

最佳答案

SELECT  *
FROM CertDesc cd
WHERE NOT EXISTS
(
SELECT NULL
FROM Certs c
WHERE c.WorkerId = 1
AND c.name = cd.name
AND c.version = cd.version
)

,或者,如果 nameversion 就足够了,就这样:

SELECT  name, version
FROM CertDesc
EXCEPT
SELECT name, version
FROM Certs
WHERE WorkerId = 1

编辑:后一个查询仅适用于 SQL-Server 2005。

关于SQL——对在多行键上连接的表使用 IN,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4809952/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com