关于一道数据库面试题
year que amount
2000 1 1.2
2000 2 1.1
2000 3 1.3
2001 1 1.2
2001 2 2.1
2001 3 3.1
用sql语句实现为：
year que1 que2 que3 amount
2000 1 2 3 3.6
2001 1 2 3 6.4
Create table test (name char(10),km char(10),cj int)
go
insert test values( ‘2005 ‘, ‘1 ‘,8)
insert test values( ‘2006 ‘, ‘2 ‘,8)
insert test values( ‘2005 ‘, ‘2 ‘,5)
insert test values( ‘2006 ‘, ‘1 ‘,7)
insert test values( ‘2005 ‘, ‘3 ‘,5)
insert test values( ‘2006 ‘, ‘3 ‘,8)
declare @sql varchar(8000)
set @sql = ‘select name ‘
select @sql = @sql + ‘,sum(case km when ‘ ‘ ‘+km+ ‘ ‘ ‘ then cj end) [ ‘+km+ ‘] ‘
from (select distinct km from test) as a
select @sql = @sql+ ‘ from test group by name ‘
exec(@sql)
drop table test
