MySQL 动态创建内联 SQL 表（用于排除左连接）

Create an inline SQL table on the fly (for an excluding left join)

提问by rinogo

Let's assume the following:

让我们假设以下情况：

Table A

表A

id | value
----------
1   | red
2   | orange
5   | yellow
10  | green
11  | blue
12  | indigo
20  | violet

I have a list of id's (10, 11, 12, 13, 14) that can be used to look up id's in this table. This list of id's is generated in my frontend.

我有一个 id 列表（10、11、12、13、14），可用于在此表中查找 id。这个 id 列表是在我的前端生成的。

Using purely SQL, I need to select the id's from this list (10, 11, 12, 13, 14) that do not have entries in Table A (joining on the 'id' column). The result should be the resultset of id's 13 and 14.

使用纯 SQL，我需要从此列表 (10, 11, 12, 13, 14) 中选择表 A 中没有条目的 ID（连接到“id”列）。结果应该是 id 的 13 和 14 的结果集。

How can I accomplish this using only SQL? (Also, I'd like to avoid using a stored procedure if possible)

如何仅使用 SQL 完成此操作？（另外，如果可能的话，我想避免使用存储过程）

The only approach I can think of is something that would create an inline SQL table on the fly to temporarily hold my list of id's. However, I have no idea how to do this. Is this possible? Is there a better way?

我能想到的唯一方法是动态创建一个内联 SQL 表来临时保存我的 id 列表。但是，我不知道该怎么做。这可能吗？有没有更好的办法？

Thanks! :)

谢谢！:)

采纳答案by eggyal

You can create an "inline table" with a UNIONsubquery:

您可以使用UNION子查询创建“内联表” ：

(
            SELECT 10 AS id
  UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
  -- etc.
) AS inline_table

回答by phillip voyle

You can do this from SQL Server 2008 onwards using a table value constructor.

您可以使用表值构造函数从 SQL Server 2008 开始执行此操作。

SELECT * FROM (
   VALUES(1, 'red'),
         (2, 'orange'),
         (5, 'yellow'),
         (10, 'green'),
         (11, 'blue'),
         (12, 'indigo'),
         (20, 'violet'))
   AS Colors(Id, Value)

More information here: Table Value Constructor

此处的更多信息： 表值构造函数

回答by Quassnoi

CREATE TEMPORARY TABLE ids (id INT NOT NULL PRIMARY KEY);

INSERT
INTO    ids
VALUES
(10),
(11),
(12),
(13),
(14);

SELECT  *
FROM    ids
WHERE   id NOT IN
        (
        SELECT  id
        FROM    a
        );

回答by user3417837

create table B (id int)
insert into B values (10),(11),(12),(13),(14)

select *
from B
left join A 
on A.id=B.id
where A.id is null

drop table B

http://sqlfiddle.com/#!6/6666c1/30

http://sqlfiddle.com/#!6/6666c1/30