根据类别的Mysql年度报告，列中包含月份名称 [英] Mysql Yearly Report according to Categories with month name in columns
问题描述
我有2桌收入和费用
表：收入
------------- -----------------------------------------
transactionID | transactionDate |分公司| TotalAMount
----------------------------------------- -------------
1 | 2013-01-01 | MC | 23900
----------------------------------------- -------------
2 | 2013-01-02 | MC | 12500
----------------------------------------- -------------
3 | 2013-02-11 | MC | 1735
----------------------------------------- -------------
4 | 2013-02-15 | MC | 9000
----------------------------------------- -------------
5 | 2013-03-01 | MC | 1100
----------------------------------------- -------------
6 | 2013-04-21 | MC | 45690
----------------------------------------- -------------
7 | 2013-05-01 | MC | 9900
----------------------------------------- -------------
.....和收入列表下降....
表：expenserecord
-------------------------------------- ----------------
id | transactionID |分类|项目|金额
----------------------------------------- -------------
1 | 1 |茶点| 1500
----------------------------------------- -------------
2 | 1 |巡逻| 2000
----------------------------------------- -------------
3 | 2 |货物| 350
----------------------------------------- -------------
4 | 3 |广告| 1200
----------------------------------------- -------------
.....和ExpenseRecord列表下降....
我的问题是如何获得下面提到的所需输出
1.给我12个月费用报告类别明智。 （可能如下所示）
类别| Jan |二月|三月
---------------- | ------ | -------- | -------
货物| 350 | 0 | 0
茶点| 1500 | 0 | 6374
广告| 0 | 1200 | 0
.....和产品清单下降....
I have 2 tables revenue and expense
Table: Revenue
------------------------------------------------------
transactionID | transactionDate | Branch | TotalAMount
------------------------------------------------------
1 | 2013-01-01 | MC | 23900
------------------------------------------------------
2 | 2013-01-02 | MC | 12500
------------------------------------------------------
3 | 2013-02-11 | MC | 1735
------------------------------------------------------
4 | 2013-02-15 | MC | 9000
------------------------------------------------------
5 | 2013-03-01 | MC | 1100
------------------------------------------------------
6 | 2013-04-21 | MC | 45690
------------------------------------------------------
7 | 2013-05-01 | MC | 9900
------------------------------------------------------
..... and Revenue lists goes down ....
Table : expenserecord
------------------------------------------------------
id |transactionID | Category| Item| Amount
------------------------------------------------------
1 | 1 | Refreshment | 1500
------------------------------------------------------
2 | 1 | Patrol | 2000
------------------------------------------------------
3 | 2 | Cargo | 350
------------------------------------------------------
4 | 3 | Advertisement | 1200
------------------------------------------------------
..... and ExpenseRecord lists goes down ....
my question is how to get desired output mentioned below
1. Give me 12 months Expense report Category wise. (It may look like below)
Category | Jan | Feb | March
----------------| ------ | -------- | -------
Cargo | 350 | 0 | 0
Refreshment | 1500 | 0 | 6374
Advertisement | 0 | 1200 | 0
..... and product lists goes down ....
推荐答案
这可能不是最好的答案，但至少你可以了解如何处理你的报告，而不是使用支点。
This may not be the best answer but at least you can get an idea on how to work on
your report, and not using pivot.
SELECT A.[Category],
isnull(SUM([JANUARY]),'') as [JANUARY],
isnull(SUM([FEBRUARY]),'') as [FEBRUARY],
isnull(SUM([MARCH]),'') as [MARCH],
isnull(SUM([APRIL]),'') as [APRIL],
isnull(SUM([MAY]),'') as [MAY],
isnull(SUM([JUNE]),'') as [JUNE],
isnull(SUM([JULY]),'') as [JULY],
isnull(SUM([AUGUST]),'') as [AUGUST],
isnull(SUM([SEPTEMBER]),'') as [SEPTEMBER],
isnull(SUM([OCTOBER]),'') as [OCTOBER],
isnull(SUM([NOVEMBER]),'') as [NOVEMBER],
isnull(SUM([DECEMBER]),'') as [DECEMBER]
FROM (SELECT EXPENSERECORD.Category [Category],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'January' THEN SUM(EXPENSERECORD.AMOUNT) END AS [JANUARY],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'February' THEN SUM(EXPENSERECORD.AMOUNT) END AS [FEBRUARY],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'March' THEN SUM(EXPENSERECORD.AMOUNT) END AS [March],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'April' THEN SUM(EXPENSERECORD.AMOUNT) END AS [APRIL],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'May' THEN SUM(EXPENSERECORD.AMOUNT) END AS [MAY],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'June' THEN SUM(EXPENSERECORD.AMOUNT) END AS [JUNE],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'July' THEN SUM(EXPENSERECORD.AMOUNT) END AS [JULY],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'August' THEN SUM(EXPENSERECORD.AMOUNT) END AS [AUGUST],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'September' THEN SUM(EXPENSERECORD.AMOUNT) END AS [SEPTEMBER],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'October' THEN SUM(EXPENSERECORD.AMOUNT) END AS [OCTOBER],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'November' THEN SUM(EXPENSERECORD.AMOUNT) END AS [NOVEMBER],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'December' THEN SUM(EXPENSERECORD.AMOUNT) END AS [DECEMBER]
FROM REVENUE
LEFT JOIN EXPENSERECORD ON REVENUE.transactionID = EXPENSERECORD.transactionID
GROUP BY REVENUE.transactionDate, EXPENSERECORD.Category) A
WHERE A.Category IS NOT NULL
GROUP BY A.Category
SELECT
er.category`Category`，
SUM（IF（'2013-01-01'和'2013-01-31'之间的transactionDate， r.totalamount，0））Jan，
SUM（IF（'2013-02-01'和'2013-02-31'之间的事务日期，r.totalamount，0））2月，
SUM（IF（'2013-03-01'和'2013-03-31'之间的transactionDate，r.totalamount，0））Mar，
SUM（IF（transactionDate）在'2013-04-01'和'2013-04-31'之间，r.totalamount，0））4月，
SUM（IF（'2013-05-01'和'2013之间的交易日期） -05-31'，r.totalamount，0））5月，
SUM（IF（2013年之间的transactionDate） -06-01'和'2013-06-31'，r.totalamount，0））Jun，
SUM（IF（'2013-07-01'和'2013-07-之间的交易日期 - 31'，r.totalamount，0））Jul，
SUM（IF（'2013-08-01'和'2013-08-31'之间的transactionDate，r.totalamount，0））8月，
SUM（IF（'2013-09-01'和'2013-09-31'之间的交易日期，r.totalamount，0））9月，
SUM（ IF（'2013-10-01'和'2013-10-31'之间的交易日期，r.totalamount，0））10月，
SUM（IF（'2013-11-01'之间的交易日期'和'2013-11-31'，r.totalamount，0））11月，
SUM（IF（'2013-12-01'和'2013-12-31'之间的交易日期，r。 totalamount，0））Dece
来自收入r
LEFT JOIN expenserecord er ON R.transactionID = ER.transactionID
GROUP BY
er.category
SELECT
er.category `Category`,
SUM(IF(transactionDate between '2013-01-01' and '2013-01-31', r.totalamount, 0)) Jan,
SUM(IF(transactionDate between '2013-02-01' and '2013-02-31', r.totalamount, 0)) Feb,
SUM(IF(transactionDate between '2013-03-01' and '2013-03-31', r.totalamount, 0)) Mar,
SUM(IF(transactionDate between '2013-04-01' and '2013-04-31', r.totalamount, 0)) Apr,
SUM(IF(transactionDate between '2013-05-01' and '2013-05-31', r.totalamount, 0)) May,
SUM(IF(transactionDate between '2013-06-01' and '2013-06-31', r.totalamount, 0)) Jun,
SUM(IF(transactionDate between '2013-07-01' and '2013-07-31', r.totalamount, 0)) Jul,
SUM(IF(transactionDate between '2013-08-01' and '2013-08-31', r.totalamount, 0)) Aug,
SUM(IF(transactionDate between '2013-09-01' and '2013-09-31', r.totalamount, 0)) Sept,
SUM(IF(transactionDate between '2013-10-01' and '2013-10-31', r.totalamount, 0)) Oct,
SUM(IF(transactionDate between '2013-11-01' and '2013-11-31', r.totalamount, 0)) Nov,
SUM(IF(transactionDate between '2013-12-01' and '2013-12-31', r.totalamount, 0)) Dece
from revenue as r
LEFT JOIN expenserecord er ON R.transactionID=ER.transactionID
GROUP BY
er.category
这篇关于根据类别的Mysql年度报告，列中包含月份名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
