sql查询部门及客户收支汇总

Question

刚刚学习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

Answer 1

如果有个日历表，查询可以简单些。没有日历表，就得这么写：

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;