常用的数据统计Sql 总结

最近刚在搞一个BI的项目，里面需要大量的sql 数据统计相关运用，加深了我又对SQL的理解与使用。

 

所以，分享几个数据统计时常用的sql 语句总结： 

 

1. 统计各个条件下的数据

select BatchId,sum(CardSum) 总金额, sum(case when Status=1 then CardSum else 0 end) as 已使用, sum(case when Status=2 then CardSum else 0 end) as 已冻结 from GiftCard group by BatchId

 

2. 统计每日,每月,每年的数据

select year(AddTime) 年,month(AddTime) 月,day(AddTime) 日,COUNT(1) 数量,sum(CardSum) 销售合计 from GiftCard group by year(AddTime),month(AddTime),day(AddTime)

 

3. 某列去重统计

select COUNT(BatchId),COUNT(distinct BatchId),COUNT(distinct BatchName) from GiftCard

 

4. 行转列

SELECT * FROM (SELECT BatchName, CardSum as TotAmount FROM GiftCard) as s PIVOT ( SUM(TotAmount)FOR BatchName IN (zx测试商品, test新人优惠券,测试高考大放送) )AS MyPivot

 

5. 得到表中最小的未使用的ID号

SELECT (CASE WHEN EXISTS(SELECT * FROM GiftCard b WHERE b.Id = 1) THEN MIN(Id) + 1 ELSE 1 END) as Id FROM GiftCardWHERE NOT Id IN (SELECT a.Id - 1 FROM GiftCard a)

 

6. 查询某一列数据不重复的数量

select *
from GiftCard a
where not exists(select 1 from GiftCard where BatchName=a.BatchName and ID<a.ID)

 

7. 按年统计1月到12个月的销量

select year(AddTime) as '年', SUM(case when MONTH(AddTime)=1 then CardSum else 0 end ) as '一月', SUM(case when MONTH(AddTime)=2 then CardSum else 0 end ) as '二月', SUM(case when MONTH(AddTime)=3 then CardSum else 0 end ) as '三月', SUM(case when MONTH(AddTime)=4 then CardSum else 0 end ) as '四月', SUM(case when MONTH(AddTime)=5 then CardSum else 0 end ) as '五月', SUM(case when MONTH(AddTime)=6 then CardSum else 0 end ) as '六月', SUM(case when MONTH(AddTime)=7 then CardSum else 0 end ) as '七月', SUM(case when MONTH(AddTime)=8 then CardSum else 0 end ) as '八月', SUM(case when MONTH(AddTime)=9 then CardSum else 0 end ) as '九月', SUM(case when MONTH(AddTime)=10 then CardSum else 0 end ) as '十月', SUM(case when MONTH(AddTime)=11 then CardSum else 0 end ) as '十一月', SUM(case when MONTH(AddTime)=12 then CardSum else 0 end ) as '十二月'from GiftCardgroup by year(AddTime)

 

作者：章为忠 
出处：http://www.fpeach.com/ 
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接。如有问题，可以微信：18618243664 联系我，非常感谢。 

扫下面的二维码关注我的微信公众号。 

总结

以上是生活随笔为你收集整理的常用的数据统计Sql 总结的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。