你能否定义SQL中的“字面量”表？

Question

是否有SQL子查询语法可以直接定义临时表？

例如：

SELECT
  MAX(count) AS max,
  COUNT(*) AS count
FROM
  (
    (1 AS id, 7 AS count),
    (2, 6),
    (3, 13),
    (4, 12),
    (5, 9)
  ) AS mytable
  INNER JOIN someothertable ON someothertable.id=mytable.id

这将避免执行两到三个查询：创建临时表、将数据放入其中，然后在连接中使用它。

我正在使用MySQL，但对其他能够执行类似操作的数据库也感兴趣。

- thomasrutter

Answer 1

我想你可以使用带有多个SELECT和UNION组合的子查询。

SELECT a, b, c, d
FROM (
    SELECT 1 AS a, 2 AS b, 3 AS c, 4 AS d
    UNION ALL 
    SELECT 5 , 6, 7, 8
) AS temp;

- Blixt

---

哇，那是一个非常有创意的解决方案！听起来至少可以有效。 - thomasrutter

Answer 2

你可以在PostgreSQL中完成:

=> select * from (values (1,7), (2,6), (3,13), (4,12), (5,9) ) x(id, count);
 id | count 
----+-------
  1 |     7
  2 |     6
  3 |    13
  4 |    12
  5 |     9

http://www.postgresql.org/docs/8.2/static/sql-values.html

- dvv

---

PostgreSQL 无敌。 - Dan Lenski

也适用于Spark SQL。 - jab

问题涉及MySQL。 - Stijn de Witt

起初我认为x是一些特殊的PostgreSQL东西，直到我意识到它只是与 as x (id, count) 相同。 - lmsurprenant

Answer 3

在 Microsoft T-SQL 2008 中，格式为：

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)

即如Jonathan所述，但不包括“table”关键字。

参见：

• FROM (T-SQL MSDN文档)（在派生表下），以及
• Table Value Constructor (T-SQL MSDN文档)

- Pete

---

这个可以运行，但我想用同样的方法创建第二个表并将它们连接在一起，但是我似乎无法得到正确的语法... - Michael

Answer 4

在标准SQL中（SQL 2003 - 详见http://savage.net.au/SQL/），你可以使用：

INSERT INTO SomeTable(Id, Count) VALUES (1, 7), (2, 6), (3, 13), ...

稍加追踪，您还可以使用：

SELECT * FROM TABLE(VALUES (1,7), (2, 6), (3, 13), ...) AS SomeTable(Id, Count)

无论这些是否在MySQL中可行，这是一个独立的问题 - 但你可以随时请求添加它，或者自己添加（这就是开放源代码的美妙之处）。

无论这些在MySQL中是否可行，这都是一个独立的问题 - 但你可以随时请求添加它，或者自行添加（这就是开放源代码的优越性）。

- Jonathan Leffler

---

