常用的数据统计Sql 总结(转)
转:http://www.cnblogs.com/zhangweizhong/p/5577842.html
最近刚在搞一个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 GiftCard WHERE 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 GiftCard group by year(AddTime)
如何联系我:【我的公司】www.xinzhenkj.com(信真科技)【技术咨询】www.laohuzx.com 【QQ】3396726884
图书购买京东链接***微信小程序商城开发实战*** | ***新媒体营销精华:精准定位+爆款打造+匠心运营+内容变现***