常用的数据统计Sql 总结（转）-阿里云开发者社区

常用的数据统计Sql 总结（转）

2016-06-14 856

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 转：http://www.cnblogs.com/zhangweizhong/p/5577842.html 最近刚在搞一个BI的项目，里面需要大量的sql 数据统计相关运用，加深了我又对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.bravetiger.cn 【QQ】3396726884 （咨询问题100元起，帮助解决问题500元起）【博客】http://www.cnblogs.com/kenshinobiy/