求教各位高手一道SQL语句。谢谢了。

Question

现有表及题目要求如下：
/*赛马（赛马编号，赛马颜色，赛马产地）
　　比赛记录（场次，获胜赛马编号）
　　投注记录（场次，会员编号，所选赛马编号）
　　投注过程：每场，会员可以投一匹马，投注化2元，获胜可以得到10元奖励。
　　题目： 使用SQL语句确定赚钱最多的用户（同时考虑获得与亏损的情况）。
*/
下面是我建立的表及数据。
create database netfm
use netfm 
create table Numbers(
NumberID char(6) primary key,
NName char(50) not null,
NRddress char(100) not null,
NCash int check(NCash>0),
)
create table Horses
(
 HorseID char(6) primary key,
 Hcolor varchar(20) not null,
 Haddress varchar(50) not null,
)
create table WinnerRecord
(
RecordID char(6) primary key,
HorseID char(6) foreign key(HorseID) references Horses(HorseID)
)
create table  HistoryRecord
(RecordID char(6) not null,
 NumberID char(6) not null,
 HorseID  char(6) not null,
 foreign key (RecordID) references WinnerRecord(RecordID),
 foreign key (NumberID) references Numbers(NumberID),
 foreign key (HorseID)  references Horses(HorseID),
 primary key(RecordID,NumberID,HorseID)
)*/
/*
insert into Numbers
values('N00001','netfm','waiqiangsi road 21#',50)
insert into Numbers
values('N00002','asan','tianmei road 17#',100)
insert into Numbers
values('N00003','endlesshorizon','zhongguancui road 12#',80)
insert into Numbers
values('N00004','snowdream','pku west  road 13#',30)
insert into Numbers
values('N00005','keane','xiafeilu road 87#',250)insert into Horses
values('H00001','white','England')
insert into Horses
values('H00002','black','NewZeland')
insert into Horses
values('H00003','gray','American')
insert into Horses
values('H00004','orange','Frace')
insert into Horses
values('H00005','yellow','china')
delete from HistoryRecord
 delete from WinnerRecord
 
insert into WinnerRecord
values('R00001','H00001')
insert into WinnerRecord
values('R00002','H00002')
insert into WinnerRecord
values('R00003','H00001')
insert into WinnerRecord
values('R00004','H00001')
insert into WinnerRecord
values('R00005','H00002')
insert into WinnerRecord
values('R00006','H00001')insert into HistoryRecord 
values('R00001','N00001','H00001')
insert into HistoryRecord
values('R00001','N00001','H00002')
insert into HistoryRecord
values('R00001','N00002','H00001')
insert into HistoryRecord
values('R00001','N00002','H00003')
insert into HistoryRecord
values('R00001','N00002','H00004')insert into HistoryRecord
values('R00002','N00001','H00001')
insert into HistoryRecord
values('R00002','N00001','H00002')
insert into HistoryRecord
values('R00002','N00001','H00003')
insert into HistoryRecord
values('R00002','N00002','H00003')
insert into HistoryRecord
values('R00002','N00002','H00004')
insert into HistoryRecord
values('R00002','N00003','H00001')
insert into HistoryRecord
values('R00002','N00004','H00001')
insert into HistoryRecord
values('R00002','N00004','H00002')insert into HistoryRecord
values('R00003','N00001','H00002')
insert into HistoryRecord
values('R00003','N00001','H00003')
insert into HistoryRecord
values('R00003','N00002','H00003')
insert into HistoryRecord
values('R00003','N00002','H00004')
insert into HistoryRecord
values('R00003','N00003','H00001')
insert into HistoryRecord
values('R00003','N00004','H00001')
insert into HistoryRecord
values('R00003','N00004','H00002')

Answer 1

select top 1 
       a.nname,
       [money]=sum(case when c.recordid is not null and c.horseid is not null then 10 else -2 end)
from Numbers a
join HistoryRecord b on a.numberid=b.numberid
left join WinnerRecord c on b.horseid=c.horseid and b.recordid=c.recordid
group by a.nname
order by [money] desc
/*
nname                                              money       
-------------------------------------------------- ----------- 
snowdream                                          16
*/

Answer 2

投注花2元,输赢都得减。
select top 1 
       a.nname,
       [money]=sum(case when c.recordid is not null and c.horseid is not null then 10-2 else -2 end)
from Numbers a
join HistoryRecord b on a.numberid=b.numberid
left join WinnerRecord c on b.horseid=c.horseid and b.recordid=c.recordid
group by a.nname
order by [money] desc
/*
nname                                              money       
-------------------------------------------------- ----------- 
snowdream                                          12
*/

Answer 3

select top 1 numberid,my from (
select sum(my)as my,numberid from (
select numberid,historyrecord.recordid,horseid,my=
case horseid when 
(select horseid from winnerrecord where winnerrecord.recordid=historyrecord.recordid) 
then 10 else -2 
end 
from historyrecord
) as newtable1 group by numberid
) as newtable2 order by my desc
结果：/*
    numberid    my
1    N00004     16
*/

Answer 4

呵呵，join效率不高哦，简单测试了一下，我的效率是他的两倍。