求SQL语句

[no_com]

求SQL语句
________________________________________________________________________
表：Tbl_1
date col_int col_name
2002/06/01 80 AAA
2002/07/01 40 AAA
2002/07/02 20 AAA
2002/07/02 10 AAA
+++++++++++++++++++++++
问：如何通过查询生成下表（就是把AAA按照时间[2002/07/02]汇总）？
Col_name col_DD(日) col_MM（当月） col_YY（当年）
AAA 30 70 150
________________________________________________________________________

[zosky]

select col_name,sum(case when day([date])='02' then col_int end) as col_DD,
sum(case when month([date])='07' then col_int end) as col_mm,
sum(case when year([date])='2003' then col_int end) as col_yy
from tbl_1
group by col_name

[pengdali]

select [col_name],
sum(case when datediff(day,[date],'2002/07/02')=0 then col_int end) [col_DD(日)],
sum(case when datediff(month,[date],'2002/07/02')=0 then col_int end) [col_MM（当月）],
sum(case when datediff(year,[date],'2002/07/02')=0 then col_int end) [col_YY（当年）]
from Tbl_1 group by [col_name]

[yehuazi]

select Col_name,
count('col_MM（当月）') as 'col_DD(日)',
count('col_YY（当年）') as 'col_MM（当月）',
count(*) as 'col_YY（当年）'
from 表
where [date]='2002/07/02'
group by Col_name,datepart(yy,[date])

[CrazyFor]

select [col_name],
sum(case when datediff(day,[date],'2002/07/02')=0 then col_int else 0 end) [col_DD(日)],
sum(case when datediff(month,[date],'2002/07/02')=0 then col_int else 0 end) [col_MM（当月）],
sum(case when datediff(year,[date],'2002/07/02')=0 then col_int else 0 end) [col_YY（当年）]
from Tbl_1 group by [col_name]

[weixiao51]

select col_name,
(select sum(col_int) from Tbl_1 where datediff(day,date,getdate()) = 0) AS col_dd,
(select sum(col_int) from Tbl_1 where datediff(day,date,getdate()) = 0) AS col_mm,
(select sum(col_int) from Tbl_1 where datediff(day,date,getdate()) = 0) AS col_yy
from Tbl_1 group by col_name

[friendliu]

大力的对。

[愉快的登山者]

declare @edate datetime
set @edate = '2002-7-2'
select col_name, sum(col_int) [col_DD(日)],
(select sum(col_int) from tbl_1 where col_name = A.col_name and datediff(month,@edate, [date]) = 0) [col_MM(当月)],
(select sum(col_int) from tbl_1 where col_name = A.col_name and datediff(year,@edate, [date]) = 0) [col_YY(当年)]
from from tbl_1 A
where [date] = @edate
group by col_name

[friendliu]

select col_name,
sum(case when date='2002/07/02' then col_int end) as col_DD(日),
sum(case when datediff(month,[date],'2002/07/02')='0' then col_int end) as col_MM（当月）,
sum(case when datediff(year,[date],'2002/07/02')='0' then col_int end) as col_YY（当年）
from Tbl_1 group by col_name

[dcshi2001]

Select Col_name,Col_DD=Select Sum(Col_Int) from tb1_1 where Substring(Date,9,2)='23',Col_mm=Select Sum(Col_Int) from tb1_1 where Substring(Date,6,2)='07',
Col_DD=Select Sum(Col_Int) from tb1_1 where Substring(Date,1,4)='2003' from Tb1_1
order by col_name

[weixiao51]

select col_name,
(select sum(col_int) from Tbl_1 where datediff(day,date,getdate()) = 0) AS col_dd,
(select sum(col_int) from Tbl_1 where datediff(month,date,getdate()) = 0) AS col_mm,
(select sum(col_int) from Tbl_1 where datediff(year,date,getdate()) = 0) AS col_yy
from Tbl_1 group by col_name

[nboys]

select col_name,sum(col_int) as col_DD,
(select sum(col_int) from Tb1_1 a where datepart(mm,a.date)=datepart(mm,b.date) and a.col_name=b.col_name) as col_MM,
(select sum(col_int) from Tb1_1 c where datepart(yy,c.date)=datepart(yy,b.date) and c.col_name=b.col_name) as col_YY
from Tb1_1 b group by col_name,date having date='2002/07/02'

[nboys]

select col_name,sum(col_int) as [col_DD(日)],
(select sum(col_int) from Tb1_1 a where datepart(mm,a.date)=datepart(mm,b.date) and a.col_name=b.col_name) as [col_MM(当月)],
(select sum(col_int) from Tb1_1 c where datepart(yy,c.date)=datepart(yy,b.date) and c.col_name=b.col_name) as [col_YY(当年)]
from Tb1_1 b group by col_name,date having date='2002/07/02'

[txlicenhe]

select [col_name],
sum(case when datediff(day,[date],'2002/07/02')=0 then col_int end) [col_DD(日)],
sum(case when datediff(month,[date],'2002/07/02')=0 then col_int end) [col_MM（当月）],
sum(case when datediff(year,[date],'2002/07/02')=0 then col_int end) [col_YY（当年）]
from Tbl_1 group by [col_name]