MySql数量统计sql

最新推荐文章于 2025-03-28 09:29:24 发布

原创最新推荐文章于 2025-03-28 09:29:24 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

MySQL 专栏收录该内容

1 篇文章

订阅专栏

本文汇总了每月创建、完成和关闭工单的数量，按周划分，并深入分析了用户完成任务的情况，揭示了关键指标。关键词包括工单处理、用户行为、完成率和时间周期。



-- 统计创建工单数量，

SELECT
date_add(DATE_ADD(curdate(), INTERVAL - DAY(curdate()) + 2 DAY), INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY ) date ,
(SELECT count(temp_id) from ows_order oo where left(oo.create_time, 10) = date) total_count
from mysql.help_topic
where help_topic_id  < day(curdate())
order by total_count desc;



-- 完成工单数量（按时、超时都算完成）
SELECT
date_add(DATE_ADD(curdate(), INTERVAL - DAY(curdate()) + 2 DAY), INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY ) date ,
(SELECT count(temp_id) from ows_order oo where left(oo.create_time, 10) = date and oo.completed_type in (1,2)) total_count
from mysql.help_topic
where help_topic_id  < day(curdate())
order by total_count desc;


-- ，关闭工单数量，

SELECT
date_add(DATE_ADD(curdate(), INTERVAL - DAY(curdate()) + 2 DAY), INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY ) date ,
(SELECT count(temp_id) from ows_order oo where left(oo.create_time, 10) = date and oo.closed_time is not null) total_count
from mysql.help_topic
where help_topic_id  < day(curdate())
order by total_count desc
;






-- 当月  创建，完成，关闭
(select count(temp_id) q from ows_order where left(create_time, 7) =  DATE_FORMAT(NOW(),'%Y-%m') )
UNION all
(
select count(temp_id) q from ows_order where left(create_time, 7) =  DATE_FORMAT(NOW(),'%Y-%m') and completed_type in (1,2)
)
UNION all
(
select count(temp_id) q from ows_order where left(create_time, 7) =  DATE_FORMAT(NOW(),'%Y-%m') and closed_time is not null
)



-- 选择日期
(select count(temp_id) q from ows_order where left(create_time, 7) =  '2022-03' )
UNION all
(
select count(temp_id) q from ows_order where left(create_time, 7) = '2022-03' and completed_type in (1,2)
)
UNION all
(
select count(temp_id) q from ows_order where left(create_time, 7) =  '2022-03' and closed_time is not null
)


-- 传参循环 周
(select count(temp_id) q from ows_order where left(create_time, 10) >=  '2022-04-01' and left(create_time, 10) <=  '2022-04-07'  )
UNION all
(
select count(temp_id) q from ows_order where left(create_time, 10) >=  '2022-04-01' and left(create_time, 10) <=  '2022-04-07' and completed_type in (1,2)
)
UNION all
(
select count(temp_id) q from ows_order where left(create_time, 10) >=  '2022-04-01' and left(create_time, 10) <=  '2022-04-07' and closed_time is not null
);


(select count(temp_id) q from ows_order where left(create_time, 10) >=  '2022-04-07' and left(create_time, 10) <=  '2022-04-14'  )
UNION all
(
select count(temp_id) q from ows_order where left(create_time, 10) >=  '2022-04-07' and left(create_time, 10) <=  '2022-04-14' and completed_type in (1,2)
)
UNION all
(
select count(temp_id) q from ows_order where left(create_time, 10) >=  '2022-04-07' and left(create_time, 10) <=  '2022-04-14' and closed_time is not null
)






-- 任务二：
SELECT user_id, count(temp_id) q from ows_order where left(create_time, 7) = DATE_FORMAT(NOW(),'%Y-%m') and completed_type in (1,2)  GROUP BY user_id ORDER BY q desc limit 15