• 主页
• 基础类
• 应用实例
• 新技术前沿

# 一道牛逼的数据库题目，NB的人请进

lg3605119 2007-12-12 06:06:06

.....
.....

....
....(所有员工信息)

...全文
154 点赞 收藏 6

6 条回复

lynxma 2007-12-24
--建立数据
create table T_money
(

)
insert T_money (姓名,金额,日期)
select '张三',100,'1998/4/8' union all
select '张三',10,'1998/7/4' union all
select '李四',20.5,'1999/8/9' union all
select '张三',10,'1999/8/7' union all
select '王五',30.5,'1998/1/1' union all
select '刘二',22.5,'2000/8/4' union all
select '李四',30,'2001/8/9' union all
select '赵一',2,'2002/8/4' union all
select '王五',14,'2000/2/21' union all
select '张三',52,'1999/12/9' union all
select '吴六',33,'2005/8/9' union all
select '刘二',20,'2007/8/9'

--建立查询
declare @str1 varchar(4000)
set @str1='select 姓名'
select @str1=@str1+',isnull(sum(case year(日期) when '+cast(年 as varchar(4))+' then 金额 end),0) ['
+cast(年 as varchar(4))+'年总金额]'
from (select distinct year(日期) as 年 from T_money) as a
select @str1=@str1+' from T_money group by 姓名'
exec(@str1)

--删除数据
drop table T_money

jxwangjm 2007-12-19

select t1998.[name],t1998.c1998,1999.c1999
from
(select [name],sum(金额) as c1998
from table1 where left(日期,4)=1998
group by [name]) t1998
full join
(select [name],sum(金额) as c1999
from table1 where left(日期,4)=1999
group by [name]) t1999 on t1998.[name]=t1999.[name]

yelloworange 2007-12-13
declare @y bigint
declare @yy varchar(4)
declare @s varchar(200)
declare @ss varchar(3000)
declare @sss varchar(8000)
select @y=min(datepart(yy,[date])) from tt
select @s=''
select @ss=''
select @sss=''
while(@y <=datepart(yy,getdate()))
begin
select @yy=cast(@y as char(4))
/*select @yy*/
select @s='sum(case when datepart(yy,[date])='+@yy+' then total else 0 end) as '''+@yy+ '总金额'',' from tt group by name,datepart(yy,[date])
select @y=@y+1
select @ss=@ss+@s
select @sss='select name,'+left(ltrim(@ss),len(ltrim(@ss))-1)+' from tt group by name'

end
print @sss

yelloworange 2007-12-13
declare @y bigint
declare @yy varchar(4)
declare @s varchar(200)
declare @ss varchar(8000)
select @y=min(datepart(yy,[date])) from tt
select @s=''
select @ss=''
while(@y <=datepart(yy,getdate()))
begin
select @yy=cast(@y as char(4))
/*select @yy*/
select @s='sum(case when datepart(yy,[date])='+@yy+' then total else 0 end) as '''+@yy+ '总金额'',' from tt group by name,datepart(yy,[date])
select @y=@y+1
select @ss=@ss+@s

end
@ss可得出动态的列名以及对应值

yowbell 2007-12-12

fcuandy 2007-12-12

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区