Mysql:在select语句中创建内联表?

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

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

Something like:

喜欢的东西:

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

that would output

将输出

|  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

I realize that I can do

我意识到我能做到。

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

But that seems pretty evil

但这似乎相当邪恶

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.

我不想做存储过程，因为可能a b 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.

我真正想要的是选择1，'a' UNION SELECT 2，'b' UNION SELECT 3，'c'具有更好的语法。

2 个解决方案

#1

The only ways i can remember now is using UNION or creating a TEMPORARY TABLE and inserting those values into it. Does it suit you?

我现在能记住的唯一方法是使用UNION或创建临时表并将这些值插入其中。它适合你吗?

TEMPORARY_TABLE (tested and it works):

TEMPORARY_TABLE(测试并工作):

Creation:

创造:

CREATE TEMPORARY TABLE MyInlineTable (id LONG, content VARCHAR(1) );

INSERT INTO MyInlineTable VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');

Usage:

用法:

SELECT 
  MyTable.*,
  MyInlineTable.CONTENT
FROM
  MyTable
  JOIN 
    SELECT * FROM MyInlineTable;
  ON MyTable.ID = MyInlineTable.ID

TEMPORARY_TABLES lifetime (reference):

TEMPORARY_TABLES一生(参考):

Temporary tables are automatically dropped when they go out of scope, unless they have already been explicitly dropped using DROP TABLE:

临时表在超出范围时自动删除，除非已使用DROP TABLE显式删除临时表:

。

All other local temporary tables are dropped automatically at the end of the current session.

所有其他本地临时表将在当前会话结束时自动删除。

。

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.`

当创建表的会话结束并且所有其他任务都停止引用时，全局临时表将自动删除。任务和表之间的关联仅用于单个Transact-SQL语句的生命。这意味着在最后一个Transact-SQL语句完成时，将删除一个全局临时表，该语句在创建会话时主动引用表。

#2

-1

Yes. Do with stored procedures or views.

是的。处理存储过程或视图。

#1