求一条SQL

Question

有3个表tb1,tb2,tb3
其中tb1:
学号   姓名
01     张三
02     李四
03     王五
04     赵六
05     陈七
............tb2:
学号   语文   数学
01     85     56
02     92     89
03     100    99
05     70     0
  ..............tb3:
学号   英语   物理
01     98     65
02     87     65
03     96     88
04     85     84
...............想用一条SQL查出：
名次    姓名   语文  数学   英语   物理   总分
 1      王五    100  99     96     88      383
 2      李四    92   89     87     65      333
 3      张三    85   56     98     65      304
 4      赵六    0    0      85     84      169
 5      陈七    70   0      0       0       70
.......................

Answer 1

select tb1.学号,tb2.语文,tb2.数学,tb3.英语,tb3.物理,总分=（tb2.语文+tb2.数学+tb3.英语+tb3.物理）
from tb1,tb2,tb3
where tb1.学号=tb2.学号=tb3.学号

Answer 2

;with tb as
(
select  姓名, 语文, 数学,英语, 物理,总分=(语文+ 数学+英语+ 物理)
from tb1, tb2, tb3
where tb1.学号=tb2.学号
and tb1.学号=tb2.学号
)
select 名次=row_number() over(order by 总分),    姓名 , 语文,  数学,  英语 , 物理,  总分
from tb

Answer 3

select tbtmp1.学号,tbtmp1.语文,tbtmp1.数学,tbtmp1.英语,tbtmp1.物理,（tbtmp1语文+tbtmp1.数学+tbtmp1.英语+tbtmp1.物理）as 总分 from (select tbtmp.*,tb3.英语,tb3.物理 from tbtmp left join (select tb1.学号,tb1.姓名,tb2.语文,tb2.数学 from tb1 left join tb2 on tb1.学号=tb2.学号) as tbtmp on tbtmp.学号=tb3.学号) as tbtmp1 order by tbtmp1.总分

Answer 4

--sql 2000
select  名次 = (selec count(1) from 
(
select tb1.* , 
       tb2.语文,
       tb2.数学,
       tb3.英语,
       tb3.物理,
       tb2.语文 + tb2.数学 + tb3.英语 + tb3.物理 总分
from tb1 , tb2 , tb3
where tb1.学号 = tb2.学号 and tb1.学号 = tb3.学号) n where 总分 < m.总分) + 1 , * from
(
select tb1.* , 
       tb2.语文,
       tb2.数学,
       tb3.英语,
       tb3.物理,
       tb2.语文 + tb2.数学 + tb3.英语 + tb3.物理 总分
from tb1 , tb2 , tb3
where tb1.学号 = tb2.学号 and tb1.学号 = tb3.学号
) m--如果考虑不存在的情况，则使用isnull
select  名次 = (selec count(1) from 
(
select tb1.* , 
       isnull(tb2.语文,0) 语文,
       isnull(tb2.数学,0) 数学,
       isnull(tb3.英语,0) 英语,
       isnull(tb3.物理,0) 物理,
       isnull(tb2.语文,0) +
       isnull(tb2.数学,0) +
       isnull(tb3.英语,0) +
       isnull(tb3.物理,0) 总分
from tb1 left join tb2 on tb1.学号 = tb2.学号 
left join tb3 on tb1.学号 = tb3.学号 
) n where 总分 < m.总分) + 1 , * from
(
select tb1.* , 
       isnull(tb2.语文,0) 语文,
       isnull(tb2.数学,0) 数学,
       isnull(tb3.英语,0) 英语,
       isnull(tb3.物理,0) 物理,
       isnull(tb2.语文,0) +
       isnull(tb2.数学,0) +
       isnull(tb3.英语,0) +
       isnull(tb3.物理,0) 总分
from tb1 left join tb2 on tb1.学号 = tb2.学号 
left join tb3 on tb1.学号 = tb3.学号 
) m
--sql 2005用row_number

Answer 5

有关名次的查询参考如下,因为可能要考虑重分的情况,名次应该是并列才对.表jh03有下列数据：
name　score
aa　　99
bb　　56
cc　　56
dd　　77
ee　　78
ff　　76
gg　　78
ff　　501. 名次生成方式1,Score重复时合并名次
SELECT *　,　Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name       Score        Place 
---------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        3
ff         76.00        4
bb         56.00        5
cc         56.00        5
ff         50.00        62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name       Score        Place 
--------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        4
ff         76.00        5
bb         56.00        6
cc         56.00        6
ff         50.00        8

Answer 6

