带有列值总和的 MySQL 数据透视查询 [英] MySQL Pivot Query with Sum of column value
本文介绍了带有列值总和的 MySQL 数据透视查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
表 1(报告)- 数据结构
Table 1(reports) - Structure with Data
id distributorId clientId amount
1 1 162 2500
2 2 163 3500
3 3 203 4500
4 6 157 5500
5 1 162 1500
6 3 163 2000
7 3 162 1000
表 2(分销商)- 数据结构
Table 2(distributor) - Structure with Data
id distributor
1 Dis A
2 Dis B
3 Dis C
6 Dis D
表 3(客户)- 数据结构
Table 3(clients) - Structure with Data
id client_name
162 Client A
163 Client B
203 Client C
157 Client D
使用上面定义的 3 个表的期望输出:
Desired Output Using the above defined 3 tables:
client_name Dis A Dis B Dis C Dis D
Client A 4000 0 1000 0
Client B 0 3500 2000 0
Client C 0 0 4500 0
Client D 0 0 0 5500
推荐答案
对于固定的pivot sql,试试这个:
For the fixed pivot sql, try this:
select
t.client_name,
sum(if(t.distributor = 'Dis A', t.amount, 0)) as `Dis A`,
sum(if(t.distributor = 'Dis B', t.amount, 0)) as `Dis B`,
sum(if(t.distributor = 'Dis C', t.amount, 0)) as `Dis C`,
sum(if(t.distributor = 'Dis D', t.amount, 0)) as `Dis D`
from (
select c.id, c.client_name, d.distributor, r.amount
from clients c
left join reports r on c.id = r.clientId
left join distributor d on d.id = r.distributorId
)t
group by t.id
order by t.client_name
对于动态pivot sql,试试这个:
For the dynamic pivot sql, try this:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(if(t.distributor = ''',
distributor,
''', t.amount, 0)) AS `',
distributor ,'`'
)
) INTO @sql
FROM distributor;
SET @sql = CONCAT('SELECT t.client_name, ', @sql, ' FROM (
select c.id, c.client_name, d.distributor, r.amount
from clients c
left join reports r on c.id = r.clientId
left join distributor d on d.id = r.distributorId
)t
group by t.id
order by t.client_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
这篇关于带有列值总和的 MySQL 数据透视查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文