mysql - 高级 SQL 选择获取具有不同标题名称的相同列值

Question

我正在尝试从具有四列的表 T1 创建一个选择查询，如下所示

• 文件名
• agnt_name
• agnt_lot_num
• agnt_serial_num

我的目标是获得一个选择输出表，其列名称为 synoynym ，如下所示

文件名，'UDAV'，'Hemat'，'CCI'

我面临的挑战性条件是:

UDAV - SELECT agnt_serial_num FROM T1 WHERE agnt_name ='UV inhibitor'
Hemat - SELECT agnt_serial_num FROM T1 WHERE agnt_name ='hematoxylin'
CCI - SELECT concat(agnt_serial_num,'_',agnt_lot_num) FROM T1 WHERE agnt_name ='Carbon Morpher'

每个“agnt_name(s)”对于文件名来说都是唯一的。任何帮助将不胜感激

Answer 1

我猜:

select a.file_name
, a.sn as 'UDAV'
, b.sn as 'Hemat'
, c.sn as 'CCI'
from 
( SELECT agnt_serial_num sn FROM T1 WHERE agnt_name ='UV inhibitor' ) a
,( SELECT agnt_serial_num sn FROM T1 WHERE agnt_name ='hematoxylin' )b
,( SELECT concat(agnt_serial_num,'_',agnt_lot_num) sn FROM T1 WHERE agnt_name ='Carbon Morpher' ) c
where a.file_name = b.file_name
and b.file_name = c.file_name