>我有以下格式的数据:
date
(月/日/年)、desc
(明细)、category
(a、b、c)、tran_type
(借方、贷方)、amount spent
(金额)。
我想以以下格式获取数据:
category || tran_type || Jan_total || feb_total || mar_total
A || debit || $101 || $201 || $302
A || credit || $500 || $600 || $200
此查询应提供所需的结果。
SELECT category
,tran_type
,SUM(IF(month(date) = 1,ABS(amount),0)) as jan_total
,SUM(IF(month(date) = 2,ABS(amount),0)) as feb_total
,SUM(IF(month(date) = 3,ABS(amount),0)) as mar_total
,SUM(IF(month(date) = 4,ABS(amount),0)) as apr_total
,SUM(IF(month(date) = 5,ABS(amount),0)) as may_total
,SUM(IF(month(date) = 6,ABS(amount),0)) as jun_total
,SUM(IF(month(date) = 7,ABS(amount),0)) as jul_total
,SUM(IF(month(date) = 8,ABS(amount),0)) as aug_total
,SUM(IF(month(date) = 9,ABS(amount),0)) as sep_total
,SUM(IF(month(date) = 10,ABS(amount),0)) as okt_total
,SUM(IF(month(date) = 11,ABS(amount),0)) as nov_total
,SUM(IF(month(date) = 12,ABS(amount),0)) as dec_total
FROM transactions
WHERE YEAR(date) = '2011'
GROUP BY category, tran_type
如果您不想遇到麻烦,请不要忘记按年份过滤。
那么年份呢?是否希望 1 月列添加 2011 年 1 月与 2010 年 1 月的交易记录?
我假设您需要单独添加年份列或使用 WHERE 子句,因此在示例中包括了两个选项:
基本上,您需要生成十二个子查询才能实现这一点,并且SQL看起来很混乱,并且不是完成此操作的常规方法:
SELECT category, YEAR(date), tran_type,
(SELECT SUM(amounts) FROM TableName s1 WHERE YEAR(t.date)=YEAR(s1.date) AND MONTH(s1.date)=1 AND t.category=s1.category AND t.tran_type=s1.tran_type) AS 'Jan_Total',
(SELECT SUM(amounts) FROM TableName s2 WHERE YEAR(t.date)=YEAR(s2.date) AND MONTH(s2.date)=2 AND t.category=s2.category AND t.tran_type=s2.tran_type) AS 'Feb_Total',
....REPEAT ABOVE 2 LINES FOR EACH MONTH
FROM TableName t
WHERE t.date>'2011-01-01'
GROUP BY t.category, YEAR(t.date), t.tran_type;
如前所述,上面并不完全优雅,更好的解决方案是使用以下 SQL 并将表示层中的数据格式化给用户:
SELECT category, YEAR(date), MONTH(date), tran_type, SUM(amounts)
FROM TableName
GROUP BY TableName;
希望有帮助。
对于单个月(2011 年 1 月),您可以使用:
SELECT category, tran_type, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-01-01" AND date<"2011-02-01" GROUP BY category, tran_type;
假设您至少有一个索引 date
那么这应该相当快。(date, category, tran_type)
更好的索引,因为这也有助于分组。
如果要以所描述的格式组合多个月份,可以将这些查询中的许多组合为子查询,如下所示:
SELECT jan.category, jan.tran_type, jan.total, feb.total FROM
(SELECT category, tran_type, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-01-01" AND date<"2011-02-01" GROUP BY category, tran_type) AS jan,
(SELECT category, tran_type, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-02-01" AND date<"2011-03-01" GROUP BY category, tran_type) AS feb
WHERE jan.category = feb.category
AND jan.tran_type = feb.tran_type;
虽然我怀疑获取所需数据的最简单方法(尽管效率不高,格式也不完全符合您描述的格式)是:
SELECT category, tran_type, MONTH(date) AS theMonth, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-01-01" AND date<"2012-01-01" GROUP BY category, tran_type, theMonth;
MONTH()
函数的这种使用远非最佳选择,但如果您不运行查询,这通常是要走的路。