今天去一家公司面试了,由于很紧张当时竟然没做出来,懊悔不已,后来后痛定思痛,要把它做出来,只为学习用:
题目:根据所给的两个表,求出所示效果,附图:
表一:flights
flightdate scn fmoney
2009-01-09 01 523.0
2009-08-15 01 708.0
2010-08-02 02 199.0
表二:signment
scn sname
01 英国
02 美国
结果:
flightdate sname fmoney
2010 美国 199.0
2009 英国 1231.0
create table flights(flightdate date,scn varchar(10),fmoney decimal(10,1))
insert into flights values('2009-01-09','01' , 523)
insert into flights values('2009-08-15','01' , 708)
insert into flights values('2010-08-02','02' , 199)
go
create table signment(scn varchar(10),sname varchar(10))
insert into signment values('01' , '英国' )
insert into signment values('02' , '美国' )
go
--===================================
/*结果
------------------------------------
2010 美国 199.0
2009 英国 1231.0
------------------------------------
*/
--方法一:
select y,sname,sum(fmoney) as money from
(select substring(convert(varCHAR,b.flightdate,120),1,4) as y,a.sname,b.fmoney from signment a,flights b where a.scn=b.scn) as tb
group by y,sname
--方法二:
select SUBSTRING(CONVERT(varchar, ta.flightdate,120),1,4),tb.sname, SUM(ta.fmoney)
from flights ta,signment tb
where ta.scn=tb.scn
group by SUBSTRING(CONVERT(varchar, ta.flightdate,120),1,4),tb.sname