sqlite 如何使两个子查询相交

zour9fqk 于 2023-10-23 发布在 SQLite

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

你好，我想与对方相交2长的，但我发现，你只能相交简单的。有可能得到这样的东西吗？

SELECT id, name as antibodyName 
FROM Antibodies 
WHERE id IN (
           (SELECT id FROM Antibodies WHERE name LIKE ?
                   UNION ALL
            SELECT antiId FROM AssignedColors WHERE name LIKE ?
                   UNION ALL
            SELECT antiId FROM AssignedReactivities WHERE name LIKE ?)
         INTERSECT
           (SELECT id FROM Antibodies WHERE name LIKE ?
                   UNION ALL
            SELECT antiId FROM AssignedColors WHERE name LIKE ?
                   UNION ALL
            SELECT antiId FROM AssignedReactivities WHERE name LIKE ?)
)
AND dateOfCreation >= ? AND dateOfCreation <= ?
ORDER BY dateOfCreation DESC LIMIT ? OFFSET ?;

实际上，我想得到其他查询的交集。

sqlite

来源：https://stackoverflow.com/questions/64214713/how-to-intersect-2-subqueries

2条答案

按热度按时间

uqdfh47h1#

你必须为每组联合查询使用SELECT语句：

SELECT id, name as antibodyName 
FROM Antibodies 
WHERE id IN (
  SELECT id FROM
  (
    SELECT id FROM Antibodies WHERE name LIKE ?
    UNION all
    SELECT antiId FROM AssignedColors WHERE name LIKE ?
    UNION all
    SELECT antiId FROM AssignedReactivities WHERE name LIKE ?
  )
  INTERSECT
  SELECT id FROM
  (
    SELECT id FROM Antibodies WHERE name LIKE ?
    UNION all
    SELECT antiId FROM AssignedColors WHERE name LIKE ?
    UNION all
    SELECT antiId FROM AssignedReactivities WHERE name LIKE ?
  )
) AND dateOfCreation >= ? AND dateOfCreation <= ?
ORDER BY dateOfCreation DESC LIMIT ? OFFSET?;

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

krugob8w2#

如果我正确理解了你的问题，INNER JOIN将有助于最有效地解决问题。可以将内部的SELECT更改为下面的代码，外部的SELECT可以保持不变。

(SELECT id FROM Antibodies WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedColors WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedReactivities WHERE name LIKE ?) A
JOIN
(SELECT id FROM Antibodies WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedColors WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedReactivities WHERE name LIKE ?) B
ON A.id = B.id

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

我来回答

sqlite 如何使两个子查询相交

2条答案

相关问题

热门标签

最新问答