MySQL:在选择语句内创建内联表? [英] Mysql: Create inline table within select statement?

问题描述

MySql中是否有一种方法可以创建用于联接的内联表?

Is there a way in MySql to create an inline table to use for join?

类似的东西:

SELECT LONG [1,2,3] as ID, VARCHAR(1) ['a','b','c'] as CONTENT

将会输出

|  ID  |  CONTENT  |
| LONG | VARCHAR(1)|
+------+-----------+
|   1  |    'a'    |
|   2  |    'b'    |
|   3  |    'c'    |

我可以在这样的联接中使用:

and that I could use in a join like this:

SELECT 
  MyTable.*,
  MyInlineTable.CONTENT
FROM
  MyTable
  JOIN 
    (SELECT LONG [1,2,3] as ID, VARCHAR(1) ['a','b','c'] as CONTENT MyInlineTable)
  ON MyTable.ID = MyInlineTable.ID

我意识到我可以做到

SELECT 1,'a' UNION SELECT 2,'b' UNION SELECT 3,'c'

但这似乎很邪恶

我不想执行存储过程，因为在每次查询时，a，b，c都可能会发生变化，数据的大小也可能会发生变化.另外，存储过程也需要保存在数据库中，我不想只为此修改数据库. 视图是一回事.

I don't want to do a stored procedure because potentially a,b,c can change at every query and the size of the data as well. Also a stored procedure needs to be saved in the database, and I don't want to have to modify the database just for that. View is the same thing.

我真正要寻找的是用更好的语法完成SELECT 1,'a' UNION SELECT 2,'b' UNION SELECT 3,'c'的事情.

What I am really looking for is something that does SELECT 1,'a' UNION SELECT 2,'b' UNION SELECT 3,'c' with a nicer syntax.

推荐答案

我真正要寻找的是具有更好的语法的SELECT 1，'a'UNION SELECT 2，'b'UNION SELECT 3，'c'.

是的，可以使用 行构造器 :

Yes, it is possible with ROW CONSTRUCTOR introduced in MySQL 8.0.19:

VALUES ROW (1,'a'), ROW(2,'b'), ROW(3,'c') 

并带有JOIN:

SELECT *
FROM tab 
JOIN (VALUES ROW (1,'a'), ROW(2,'b'), ROW(3,'c') ) sub(id, content)
  ON tab.id = sub.id;

db<>小提琴演示

db<>fiddle demo

这篇关于MySQL:在选择语句内创建内联表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！