526互联

sql语句，实现总结每个部门的每个用途的金额总和

发布时间 2023-12-31 15:50:46作者: 不洗澡超酷
SELECT '总经理办公室' AS 部门, COALESCE(SUM(CASE WHEN yongtu = '办公用品' THEN price ELSE 0 END), 0) AS 办公用品, COALESCE(SUM(CASE WHEN yongtu = '差旅费' THEN price ELSE 0 END), 0) AS 差旅费, COALESCE(SUM(CASE WHEN yongtu = '业务招待' THEN price ELSE 0 END), 0) AS 业务招待, COALESCE(SUM(CASE WHEN yongtu = '科研经费' THEN price ELSE 0 END), 0) AS 科研经费, COALESCE(SUM(CASE WHEN yongtu = '油费' THEN price ELSE 0 END), 0) AS 油费, COALESCE(SUM(CASE WHEN yongtu = '其他' THEN price ELSE 0 END), 0) AS 其他 FROM otherbuy WHERE bumen = '总经理办公室' UNION ALL SELECT '业务部' AS 部门, COALESCE(SUM(CASE WHEN yongtu = '办公用品' THEN price ELSE 0 END), 0) AS 办公用品, COALESCE(SUM(CASE WHEN yongtu = '差旅费' THEN price ELSE 0 END), 0) AS 差旅费, COALESCE(SUM(CASE WHEN yongtu = '业务招待' THEN price ELSE 0 END), 0) AS 业务招待, COALESCE(SUM(CASE WHEN yongtu = '科研经费' THEN price ELSE 0 END), 0) AS 科研经费, COALESCE(SUM(CASE WHEN yongtu = '油费' THEN price ELSE 0 END), 0) AS 油费, COALESCE(SUM(CASE WHEN yongtu = '其他' THEN price ELSE 0 END), 0) AS 其他 FROM otherbuy WHERE bumen = '业务部' UNION ALL SELECT '技术部' AS 部门, COALESCE(SUM(CASE WHEN yongtu = '办公用品' THEN price ELSE 0 END), 0) AS 办公用品, COALESCE(SUM(CASE WHEN yongtu = '差旅费' THEN price ELSE 0 END), 0) AS 差旅费, COALESCE(SUM(CASE WHEN yongtu = '业务招待' THEN price ELSE 0 END), 0) AS 业务招待, COALESCE(SUM(CASE WHEN yongtu = '科研经费' THEN price ELSE 0 END), 0) AS 科研经费, COALESCE(SUM(CASE WHEN yongtu = '油费' THEN price ELSE 0 END), 0) AS 油费, COALESCE(SUM(CASE WHEN yongtu = '其他' THEN price ELSE 0 END), 0) AS 其他 FROM otherbuy WHERE bumen = '技术部' UNION ALL SELECT '人事部' AS 部门, COALESCE(SUM(CASE WHEN yongtu = '办公用品' THEN price ELSE 0 END), 0) AS 办公用品, COALESCE(SUM(CASE WHEN yongtu = '差旅费' THEN price ELSE 0 END), 0) AS 差旅费, COALESCE(SUM(CASE WHEN yongtu = '业务招待' THEN price ELSE 0 END), 0) AS 业务招待, COALESCE(SUM(CASE WHEN yongtu = '科研经费' THEN price ELSE 0 END), 0) AS 科研经费, COALESCE(SUM(CASE WHEN yongtu = '油费' THEN price ELSE 0 END), 0) AS 油费, COALESCE(SUM(CASE WHEN yongtu = '其他' THEN price ELSE 0 END), 0) AS 其他 FROM otherbuy WHERE bumen = '人事部' UNION ALL SELECT '财务部' AS 部门, COALESCE(SUM(CASE WHEN yongtu = '办公用品' THEN price ELSE 0 END), 0) AS 办公用品, COALESCE(SUM(CASE WHEN yongtu = '差旅费' THEN price ELSE 0 END), 0) AS 差旅费, COALESCE(SUM(CASE WHEN yongtu = '业务招待' THEN price ELSE 0 END), 0) AS 业务招待, COALESCE(SUM(CASE WHEN yongtu = '科研经费' THEN price ELSE 0 END), 0) AS 科研经费, COALESCE(SUM(CASE WHEN yongtu = '油费' THEN price ELSE 0 END), 0) AS 油费, COALESCE(SUM(CASE WHEN yongtu = '其他' THEN price ELSE 0 END), 0) AS 其他 FROM otherbuy WHERE bumen = '财务部' UNION ALL SELECT '市场部' AS 部门, COALESCE(SUM(CASE WHEN yongtu = '办公用品' THEN price ELSE 0 END), 0) AS 办公用品, COALESCE(SUM(CASE WHEN yongtu = '差旅费' THEN price ELSE 0 END), 0) AS 差旅费, COALESCE(SUM(CASE WHEN yongtu = '业务招待' THEN price ELSE 0 END), 0) AS 业务招待, COALESCE(SUM(CASE WHEN yongtu = '科研经费' THEN price ELSE 0 END), 0) AS 科研经费, COALESCE(SUM(CASE WHEN yongtu = '油费' THEN price ELSE 0 END), 0) AS 油费, COALESCE(SUM(CASE WHEN yongtu = '其他' THEN price ELSE 0 END), 0) AS 其他 FROM otherbuy WHERE bumen = '市场部' UNION ALL SELECT '销售部' AS 部门, COALESCE(SUM(CASE WHEN yongtu = '办公用品' THEN price ELSE 0 END), 0) AS 办公用品, COALESCE(SUM(CASE WHEN yongtu = '差旅费' THEN price ELSE 0 END), 0) AS 差旅费, COALESCE(SUM(CASE WHEN yongtu = '业务招待' THEN price ELSE 0 END), 0) AS 业务招待, COALESCE(SUM(CASE WHEN yongtu = '科研经费' THEN price ELSE 0 END), 0) AS 科研经费, COALESCE(SUM(CASE WHEN yongtu = '油费' THEN price ELSE 0 END), 0) AS 油费, COALESCE(SUM(CASE WHEN yongtu = '其他' THEN price ELSE 0 END), 0) AS 其他 FROM otherbuy WHERE bumen = '销售部';