常用的數(shù)據(jù)統(tǒng)計(jì)Sql 總結(jié)
標(biāo)簽:
SQL Server
最近刚在搞一个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 MyPivo
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)
點(diǎn)擊查看更多內(nèi)容
8人點(diǎn)贊
評(píng)論
評(píng)論
共同學(xué)習(xí),寫下你的評(píng)論
評(píng)論加載中...
作者其他優(yōu)質(zhì)文章
正在加載中
感謝您的支持,我會(huì)繼續(xù)努力的~
掃碼打賞,你說多少就多少
贊賞金額會(huì)直接到老師賬戶
支付方式
打開微信掃一掃,即可進(jìn)行掃碼打賞哦