求SQL语句

Question

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

Answer 1

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]

Answer 2

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])

Answer 3

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]

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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'

Answer 10

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'

Answer 11

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]