我知道T-SQL,但对MySQL来说还很陌生。我真的很想做这样的事情,也许在例程中使用子查询,也许是临时表。每个选择的结果都有多行,行数各不相同。

create procedure procname

select col1 into @var1 from t1 inner join t2 on t1.id = t2.id where t2.col2 = 1;
select col1 into @var2 from t1 inner join t2 on t1.id = t2.id where t2.col2 = 2;
select col1 into @var3 from t1 inner join t2 on t1.id = t2.id where t2.col2 = 3;

create temporary table tmpT (col1,col2,col3) insert (@var1, @var2, @var3);
select * from tmpT;


但是,我正在尝试的一切都抛出错误。抱歉,如果已经有人问过这个问题,但是我找不到有人问过的线程。哦,顺便说一句,我正在使用phpmyadmin,mysql版本5.6.x

最佳答案

您可以将MAXCASE语句一起使用:

select max(case when t2.col2 = 1 then col1 end) as col1,
       max(case when t2.col2 = 2 then col1 end) as col2,
       max(case when t2.col2 = 3 then col1 end) as col3
from t1 inner join t2 on t1.id = t2.id
where t2.col2 in (1,2,3)

09-30 17:37
查看更多