SQLite：如何获取匹配的IN列表和另一个列表

cdmah0mi 于 2023-03-23 发布在 SQLite

关注(0)|答案(2)|浏览(349)

我有三张table：
Employee (id, name)
Department (id, name)
DepartmentLink (id, employeeId, departmentId)
我正在尝试编写一个查询，该查询的结果是在列表A中至少有一个departmentId，并且在列表B中至少有一个departmentId。
我试过了，但没有结果：

SELECT
    Employee.id,
    DepartmentLink.departmentId
FROM DepartmentLink 
INNER JOIN Employee ON DepartmentLink.employeeId == Employee .id
WHERE DepartmentLink.departmentId IN (1,2,3)
AND DepartmentLink.departmentId IN (100)

结果应该是部门（1或2或3）和100中的Employee（或只是ID）。
这在SQLite中可能吗？

sqlite

来源：https://stackoverflow.com/questions/75803942/sqlite-how-to-get-a-match-in-list-and-another-list

2条答案

按热度按时间

vfwfrxfs1#

使用INTERSECT ...

SELECT employeeId
FROM departmentLink
WHERE departmentId in (1, 2, 3)
INTERSECT
SELECT employeeId
FROM departmentLink
WHERE departmentId in (100)

赞(0）回复(0）举报 2023-03-23

umuewwlo2#

可以使用两个exists条件：

SELECT e.id,
FROM   Employee e
WHERE  EXISTS (SELECT *
               FROM   DepartmentLink d1
               WHERE  d1.employeeId = e.id AND departmentId IN (1,2,3)) AND
       EXISTS (SELECT *
               FROM   DepartmentLink d2
               WHERE  d2.employeeId = e.id AND departmentId = 100)

赞(0）回复(0）举报 2023-03-23

我来回答

SQLite：如何获取匹配的IN列表和另一个列表

2条答案

相关问题

热门标签

最新问答