常用的数据统计Sql 总结

本文分享了在BI项目中常用的SQL数据统计技巧,包括按不同条件统计数据、按日期统计、去重统计等,并提供了实际应用的SQL语句示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

最近刚在搞一个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)

[size=xx-small]作者:章为忠
出处:http://www.cnblogs.com/zhangweizhong/ [/size]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值