如何在 Access SQL 中执行 INTERSECT 查询

Question

我有这个Python代码片段，用于查询SQLite表'note'，'tag'和多对多连接表'fkeys'。我猜非常程序化，但它有效。现在我想在 Access 中做类似的事情，但 Jet SQL 不支持 INTERSECT 。

我已经在这里尝试了类似帖子的代码几个小时。WHERE EXISTS或GROUP BY ... HAVING +子查询都不起作用。SQL-92选项也没有帮助。这可以在 Jet SQL 中完成吗？

  db.execute('SELECT DISTINCT n.rowid as rowid, n.note_txt as note_txt,
  date(n.timestamp) as timestamp
  FROM note n
  JOIN fkeys f
  ON n.rowid = f.note_id
  JOIN tag t
  ON t.rowid = f.tag_id
  WHERE t.tag_text = ?
INTERSECT  
  SELECT DISTINCT n.rowid as rowid, n.note_txt as note_txt,
  date(n.timestamp) as timestamp
  FROM note n
  JOIN fkeys f
  ON n.rowid = f.note_id
  JOIN tag t
  ON t.rowid = f.tag_id
  WHERE t.tag_text = ?
INTERSECT
  SELECT DISTINCT n.rowid as rowid, n.note_txt as note_txt,
  date(n.timestamp) as timestamp
  FROM note n
  JOIN fkeys f
  ON n.rowid = f.note_id
  JOIN tag t
  ON t.rowid = f.tag_id
  WHERE t.tag_text = ?
  ORDER BY timestamp',[srchtxt0,srchtxt1,srchtxt2])

Answer 1

我相信以下内容应该适用于 ACE/Jet 数据库：

sql = """
SELECT rowid, note_txt, timestamp
FROM [note]
WHERE
    rowid IN
        (
            SELECT f.note_id
            FROM 
                [fkeys] AS f 
                INNER JOIN 
                [tag] AS t 
                    ON t.rowid = f.tag_id 
            WHERE t.tag_text = ?
        )
        AND rowid IN
            (
                SELECT f.note_id
                FROM 
                    [fkeys] AS f 
                    INNER JOIN 
                    [tag] AS t 
                        ON t.rowid = f.tag_id 
                WHERE t.tag_text = ?
            )
        AND rowid IN
            (
                SELECT f.note_id
                FROM 
                    [fkeys] AS f 
                    INNER JOIN 
                    [tag] AS t 
                        ON t.rowid = f.tag_id 
                WHERE t.tag_text = ?
            )
ORDER BY 3 
"""
cursor1 = db.execute(sql,[srchtxt0,srchtxt1,srchtxt2])