请问,我有一个如下所示的数据表:
NAME Year Class VALUE
sh 2006 POP 89
sh 2007 POP 90
sh 2008 POP 98
sh 2006 First 0.5
sh 2007 First 0.46
sh 2008 First 0.44
sh 2006 Second 0.3
sh 2007 Second 0.31
sh 2008 Second 0.32
nj 2006 POP 40
nj 2007 POP 38
nj 2008 POP 42
nj 2006 First 0.1
nj 2007 First 0.3
nj 2008 First 0.2
nj 2006 Second 0.5
nj 2007 Second 0.456
nj 2008 Second 0.6
*********************************************
我想得到如下的查询结果,输入参数为(Year=2007,Class=POP) &&(Year=2008,Class=First)
结果如下:
NAME 2007POP 2008First
sh 90 0.44
nj 38 0.2
*********************************************
这样的SQL如何写?紧急求助:)
NAME Year Class VALUE
sh 2006 POP 89
sh 2007 POP 90
sh 2008 POP 98
sh 2006 First 0.5
sh 2007 First 0.46
sh 2008 First 0.44
sh 2006 Second 0.3
sh 2007 Second 0.31
sh 2008 Second 0.32
nj 2006 POP 40
nj 2007 POP 38
nj 2008 POP 42
nj 2006 First 0.1
nj 2007 First 0.3
nj 2008 First 0.2
nj 2006 Second 0.5
nj 2007 Second 0.456
nj 2008 Second 0.6
*********************************************
我想得到如下的查询结果,输入参数为(Year=2007,Class=POP) &&(Year=2008,Class=First)
结果如下:
NAME 2007POP 2008First
sh 90 0.44
nj 38 0.2
*********************************************
这样的SQL如何写?紧急求助:)
from tb m , tb n
where m.Year=2007 and m.Class='POP' and n.Year=2008 and n.Class='First' and m.name = n.name
name,
sum(case when Year=2007 and Class='POP' then VALUE end),
sum(case when Year=2008 and Class='First' then VALUE end)
from
tname
group by
name
insert into tb values('sh', 2007 ,'POP' ,90)
insert into tb values('sh', 2008 ,'First' ,0.44)
insert into tb values('nj', 2007 ,'POP' ,38)
insert into tb values('nj', 2008 ,'First' ,0.2)select m.NAME , m.VALUE [2007POP] , n.VALUE [2008First]
from tb m , tb n
where m.Year=2007 and m.Class='POP' and n.Year=2008 and n.Class='First' and m.name = n.namedrop table tb/*
NAME 2007POP 2008First
---------- -------------------- --------------------
sh 90.00 .44
nj 38.00 .20(所影响的行数为 2 行)
*/