如何使用case语句在MySQL中透视表? [英] How to pivot a table in MySQL using case statements?

问题描述

我正在尝试使用case语句在MySQL中透视表.这个问题在这里被问过很多次了，我已经研究了所有这些答案，但是我正在寻找一种解决方案:

1.使用案例陈述.不是自我联接，子查询或联合.
2.仅使用SQL.不是Excel或Shell脚本.
3.在MySQL上运行.

I am trying to pivot a table in MySQL using case statements. This question has been asked many times here, and I have studied all of those answers, but I am looking for a solution that:

1. Uses case statements. Not self joins, subqueries, or unions.
2. Uses just SQL. Not Excel or shell scripts.
3. Works on MySQL.

这是桌子:

create table client (
  name varchar(10),
  revenue int(11),
  expense int(11)
);

insert into client (name, revenue, expense) values ("Joe", 100, 200);
insert into client (name, revenue, expense) values ("Bill", 300, 400);
insert into client (name, revenue, expense) values ("Tim", 500, 600);

mysql> select * from client;
+------+---------+---------+
| name | revenue | expense |
+------+---------+---------+
| Joe  |     100 |     200 |
| Bill |     300 |     400 |
| Tim  |     500 |     600 |
+------+---------+---------+

我想将桌子转到此位置

+-----+------+-----+
| Joe | Bill | Tim |
| 100 | 300  | 500 |
| 200 | 400  | 600 |
+-----+------+-----+

我该怎么做?

我已经在artfulsoftware dot com和buysql dot com上看到了解决方案，但是这些解决方案不适用于我的桌子.

I have already seen the solutions at artfulsoftware dot com and buysql dot com, but those solutions are not working for my table.

推荐答案

在此处查看小提琴演示

select 
sum(case when name='Joe' then revenue else 0 end) as JOE,
sum(case when name='Bill' then revenue else 0 end) as Bill,
sum(case when name='Tim' then revenue else 0 end) as TIM

from client

union

select 
sum(case when name='Joe' then expense else 0 end) as JOE,
sum(case when name='Bill' then expense else 0 end) as Bill,
sum(case when name='Tim' then expense else 0 end) as TIM

from client

这篇关于如何使用case语句在MySQL中透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！