如何重写多列 IN 子句以在 SQLite 上工作? [英] How can I rewrite a multi-column IN clause to work on SQLite?

问题描述

我有一个类似这样的查询:

I've got a query that looks something like this:

SELECT
  *
FROM table
WHERE
  (col1, col2) in (
    ('col1_val1', 'col2_val1'),
    ('col1_val2', 'col2_val2'),
    ('col1_val3', 'col2_val3'),
  )

这在 MySQL 中有效，但在 sqlite3 中失败并出现语法错误:

This works in MySQL, but fails in sqlite3 with a syntax error:

Error: near ",": syntax error

如何将此查询重写为适用于 sqlite3 的等效查询?

How can I rewrite this query to an equivalent one that works in sqlite3?

推荐答案

选择您喜欢的版本:

http://sqlfiddle.com/#!5/6169b/9

CREATE TEMPORARY TABLE pair (a INTEGER, b INTEGER);
INSERT INTO pair (a, b) VALUES (1, 1);
INSERT INTO pair (a, b) VALUES (2, 2);
....

数据成对；如果pair(a,b) 不是唯一的

data IN pairs; if pair(a,b) is not unique

SELECT *
FROM data
WHERE EXISTS (
  SELECT NULL
  FROM  pair
  WHERE pair.a = data.a
    AND pair.b = data.b
);

数据成对；如果pair(a,b) 是唯一的

data IN pairs; if pair(a,b) is unique

SELECT     data.*
FROM       data
INNER JOIN pair
        ON pair.a = data.a
       AND pair.b = data.b;

数据不成对；如果pair(a,b) 是唯一的

data NOT IN pairs; if pair(a,b) is unique

SELECT    data.*
FROM      data
LEFT JOIN pair
   ON pair.a = data.a
  AND pair.b = data.b
WHERE pair.a IS NULL
   OR pair.b IS NULL;

使用内联表

数据成对；如果pair(a,b) 不是唯一的

using inline table

data IN pairs; if pair(a,b) is not unique

SELECT *
FROM data
WHERE EXISTS (
  SELECT NULL
  FROM  (
    SELECT 1 AS a, 1 AS b
    UNION ALL
    SELECT 2 AS a, 2 AS b
    UNION ALL
    SELECT 3 AS a, 3 AS b
  ) AS pair
  WHERE pair.a = data.a
    AND pair.b = data.b
);

数据成对；如果pair(a,b) 是唯一的

data IN pairs; if pair(a,b) is unique

SELECT     data.*
FROM       data
INNER JOIN (
  SELECT 1 AS a, 1 AS b
  UNION ALL
  SELECT 2 AS a, 2 AS b
  UNION ALL
  SELECT 3 AS a, 3 AS b
) AS pair
 ON  pair.a = data.a
AND  pair.b = data.b;

数据不成对；如果pair(a,b) 是唯一的

data NOT IN pairs; if pair(a,b) is unique

SELECT    data.*
FROM      data
LEFT JOIN (
  SELECT 1 AS a, 1 AS b
  UNION ALL
  SELECT 2 AS a, 2 AS b
  UNION ALL
  SELECT 3 AS a, 3 AS b
 ) AS pair
   ON pair.a = data.a
  AND pair.b = data.b
WHERE pair.a IS NULL
   OR pair.b IS NULL;

这篇关于如何重写多列 IN 子句以在 SQLite 上工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！