如何在PostgreSQL中获取一行中的多个值？

Question

我在PostgreSQL 9.4中有一个名为emp_leave的数据库表，其结构如下：

|emp_name|leave_type|total_lday|
| Tame   |    PL    |     3    |
| Tame   |    UL    |     4    |

我希望查询结果如下所示：

|emp_name|paid_leave|unpaid_leave|
| Tame   |    3     |      4     |    

- Zakir Hossain

Answer 1

PostgreSQL 9.4还有一个FILTER子句，用于进行条件聚合：

select emp_name, 
       max(total_lday) FILTER (where leave_type = 'PL') as paid_leave,
       max(total_lday) FILTER (where leave_type = 'UL') as unpaid_leave
from emp_leave
group by emp_name

(ANSI SQL-2016，功能T612，“高级OLAP操作”。)

- jarlh

Answer 2

您可以尝试以下方法 - 使用条件聚合。

select emp_name, 
  max(case when leave_type='PL' then total_lday end) as paid_leave,
  max(case when leave_type='UL' then total_lday end) as unpaid_leave
from emp_leave
group by emp_name

- Fahmi