修改了一下：
select tb1.学号,tb2.语文,tb2.数学,tb3.英语,tb3.物理,总分=（tb2.语文+tb2.数学+tb3.英语+tb3.物理）
from tb1,tb2,tb3
where tb1.学号=tb2.学号 and tb1.学号=tb3.学号

Answer 7

select tb1.学号,tb2.语文,tb2.数学,tb3.英语,tb3.物理,总分=（tb2.语文+tb2.数学+tb3.英语+tb3.物理）
from tb1,tb2,tb3
where tb1.学号=tb2.学号 and tb1.学号=tb3.学号
order by 总分

Answer 8

select tb1.学号,tb2.语文,tb2.数学,tb3.英语,tb3.物理,总分=（tb2.语文+tb2.数学+tb3.英语+tb3.物理） 
from tb1,tb2,tb3 
where tb1.学号=tb2.学号 and tb1.学号=tb3.学号 
order by 总分 desc

Answer 9

if object_id('tb1','U')is not null
drop table tb1
go
create table tb1
(
[学号] char(2),
[姓名] varchar(4)
)
insert into tb1 select '01','张三'
union all select '02','李四'
union all select '03','王五'
union all select '04','赵六'
union all select '05','陈七'if object_id('tb2','U')is not null
drop table tb2
go
create table tb2
(
[学号] char(2),
[语文] int,
[数学] int
)
insert into tb2 select '01',85,56 
union all select '02',92,89
union all select '03',100,99
union all select '05',70,0if object_id('tb3','U')is not null
drop table tb3
go
create table tb3
(
[学号] char(2),
[英语] int,
[物理] int
)
insert into tb3 select '01',98,65 
union all select '02',87,65
union all select '03',96,88
union all select '04',85,84--sql2005
;with tb as
(
select a.姓名,语文=isnull(b.语文,0),数学=isnull(b.数学,0),英语=isnull(c.英语,0),物理=isnull(c.物理,0),总分=(isnull(b.语文,0)+isnull(b.数学,0)+isnull(c.英语,0)+isnull(c.物理,0)) from tb1 as a left join tb2 as b on a.[学号]=b.[学号] 
left join tb3 as c on a.[学号]=c.[学号] 
)
select 名次=row_number() over(order by 总分 desc),姓名,语文,数学,英语,物理,总分 from tb
--sql2000
select 名次=identity(int,1,1),a.姓名,语文=isnull(b.语文,0),数学=isnull(b.数学,0),英语=isnull(c.英语,0),物理=isnull(c.物理,0),总分=(isnull(b.语文,0)+isnull(b.数学,0)+isnull(c.英语,0)+isnull(c.物理,0)) into #tp
from tb1 as a left join tb2 as b on a.[学号]=b.[学号] 
left join tb3 as c on a.[学号]=c.[学号] order by 总分 desc 
select * from #tp
/*
名次     姓名     语文     数学     英语     物理      总分
1 王五 100 99 96 88 383
2 李四 92 89 87 65 333
3 张三 85 56 98 65 304
4 赵六 0 0 85 84 169
5 陈七 70 0 0 0 70
*/

Answer 10

create table  Tbl
(
 xuehao int,
 xingming varchar(20) 
)
create table Tb2 
(
xuehao int,
yuwen  int,
shuxue int
)create table Tb3
(
xuehao int ,
yingyu int,
wuli int
)insert into Tbl values(1,'张三');
insert into Tbl values(2,'lilei');
insert into Tbl values(3,'lily');
insert into Tbl values(4,'sope');
insert into Tbl values(5,'kuPe');
insert into Tbl values(6,'junli')insert into tb2 values('1','83','89');
insert into tb2 values('2','88','85');
insert into tb2 values('3','98','62');
insert into tb2 values('4','84','90');
insert into tb2 values('5','88','92')insert into tb3 values('1','83','89');
insert into tb3 values('2','88','85');
insert into tb3 values('3','98','62');
insert into tb3 values('4','84','90');
insert into tb3 values('5','88','92')select a.xingming,b.*,c.*, zongfen=b.yuwen+b.shuxue+c.yingyu+c.wuli
from Tbl a,tb2 b,tb3 c  
where a.xuehao=b.xuehao and b.xuehao=c.xuehao
order by zongfen desc

Answer 11

-----sql2000 
如果 分数相同 名次会不同 ，不符合实际要求。

Answer 12

DAWUGUI又出手了，无敌了，正解都出来了，详细。

Answer 13

哦，得差出来，在用order by 进行排序