我的查询很简单-它获取银行及其每月/每年的交易金额:
select bank_name,
sum(payment_sum),
CONCAT(MONTH(payment_due_date), '/', YEAR(payment_due_date)) as month_year
from bank
join finance
on bank.bank_id = finance.bank_id
group by bank_name, month_year
返回:
如何将行值(month_year)转换为列,然后将payment_sum也转换为相应的month_year生成的列?
我在找一种方法,每月为每家银行找到一笔付款。
我看了这个话题,但我的需求似乎不同。
最佳答案
不幸的是,MySQL没有将行转换成列的PIVOT函数,因此您需要将聚合函数与CASE表达式一起使用。
如果列数有限或已知,则查询的基本语法为:
select bank_name,
sum(case when month_year ='10/2011' then payment_sum else 0 end) `10/2011`,
sum(case when month_year ='11/2011' then payment_sum else 0 end) `11/2011`,
sum(case when month_year ='12/2011' then payment_sum else 0 end) `12/2011`
from
(
select bank_name,
payment_sum,
CONCAT(MONTH(payment_due_date), '/', YEAR(payment_due_date)) as month_year
from bank
join finance
on bank.bank_id = finance.bank_id
) d
group by bank_name;
但对于您的情况,听起来您的日期数目未知,因此您可能需要使用准备好的语句来创建动态sql:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(case when month_year = ''',
CONCAT(MONTH(payment_due_date), '/', YEAR(payment_due_date)),
''' then payment_sum end) AS `',
CONCAT(MONTH(payment_due_date), '/', YEAR(payment_due_date)), '`'
)
) INTO @sql
from finance;
SET @sql
= CONCAT('SELECT bank_name, ', @sql, '
from
(
select bank_name,
payment_sum,
CONCAT(MONTH(payment_due_date), ''/'', YEAR(payment_due_date)) as month_year
from bank
join finance
on bank.bank_id = finance.bank_id
) d
group by bank_name;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
关于mysql - 将行值转置为列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18684390/