sql server数据分组统计

最新推荐文章于 2024-01-09 16:46:16 发布

原创最新推荐文章于 2024-01-09 16:46:16 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #sql #c

MSSQL 分类专栏收录该内容

11 篇文章

订阅专栏

本文介绍了如何在SQL Server中进行数据分组统计，包括按条件分组统计和按日期时间分组统计。示例展示了对tbl_orderlist表中不同金额范围的订单数量和总额的统计，以及按月份统计订单数量和总额。高效的SQL查询在提升系统响应速度和用户体验方面起着关键作用。

1使用查询所得的结果集进行再分组统计示例格式.

select *
from
(select count(*) as QuantityA, sum(ol_sum) as Amount_A from tbl_orderlist
    where year(OL_RegTime)=year(getdate()) and month(OL_RegTime)=month(getdate()) ) A,
(select count(*) as QuantityB, sum(ol_sum) as Amount_B from tbl_orderlist
    where ol_sum>30 and ol_sum<=50) B,
(select count(*) as QuantityC, sum(ol_sum) as Amount_C from tbl_orderlist
    where ol_sum>50 and ol_sum<=80) C,
(select count(*) as QuantityD, sum(ol_sum) as Amount_D from tbl_orderlist
    where ol_sum>80 and ol_sum<=100) D,
(select count(*) as QuantityE, sum(ol_sum) as Amount_E from tbl_orderlist
    where ol_sum>100) E

2,对于按日期及时间的分组统计

select month(A.OL_RegTime) as StrMonth,count(*) as QuantityA, sum(ol_sum) as Amount_A
from tbl_orderlist A
where year(A.OL_RegTime)=year(getdate())
group by month(A.OL_RegTime) order by strmonth

在许多项目中开发都涉及到数据统计的问题,应用高效、快速的sql查询可以加快系统的反应，提高用户体验的使用满意度。根据以往经验得出，系统设计应基于这种思路。以上只是一些启发性的应用，在实际的开发进程中融入这种设计思想将会做出较高质量系统。设计追求精益求精.