如何从单张表查询数据并生成两列结构的结果？

你的SQL会返回两行重复结果，核心原因是from MAILTABLE会遍历表中所有行（当前表有2行），而两个子查询都独立返回单个值，所以每一行都会重复输出一遍PM和RM的结果。

以下是几种可行的解决方法：

方法一：替换数据源为单行结果 #

将from MAILTABLE替换为仅返回单行的数据源，不同数据库的写法略有差异：

Oracle 版本 #

select 
  (select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'PRIORITY') as PM,
  (select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'REGULAR') as RM
from dual;

MySQL/SQL Server 版本 #

select 
  (select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'PRIORITY') as PM,
  (select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'REGULAR') as RM
from (select 1 as dummy) t;

方法二：用聚合函数强制返回单行 #

利用max()或min()这类聚合函数，将多行结果合并为单行：

select 
  max((select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'PRIORITY')) as PM,
  max((select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'REGULAR')) as RM
from MAILTABLE;

方法三：条件聚合（推荐，扩展性更强） #

通过case语句结合聚合函数，直接在原表上进行条件筛选和聚合，后续新增其他邮件类型时只需添加新的case分支即可：

select
  max(case when MAIL_KIND = 'PRIORITY' then PRICE end) as PM,
  max(case when MAIL_KIND = 'REGULAR' then PRICE end) as RM
from MAILTABLE
where CODE = 'MAILCOST';

内容的提问来源于stack exchange，提问作者Hannah Reyes