每个月怎么写最好

Question

费用表存储员工上报给会计部门报销的日常费用。请列出指定月份的每月总费用大于该部门当月平均费用的所有员工id。下面是创建表和插入示例行的脚本，只是为了让您对数据库中的数据有一个概念。

CREATE TABLE Expense (Department_Id varchar(20), Employee_Id varchar(20), Expense_Date date, Expense_Amt decimal(18,0))INSERT INTO Expense VALUES ('D1', 'E1', '2019-08-02',100), ('D1', 'E1', '2019-08-05',150), ('D1', 'E1', '2019-08-02',200), ('D1', 'E2', '2019-08-12',900), ('D1', 'E2', '2019-08-15',1000), ('D2', 'E3', '2019-08-15',500), ('D2', 'E4', '2019-08-20',550), ('D2', 'E5', '2019-08-10',400), ('D2', 'E6', '2019-09-10',600), ('D3', 'E7', '2019-08-14',700)

Answer 1

你好，你可以通过下面的SQL-

select Employee_Id,SUM(Expense_Amt) as Total_expense,AVG(temp.d_ex) department_avg_exp from expense 
left outer join (
select AVG(Expense_Amt) d_ex,MONTH(Expense_Date)month_id ,Department_Id from Expense group by Department_Id, MONTH(Expense_Date)
) temp ON temp.Department_Id = Expense.Department_Id
GROUP BY Employee_Id,MONTH(Expense_Date)
HAVING AVG(temp.d_ex) <SUM(Expense_Amt)