常用的数据统计Sql总结
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
常⽤的数据统计Sql总结
最近刚在搞⼀个BI的项⽬，⾥⾯需要⼤量的sql 数据统计相关运⽤，加深了我⼜对SQL的理解与使⽤。
所以，分享⼏个数据统计时常⽤的sql 语句总结：
1. 统计各个条件下的数据
select
BatchId,sum(CardSum) 总⾦额,
sum(case when Status=1 then CardSum else0 end) as已使⽤,
sum(case when Status=2 then CardSum else0 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(select1from GiftCard where BatchName=a.BatchName and ID<a.ID)
7. 按年统计1⽉到12个⽉的销量
select year(AddTime) as'年',
SUM(case when MONTH(AddTime)=1 then CardSum else0 end ) as'⼀⽉',
SUM(case when MONTH(AddTime)=2 then CardSum else0 end ) as'⼆⽉',
SUM(case when MONTH(AddTime)=3 then CardSum else0 end ) as'三⽉',
SUM(case when MONTH(AddTime)=4 then CardSum else0 end ) as'四⽉',
SUM(case when MONTH(AddTime)=5 then CardSum else0 end ) as'五⽉', SUM(case when MONTH(AddTime)=6 then CardSum else0 end ) as'六⽉', SUM(case when MONTH(AddTime)=7 then CardSum else0 end ) as'七⽉', SUM(case when MONTH(AddTime)=8 then CardSum else0 end ) as'⼋⽉', SUM(case when MONTH(AddTime)=9 then CardSum else0 end ) as'九⽉', SUM(case when MONTH(AddTime)=10 then CardSum else0 end ) as'⼗⽉', SUM(case when MONTH(AddTime)=11 then CardSum else0 end ) as'⼗⼀⽉', SUM(case when MONTH(AddTime)=12 then CardSum else0 end ) as'⼗⼆⽉' from GiftCard
group by year(AddTime)。