select '1' AAA,
'本年' BBB,
sum(case when year(BBB)=2008 then AAA else 0 end) CCC
from tb
union all
select '2' AAA,
'本月' BBB
...
from tb
...
...
'本年' BBB,
sum(case when year(BBB)=2008 then AAA else 0 end) CCC
from tb
union all
select '2' AAA,
'本月' BBB
...
from tb
...
...
select '1' AAA,
'本年' BBB,
sum(case when year(BBB)=2008 then AAA else 0 end) CCC
from tb
union all
select '2' AAA,
'本月' BBB ,
sum(case when month(BBB)=month(getdate()) then AAA else 0 end) CCC
from tb
union all
select '3' AAA,
'本日' BBB ,
aaa
from tb where datediff(d,BBB,getdate()) = 0
'本年' BBB,
sum(case when year(BBB)=2008 then AAA else 0 end) CCC
from tb
union all
select '2' AAA,
'本月' BBB ,
sum(case when month(BBB)=month(getdate()) then AAA else 0 end) CCC
from tb
union all
select '3' AAA,
'本日' BBB ,
aaa
from tb where datediff(d,BBB,getdate()) = 0这是错的
month(BBB)=month(getdate())是不能表示本月的要year(bbb)=year(getdate()) and month(BBB)=month(getdate())
SELECT '本年' AS SIGN,SUM(AAA)AS YEAR_SUM FROM # WHERE DATEDIFF(year,BBB,GETDATE())=0
UNION ALL
SELECT '本月',SUM(AAA)AS MONTH_SUM FROM # WHERE DATEDIFF(month,BBB,GETDATE())=0
UNION ALL
SELECT '本日',SUM(AAA)AS DAY_SUM FROM # WHERE DATEDIFF(day,BBB,GETDATE())=0
declare @Table1 table([AAA] int,[BBB] Datetime)
Insert @Table1
select 1000,'2008-2-8' union all
select 2000,'2008-8-8' union all
select 3000,'2008-8-20'
select
row_number()over(order by [BBB] desc)ID,[BBB],[CCC]
from
(Select sum(case when year([BBB])=year(getdate()) then [AAA] else 0 end) 本年, sum(case when [BBB]>=convert(varchar(8),getdate(),120)+'01' then [AAA] else 0 end)本月,
sum(case when datediff(d,[BBB],getdate())=0 then [AAA] else 0 end)本日 from @Table1)T
unpivot
([CCC] for [BBB] in(本年,本月,本日))t2
(3 個資料列受到影響)
ID BBB CCC
-------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------
1 本年 6000
2 本月 5000
3 本日 3000(3 個資料列受到影響)