多个别名，一列

Question

我正在尝试为同一列提供多个别名，基本上，我希望这两个查询是一个：

SELECT name AS singlePeople FROM People
JOIN ID FROM Numbers
ON People.ID=Numbers.ID
WHERE People.isMarried=f;

SELECT name AS marriedPeople FROM People
JOIN ID FROM Numbers
ON People.ID=Numbers.ID
WHERE People.isMarried=t;

我希望我的结果看起来像：

singlePeople    marriedPeople
-------------   --------------
Bob Kelly        John SMith
John Adams

Answer 1

这就够了吗？

SELECT (CASE WHEN p.isMarried THEN 'Married' ELSE 'Single' END) as which,
       name
FROM People p JOIN 
     Numbers n
     ON p.ID = n.ID;

如果没有，您可以使用变量执行此操作：

select max(case when not ismarried then name end) as single,
       max(case when ismarried then name end) as married
from (select name, p.ismarried,
             (@rn := if(@i = ismarried, @rn + 1,
                        if(@i := ismarried, 1, 1)
                       )
             ) as seqnum
      from people p join
           numbers n
           on p.id = n.id cross join
           (select @i := NULL, @rn := 0) params
      order by ismarried
     ) pn
group by rn;