mysql - 将联合查询返回到多列

Question

我有 4 个不同的选择语句，它们合并结果并显示订单，如下所示。

lab_orders              MED111
lab_orders              MED262383
mg_orders               MED262383
mg_orders               UNT222
pcg_pharmacogenetics    MED262371
pcg_pharmacogenetics    UNT248234
well_wellness           UST601061
well_wellness           UNTAccession
well_wellness           UST333

使用此查询。

select 'mg_orders',csv as csv1
from (select csv from csvdata where csv not in(select orderid from mg_orders)) as t1
union
select 'lab_orders',csv as csv2
from (select csv from csvdata where csv not in(select orderid from lab_orders)) as t2
union
select 'pcg_pharmacogenetics',csv as csv3
from (select csv from csvdata where csv not in(select orderid from pcg_pharmacogenetics)) as t3
union
select 'well_wellness',csv as csv4
from (select csv from csvdata where csv not in(select orderid from well_wellness)) as t4

我想要做的是在单独的列中返回结果集，我已尝试以下操作，但得到“没有名为 csv2 的列”

select csv1, csv2, csv3, csv4
from(
select 'mg_orders',csv as csv1
from (select csv from csvdata where csv not in(select orderid from mg_orders)) as t1
union
select 'lab_orders',csv as csv2
from (select csv from csvdata where csv not in(select orderid from lab_orders)) as t2
union
select 'pcg_pharmacogenetics',csv as csv3
from (select csv from csvdata where csv not in(select orderid from pcg_pharmacogenetics)) as t3
union
select 'well_wellness',csv as csv4
from (select csv from csvdata where csv not in(select orderid from well_wellness)) as t4
) as t


   lab_orders       mg_orders     pcg_pharmacogenetics            
   MED111           MED262383     MED262371
   MED262383        UNT222        UNT248234

Answer 1

当您使用union时，第一个查询中的列名或别名将用于结果集。在您的情况下，结果集将包含 mg_orders 和 csv1 作为列标题。

我假设您正在寻找类似的查询

select 
max(case when csv not in(select orderid from mg_orders) then csv end) as csv1,
max(case when csv not in(select orderid from lab_orders) then csv end) as csv2,
max(case when csv not in(select orderid from pcg_pharmacogenetics) then csv end) as csv3,
max(case when csv not in(select orderid from well_wellness) then csv end) as csv4
from csvdata