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

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)

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

标签：