最近剛在搞一個(gè)BI的項(xiàng)目，里面需要大量的sql 數(shù)據(jù)統(tǒng)計(jì)相關(guān)運(yùn)用，加深了我又對(duì)SQL的理解與使用。

?

所以，分享幾個(gè)數(shù)據(jù)統(tǒng)計(jì)時(shí)常用的sql 語(yǔ)句總結(jié)：?

?

1. 統(tǒng)計(jì)各個(gè)條件下的數(shù)據(jù)

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 已凍結(jié) from GiftCard group by BatchId

?

2. 統(tǒng)計(jì)每日,每月,每年的數(shù)據(jù)

select year(AddTime) 年,month(AddTime) 月,day(AddTime) 日,COUNT(1) 數(shù)量,sum(CardSum) 銷售合計(jì) from GiftCard group by year(AddTime),month(AddTime),day(AddTime)

?

3. 某列去重統(tǒng)計(jì)

select COUNT(BatchId),COUNT(distinct BatchId),COUNT(distinct BatchName) from GiftCard

?

4. 行轉(zhuǎn)列

SELECT * FROM (SELECT BatchName, CardSum as TotAmount FROM GiftCard) as s PIVOT ( SUM(TotAmount)FOR BatchName IN (zx測(cè)試商品, test新人優(yōu)惠券,測(cè)試高考大放送) )AS MyPivot

?

5. 得到表中最小的未使用的ID號(hào)

SELECT (CASE WHEN EXISTS(SELECT * FROM GiftCard b WHERE b.Id = 1) THEN MIN(Id) + 1 ELSE 1 END) as Id FROM GiftCardWHERE NOT Id IN (SELECT a.Id - 1 FROM GiftCard a)

?

6. 查詢某一列數(shù)據(jù)不重復(fù)的數(shù)量

select *
from GiftCard a
where not exists(select 1 from GiftCard where BatchName=a.BatchName and ID<a.ID)

?

7.?按年統(tǒng)計(jì)1月到12個(gè)月的銷量

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 GiftCardgroup by year(AddTime)

?

作者：章為忠?
出處：http://www.fpeach.com/?
本文版權(quán)歸作者所有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁(yè)面明顯位置給出原文連接。如有問(wèn)題，可以微信：18618243664?聯(lián)系我，非常感謝。?

掃下面的二維碼關(guān)注我的微信公眾號(hào)。?

總結(jié)

以上是生活随笔為你收集整理的常用的数据统计Sql 总结的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。