刚刚学习sql，有个练习题不太会，特此请教，需求是查询部门客户收支月报
涉及到的有四个表,表结构只表述了主要字段
BM table --部门表
id,code,name
KH table --客户表
id,code,name
SK table --收款明细表表
id,code,datetime,bmid,khid,amount
FY table --费用支出表
id,code,datetime,bmid,khid,amount
如果有个日历表，查询可以简单些。没有日历表，就得这么写：
SELECT IncomeExpense.year AS 年,
IncomeExpense.month AS 月,
BM.name AS 部门,
KH.name AS 客户,
IncomeExpense.IncomeAmount AS 收入,
IncomeExpense.ExpenseAmount AS 支出
FROM BM CROSS JOIN KH
INNER JOIN (
SELECT COALESCE(Income.bmid, Expense.bmid) AS bmid,
COALESCE(Income.khid, Expense.khid) AS khid,
COALESCE(Income.year, Expense.year) AS year,
COALESCE(Income.month, Expense.month) AS month,
Income.amount AS IncomeAmount,
Expense.amount AS ExpenseAmount
FROM (
SELECT bmid,
khid,
YEAR(datetime) AS year,
MONTH(datetime) AS month,
SUM(amount) AS amount
FROM SK
GROUP BY bmid, khid, YEAR(datetime), MONTH(datetime)
) AS Income
ON BM.id = Income.bmid AND KH.id = Income.khid
FULL OUTER JOIN (
SELECT bmid,
khid,
YEAR(datetime) AS year,
MONTH(datetime) AS month,
SUM(amount) AS amount
FROM FY
GROUP BY bmid, khid, YEAR(datetime), MONTH(datetime)
) AS Expense
ON Income.bmid = Expense.bmid
AND Income.khid = Expense.khid
AND Income.year = Expense.year
AND Income.month = Expense.month
) AS IncomeExpense
ON BM.id = IncomeExpense.bmid
AND KH.id = IncomeExpense.khid
ORDER BY BM.name, KH.name;
-- 部门收入月报
SELECT BM.id,
BM.code,
BM.name,
YEAR(SK.datetime) AS Year,
MONTH(SK.datetime) AS Month,
SUM(SK.amount) AS MonthlyIncome
FROM BM INNER JOIN SK ON BM.id = SK.bmid
GROUP BY BM.id, BM.code, BM.name, YEAR(SK.datetime), MONTH(SK.datetime)
ORDER BY BM.name, Year, Month;
-- 部门支出月报
SELECT BM.id,
BM.code,
BM.name,
YEAR(SK.datetime) AS Year,
MONTH(SK.datetime) AS Month,
SUM(SK.amount) AS MonthlyIncome
FROM BM INNER JOIN FY ON BM.id = FY.bmid
GROUP BY BM.id, BM.code, BM.name, YEAR(FY.datetime), MONTH(FY.datetime)
ORDER BY BM.name, Year, Month;
我写了部门收入月报 和 部门支出月报查询。需要查询客户收入和客户支出吗？感觉怪怪的。另外收入和费用表有字段code，是科目吗？如果需要汇总里有科目，也可以加上。