简单描述
一张表:
ID Name Money Date
1 张三 1000 2001-4-1
1 张1 1000 2001-6-1
1 张2 1000 2001-8-1
1 张3 1000 2002-4-1
1 张4 1000 2003-4-1
1 张5 1000 2005-4-1
1 张5 1000 2001-4-1想要的结果是这样的:
输入时间:2001 到2004
结果是:
姓名 2001(Money) 2003(Money) 2004(Money)
张三 1000 0 0求sql语句
一张表:
ID Name Money Date
1 张三 1000 2001-4-1
1 张1 1000 2001-6-1
1 张2 1000 2001-8-1
1 张3 1000 2002-4-1
1 张4 1000 2003-4-1
1 张5 1000 2005-4-1
1 张5 1000 2001-4-1想要的结果是这样的:
输入时间:2001 到2004
结果是:
姓名 2001(Money) 2003(Money) 2004(Money)
张三 1000 0 0求sql语句
name,
max(case when year(date)=2001 then [money] else 0 end) as '2001',
max(case when year(date)=2002 then [money] else 0 end) as '2002',
max(case when year(date)=2003 then [money] else 0 end) as '2003',
max(case when year(date)=2004 then [money] else 0 end) as '2004'
from
tb
where
year(date) between 2001 and 2004
and
name='张三'
group by
name
declare @start varchar(10)
declare @end varchar(10)
declare @sql varchar(max)
set @start = '2001'
set @end = '2004'set @sql = 'select [name]'
select @sql = @sql + ',sum(case year(date) when ' + ltrim(date) + ' then money else 0 end)[' + ltrim(date) + '(money)]'
from (select distinct year(date) as date from tb)t
select @sql = @sql + ' from tb where year(date) between '+@start+' and '+@end+' group by [name]'
exec(@sql)
set @sql = 'select name '
select @sql = @sql + ' , max(case year(date) when ''' + date + ''' then [money] else 0 end) [' + year(date)+ ']'
from (select distinct date from tb) as a
set @sql = @sql + ' from tb where year between @begintime and @endtime and name=@name group by name'
exec(@sql)
create table #T(ID int, [Name] nvarchar(20), [Money] int, Date datetime)
insert #T select 1 ,'张三', 1000 ,'2001-4-1' union all
select 1 ,'张1', 1000 ,'2001-6-1' union all
select 1 ,'张2', 1000 ,'2001-8-1' union all
select 1 ,'张3', 1000 ,'2002-4-1' union all
select 1 ,'张4', 1000 ,'2003-4-1' union all
select 1 ,'张5', 1000 ,'2005-4-1' union all
select 1 ,'张5', 1000 ,'2001-4-1' declare @sql as nvarchar(4000)
declare @SYear as int
declare @EYear as int
declare @i as int
set @sql=''
set @SYear=2001 --改这两个变量可以按指定范围查
set @EYear=2004
set @i=@SYear
while @i<=@EYear
begin
set @sql=@sql+',sum(case convert(nvarchar(4),Date,120) when '''+cast(@i as nvarchar(4))+''' then [Money] else 0 end) as '''+cast(@i as nvarchar(4))+''''
set @i=@i+1
end
set @sql='select [Name] as ''姓名'''+@sql+' from #T group by [Name]'
exec(@sql)
select 1, '张三', 1000 ,'2001-4-1' union all
select 1 ,'张1', 1000 ,'2001-6-1' union all
select 1 ,'张2', 1000 ,'2001-8-1' union all
select 1 ,'张3', 1000 ,'2002-4-1' union all
select 1 ,'张4', 1000 ,'2003-4-1' union all
select 1 ,'张5', 1000 ,'2005-4-1' union all
select 1 ,'张5', 1000 ,'2001-4-1'
select name,
sum(case year(date) when '2001' then money end) as '2001',
sum(case year(date) when '2002' then money end) as '2002',
sum(case year(date) when '2003' then money end) as '2003',
sum(case year(date) when '2003' then money end) as '2004'
from tb10 group by name
结果是:
name 2001 2002 2003 2004 2005 。2009
输入:2001-2003
结果是:
name 2001 2002 2003
declare @start varchar(10)
declare @end varchar(10)
declare @sql varchar(max)
set @start = '2002'
set @end = '2006'
set @sql = 'select [name]'
select @sql = @sql + ',sum(case year(date) when ' + ltrim(date)
+ ' then money else 0 end)[' + ltrim(date) + '(money)]'
from (select distinct year(date) as date from tb
where year(date) between @start and @end
)t
select @sql = @sql + ' from tb where year(date) between '+@start+' and '+@end+' group by [name]'
exec(@sql)
(结合你的前台处理代码)