在MYSQL存储过程中将UNION查询的结果保存在局部变量中

In my MySQL Stoared Procedure I want to store result of bellow query into local variables.

在我的MySQL Stoared过程中，我想将波纹管查询的结果存储到局部变量中。

MySQL SP

BEGIN
Declare temp_ID bigint;
Declare temp_teamName Text;
(select ID,team1 from tbl_tournament_matches where leveID = 1 and tournamentID = 91 and matchType = 'L')
UNION
(select ID,team1 from tbl_tournament_matches where leveID = 2 and tournamentID = 91 and looserTeam is not null)
ORDER BY RAND() LIMIT 0,1;
select temp_ID, temp_teamName;
END;

How can I pass result of query into local variable? note : above SP will return only 1 row.

如何将查询结果传递给局部变量？注意：上面的SP只返回1行。

1 个解决方案

#1

You can achieve this without having to store the value into a variable.

您无需将值存储到变量中即可实现此目的。

SELECT * FROM(

select ID,team1 from tbl_tournament_matches where leveID = 1 and tournamentID = 91 and matchType = 'L'

UNION 

select ID,team1 from tbl_tournament_matches where leveID = 2 and tournamentID = 91 and looserTeam is not null
) ORDER BY RAND() LIMIT 0,1

But, if you want to store a value for later use, you can use the INTO keyword:

但是，如果要存储值以供以后使用，可以使用INTO关键字：

 SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

http://dev.mysql.com/doc/refman/5.0/en/select-into.html

#1