sql - 如何从 postgresSQL 中的一行获取列数据?
问题描述
我有数据库表名工资单
| Employee | | Period | | Attributes | | Amount |
| Zakir | | Oct'18 | | Basic | | 20000 |
| Zakir | | Oct'18 | | H.Rent | | 10000 |
| Zakir | | Oct'18 | | Convene | | 2000 |
| Zakir | | Oct'18 | | Medical | | 2000 |
| Tame | | Oct'18 | | Basic | | 30000 |
| Tame | | Oct'18 | | H.Rent | | 15000 |
| Tame | | Oct'18 | | Convene | | 2500 |
| Tame | | Oct'18 | | Medical | | 2500 |
我想要喜欢
| Employee | | Period | | Basic | | H.Rent| |Convene| |Medical| |Gross|
| Zakir | | Oct'18 | | 20000 | | 10000 | | 2000 | | 2000 | |34000|
| Tame | | Oct'18 | | 30000 | | 15000 | | 2500 | | 2500 | |50000|
解决方案
用例当
select employee ,period,
sum(case when attribute='basic' then amount else 0 end) basic,
sum(case when attribute='H.Rent' then amount else 0 end H_Rent,
sum(case when attribute='Convene' then amount else 0 end) Convene,
sum(case when attribute='Medical' then amount else 0 end) Medical,
sum(case when attribute='basic' then amount else 0 end)+
sum(case when attribute='H.Rent' then amount else 0 end)+
sum(case when attribute='Convene' then amount else 0 end)+
sum(case when attribute='Medical' then amount else 0 end) as gross
from payroll group by employee ,period
推荐阅读
- php - PHP REST API POSTMAN 致命错误未捕获 PDOException:SQLSTATE[42000]:语法错误或访问冲突:1064 您的 SQL 语法有错误
- javascript - Django 没有收到来自请求的 cookie
- azure-data-factory - Synapse / 数据工厂:自托管集成运行时是否在本地公开数据
- reporting-services - 是否可以通过 vb.net 创建历史快照?或者通过任何 API 调用?
- android - 带有 Flutter Rest Api 问题的可搜索下拉列表
- wordpress - 在 wordpress 中反向帖子列(不是移动响应反向)
- angular-cli - 当我运行 ng serve 时,出现以下错误。我试图在依赖项中添加 ("css-loader": "^2.1.0") 但效果很好
- css - 悬停时带有 ajax search pro 重叠页面的 Wordpress 大型菜单下拉菜单
- javascript - inline-block 元素内的内容如何影响其高度?
- python - 在 JupyterNotebook 热键中注释/取消注释多行