我知道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
最佳答案
您可以将MAX
与CASE
语句一起使用:
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)