Mysql

最新推荐文章于 2024-09-15 08:03:51 发布

拐只小猪

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量81

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_43837803/article/details/120722661

版权

本文分享了一位工程师在处理上月项目工时统计时遇到的问题及解决方案。通过创建一个中间结果集避免了数据重复计算，显著提高了查询效率，从原本的复杂查询优化到仅用0.3秒。作者总结了SQL查询经验的重要性，提醒读者避免钻牛角尖。

摘要由CSDN通过智能技术生成

业务：按照工时表求出上个月统计需求如下

最终sql如下

SELECT
   t.project_name AS 项目名称,
   t.project_code AS 项目编号,
   sum(hours) AS 工时,
   count( user_count ) AS 总人数,
   SUM(CASE WHEN t4.sex = '男' then 1 else 0 end) AS 男,
   SUM(CASE WHEN t4.sex = '女' then 1 else 0 end) AS 女,
   SUM(CASE WHEN t4.technical_title = 441 then 1 else 0 end) AS 高级,
   SUM(CASE WHEN t4.technical_title = 440 then 1 else 0 end) AS 中级,
   SUM(CASE WHEN t4.technical_title = 439 then 1 else 0 end) AS 初级,
   SUM(CASE WHEN t4.technical_title = 442 then 1 else 0 end) AS 其他,
   SUM(CASE WHEN t4.degree = 433 then 1 else 0 end) AS 博士,
   SUM(CASE WHEN t4.degree = 432 then 1 else 0 end) AS 硕士,
   SUM(CASE WHEN t4.degree = 431 then 1 else 0 end) AS 学士
FROM
   (
   SELECT
       t2.project_name,
       t2.project_code,
       SUM( working_hours ) AS hours,
       count( DISTINCT ( t1.user_oa_id ) ) AS user_count,
       t1.user_oa_id
   FROM
       dw_cockpit_worktime t1
       LEFT JOIN project_info t2 ON t1.project_code = t2.project_code
   WHERE
       t1.record_date >= '2021-09-01'
       AND t1.record_date <= '2021-09-30'
   GROUP BY
       t1.project_code,
       t1.user_oa_id
   ) t
   LEFT JOIN project_info t2 ON t.project_code = t2.project_code
   LEFT JOIN sys_user t3 ON t.user_oa_id = t3.user_id
   LEFT JOIN project_team_members t4 ON t2.uuid = t4.project_info_uuid
   AND t3.id = t4.user_id
   AND t4.del = 0
GROUP BY
   t.project_code
ORDER BY
   t.project_code