谢谢回答！不幸的是，MySQL(5.0) 不支持 FROM TABLE(VALUES...，但至少我现在知道了。 - thomasrutter

这个答案可能比被采纳的答案更具有信息量，只是因为我使用的是MySQL，它并没有解决我的个人问题得那么好。我希望我能够接受多个答案。 - thomasrutter

相反，开源的缺点是，除非有很多人和/或有空闲时间的开发人员感兴趣，否则提问会被忽视或拒绝，并且自己动手的学习曲线通常需要很长时间，这种方式并不一定比其他方式更好。 - Michael

删除关键字“TABLE”，这将适用于MS SQL Server 2008及以上版本。 - Zarepheth

Answer 5

自MariaDB v10.3.3和MySQL v8.0.19以来，您现在可以实现这一点！

参见文档：MariaDB、MySQL

MariaDB：

WITH literaltable (id,count) AS (VALUES (1,7),(2,6),(3,13),(4,12),(5,9))
SELECT MAX(count) AS max,COUNT(*) AS count FROM literaltable

我在这里使用了 WITH，因为MariaDB没有为 VALUES ... 提供良好的列名。您可以在没有列名的情况下在联合中使用它：

SELECT 1 AS id,7 AS count UNION ALL VALUES (2,6),(3,13),(4,12),(5,9) ORDER BY count DESC

虽然文档似乎没有提到，但你甚至可以将它用作顶级查询。

VALUES (1,7),(2,6),(3,13),(4,12),(5,9) ORDER BY 2 DESC

实际列名实际上是值的第一行，因此您甚至可以这样做（尽管不够优雅，而且可能会遇到重复的列名错误）：

SELECT MAX(`7`) AS max,COUNT(*) AS count FROM (VALUES (1,7),(2,6),(3,13),(4,12),(5,9)) literaltable

---

MySQL:

我现在没有MySQL v8.0.19的实例可供测试，但根据文档的说法，下面任一选项都应该可以：

SELECT MAX(column_1) AS max,COUNT(*) AS count FROM (VALUES ROW(1,7), ROW(2,6), ROW(3,13), ROW(4,12), ROW(5,9)) literaltable

SELECT MAX(data) AS max,COUNT(*) AS count FROM (VALUES ROW(1,7), ROW(2,6), ROW(3,13), ROW(4,12), ROW(5,9)) literaltable(id,data)

与MariaDB不同，MySQL提供自动列名column_0、column_1、column_2等，并且在引用子查询时还支持重命名所有的子查询列。

我不确定，但这个开发工作日志页面似乎表明MySQL也已经实现了更短的语法（像MariaDB那样省略"ROW"），或者他们将在不久的将来实现。

- Doin

---

在8.0.19版本的发布页面中，他们表示：""" 由于与VALUES()函数冲突，这是MySQL的一个非标准特性，我们决定使用SQL标准详细形式的表值构造函数：VALUES ROW(1, 2) """ 所以不幸的是，我认为他们没有计划支持不带ROW的VALUES。 - matrix10657

Answer 6

我发现了这个链接：使用MySQL临时表

CREATE TEMPORARY TABLE TempTable ( ID int, Name char(100) ) TYPE=HEAP; 

INSERT INTO TempTable VALUES( 1, "Foo bar" ); 

SELECT * FROM TempTable; 

DROP TABLE TempTable;

- ole6ka

---

只是想知道这是否是MySQL的唯一答案。和其他评论者处于相同的情况，想知道除了临时表之外是否有不同的构造可以使用，比如一种列出数据的选择查询方式，但我猜这仍然只是内存中的数据，就像这个例子一样。 - Pysis

Answer 7

总的来说，是的。在我看来，如果您的SQL产品支持公共表达式（CTE），那就更好了，因为它比使用子查询更容易阅读，而且同一个CTE可以多次使用。例如，在SQL Server 2005及以上版本中，可以使用以下代码“创建”一个介于0和999之间的唯一整数序列表：

WITH Digits (nbr) AS 
(
 SELECT 0 AS nbr UNION ALL SELECT 1 UNION ALL SELECT 2 
 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 
 UNION ALL SELECT 9 
), 
Sequence (seq) AS
(
 SELECT Units.nbr + Tens.nbr + Hundreds.nbr 
   FROM Digits AS Units
        CROSS JOIN Digits AS Tens
        CROSS JOIN Digits AS Hundreds
)
SELECT S1.seq 
  FROM Sequence AS S1;

除非您会对序列表执行某些有用的操作，例如从基本表中的VARCHAR列解析字符。

但是，如果您多次使用此仅包含文字值的表或在多个查询中使用它，则为什么不首先将其制作成基本表呢？ 我所使用的每个数据库都有一个整数序列表（通常为100K行），因为它通常非常有用。

- onedaywhen

Answer 8

创建临时表 (ID int，Name char(100)) SELECT ....

阅读更多：http://dev.mysql.com/doc/refman/5.0/en/create-table.html

(在底部附近)

这样做的好处是，如果填充表格时出现任何问题（数据类型不匹配），则表格会自动删除。

早期的答案使用了FROM SELECT子句。如果可能，请使用它，因为它可以避免清理表格的麻烦。

FROM SELECT的缺点（可能无关紧要）是创建的数据集有多大。临时表允许索引，这可能是至关重要的。对于后续查询。看起来违反直觉，但即使是中等大小的数据集（约1000行），为查询创建索引也可能更快。

- Pat