表a 语文成绩
sno sco
1 20
2 30
3 35
4 49
表b 数学成绩
sno sco
1 11
2 22
3 33
4 31
表c 英语成绩
sno sco
1 120
2 130
3 135
4 149
求总成绩
SELECT a.sno,a.sco AS '语文' ,b.sco AS '英语',c.sco AS '文综'
FROM a JOIN b ON a.sno=b.sno
JOIN c ON a.sno=c.sno
还有其他方法么，感觉这么写很2
若有九个表又该如何，考虑大数据优化问题！
------解决方案--------------------
有多少个表就要关联多少次,没得省了.如果表都没有关联,哪来的数据呢
------解决方案--------------------
怎么说呢，你不应该说这么联结很二，你应该说表设计人员太二，尼玛又一个这样设计表的么
------解决方案--------------------
就这种写法，不2
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-27 08:12:33
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([sno] int,[sco] int)
insert [a]
select 1,20 union all
select 2,30 union all
select 3,35 union all
select 4,49
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([sno] int,[sco] int)
insert [b]
select 1,11 union all
select 2,22 union all
select 3,33 union all
select 4,31
--> 测试数据：[c]
if object_id('[c]') is not null drop table [c]
go
create table [c]([sno] int,[sco] int)
insert [c]
select 1,120 union all
select 2,130 union all
select 3,135 union all
select 4,149
--------------开始查询--------------------------
SELECT a.sno,a.sco AS '语文' ,b.sco AS '英语',c.sco AS '文综',a.sco+b.sco+c.sco '总成绩'
FROM a JOIN b ON a.sno=b.sno
JOIN c ON a.sno=c.sno
----------------结果----------------------------
/*
sno 语文 英语 文综 总成绩
----------- ----------- ----------- ----------- -----------
1 20 11 120 151
2 30 22 130 182
3 35 33 135 203
4 49 31 149 229
*/