有两个表
graID graNO graName graScore
----------- -------------------- -------------------------------------------------- -----------
1 s1101 vb 80
2 s1101 c 90
3 s1102 c 60
4 s1102 vb 80
stuID stuNO stuName stuEmail stuScore
----------- --------------------------- ----------------------- -----------
1 s1101 vb [email protected] 80
2 s1102 c [email protected] 60
3 s1102 vb [email protected] 80
第一张表是结业考试的成绩
第二张表是平时成绩使用一条查询语句显示每个学员每门课程的总成绩,总成绩等于平时成绩*0.4+结业成绩*0.6,当总成绩>=60,则需显示及格,否则显示不及格。注意:如果学员某门课程没有平时成绩则视为平时成绩为0分,例如:s11001没有课程c的平时成绩,因此其c的总成绩为:0*0.4+90*0.6=54我写了一个,可是不对,大家帮看看,最困扰我的是为什么连接出来后数据是6行select graNO ,graName ,总成绩=isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 , 及格情况=case
when isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 >=60 then '及格'
else '不及格'
end
from sScore as s inner join graduateScore as g on s.stuNO =g.graNO
graID graNO graName graScore
----------- -------------------- -------------------------------------------------- -----------
1 s1101 vb 80
2 s1101 c 90
3 s1102 c 60
4 s1102 vb 80
stuID stuNO stuName stuEmail stuScore
----------- --------------------------- ----------------------- -----------
1 s1101 vb [email protected] 80
2 s1102 c [email protected] 60
3 s1102 vb [email protected] 80
第一张表是结业考试的成绩
第二张表是平时成绩使用一条查询语句显示每个学员每门课程的总成绩,总成绩等于平时成绩*0.4+结业成绩*0.6,当总成绩>=60,则需显示及格,否则显示不及格。注意:如果学员某门课程没有平时成绩则视为平时成绩为0分,例如:s11001没有课程c的平时成绩,因此其c的总成绩为:0*0.4+90*0.6=54我写了一个,可是不对,大家帮看看,最困扰我的是为什么连接出来后数据是6行select graNO ,graName ,总成绩=isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 , 及格情况=case
when isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 >=60 then '及格'
else '不及格'
end
from sScore as s inner join graduateScore as g on s.stuNO =g.graNO
when isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 >=60 then '及格'
else '不及格'
end
from sScore as s full join graduateScore as g on s.stuNO =g.graNO and s.stuName=g.graName
,总成绩=isnull(s.stuScore,0)*0.4+isnull(g.graScore,0)*0.6
, 及格情况=case when isnull(s.stuScore,0)*0.4+isnull(g.graScore,0)*0.6 >=60 then '及格'
else '不及格'
end
from graduateScore as g left join sScore as s on s.stuNO =g.graNO and g.graName=s.stuName
那么可将full join 换成 right join.
case when isnull(s.grascore ,0)+ isnull(g.grascore ,0) > 60 then '及格'
else '不及格'
end
from
(select grano,graName ,sum(grascore * 0.4) as grascore from sscore group by grano,graName )s
full join
(select grano,graName ,sum(grascore * 0.6) as grascore from graduateScore group by grano,graName ) g
on s.stuNO =g.graNO and s.graName =g.graName
select isnull(s.stuNO,g.graNO) graNO ,isnull(s.stuName,g.graName) graName ,总成绩=isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 , 及格情况=case
when isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 >=60 then '及格'
else '不及格'
end
from sScore as s full join graduateScore as g on s.stuNO =g.graNO and s.stuName=g.graName
go
create table [ta]([graID] int,[graNO] varchar(10),[graName] varchar(10),[graScore] int)
insert [ta] select 1,'s1101','vb',80
union all select 2,'s1101','c',90
union all select 3,'s1102','c',60
union all select 4,'s1102','vb',80if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([stuID] int,[stuNO] varchar(10),[stuName] varchar(10),[stuEmail] varchar(30),[stuScore] int)
insert [tb] select 1,'s1101','vb','[email protected]',80
union all select 2,'s1102','c','[email protected]',60
union all select 3,'s1102','vb','[email protected]',80/*
使用一条查询语句显示每个学员每门课程的总成绩,
总成绩等于平时成绩*0.4+结业成绩*0.6,当总成绩>=60,
则需显示及格,否则显示不及格。
注意:如果学员某门课程没有平时成绩则视为平时成绩为0分,
例如:s1101没有课程c的平时成绩,因此其c的总成绩为:0*0.4+90*0.6=54
*/select
g.graNO ,
g.graName ,
总成绩=isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 ,
及格情况=case when isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 >=60 then '及格' else '不及格' end
from ta as g
left join tb as s
on s.stuNO =g.graNO and s.[stuName]=g.[graName]
/*
graNO graName 总成绩 及格情况
---------- ---------- --------------------------------------- ------
s1101 vb 80.0 及格
s1101 c 54.0 不及格
s1102 c 60.0 及格
s1102 vb 80.0 及格(4 行受影响)*/
go
create table [ta]([graID] int,[graNO] varchar(10),[graName] varchar(10),[graScore] int)
insert [ta] select 1,'s1101','vb',80
union all select 2,'s1101','c',90
union all select 3,'s1102','c',60
union all select 4,'s1102','vb',80if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([stuID] int,[stuNO] varchar(10),[stuName] varchar(10),[stuEmail] varchar(30),[stuScore] int)
insert [tb] select 1,'s1101','vb','[email protected]',80
union all select 2,'s1102','c','[email protected]',60
union all select 3,'s1102','vb','[email protected]',80/*
使用一条查询语句显示每个学员每门课程的总成绩,
总成绩等于平时成绩*0.4+结业成绩*0.6,当总成绩>=60,
则需显示及格,否则显示不及格。
注意:如果学员某门课程没有平时成绩则视为平时成绩为0分,
例如:s1101没有课程c的平时成绩,因此其c的总成绩为:0*0.4+90*0.6=54
*/select
isnull(g.graNO,s.[stuNO]) [No],
isnull(g.graName,s.stuName) [Name],
总成绩=isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 ,
及格情况=case when isnull(stuScore,0)*0.4+isnull(graScore,0)*0.6 >=60 then '及格' else '不及格' end
from ta as g
full join tb as s
on s.stuNO =g.graNO and s.[stuName]=g.[graName]
/*
No Name 总成绩 及格情况
---------- ---------- --------------------------------------- ------
s1101 vb 80.0 及格
s1101 c 54.0 不及格
s1102 c 60.0 及格
s1102 vb 80.0 及格(4 行受影响)
*/应该用full join
select stuNO,
stuName,
总成绩=sum(isnull(stuScore,0))*0.4 + sum(isnull(graScore,0))*0.6,
是否及格 = case when sum(isnull(stuScore,0))*0.4 + sum(isnull(graScore,0))*0.6 >=60 then '及格' else '不及格' end
from
(select * from
(select graNO,graName,sum(isnull(graScore,0)) graScore from table1 group by graNo,graName)a,
(select stuNo,stuName,sum(isnull(stuScore,0)) stuScore from table2 group by stuNo,stuName)b
where a.graNO = b.stuNo and a.graName = b.stuName)T
group by stuNO,stuName
也用了.可是就是不知道该用full join
试full join时,也根本想不到 该再加个条件 and s.stuName=g.graName
那么这个full join 应该什么时候用呢?
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([graID] int,[graNO] varchar(10),[graName] varchar(10),[graScore] int)
insert [table1] select 1,'s1101','vb',80
union all select 2,'s1101','c',90
union all select 3,'s1102','c',60
union all select 4,'s1102','vb',80if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([stuID] int,[stuNO] varchar(10),[stuName] varchar(10),[stuEmail] varchar(30),[stuScore] int)
insert [table2] select 1,'s1101','vb','[email protected]',80
union all select 2,'s1102','c','[email protected]',60
union all select 3,'s1102','vb','[email protected]',80
select graNO,
graName,
总成绩=sum(isnull(stuScore,0))*0.4 + sum(isnull(graScore,0))*0.6,
是否及格 = case when sum(isnull(stuScore,0))*0.4 + sum(isnull(graScore,0))*0.6 >=60 then '及格' else '不及格' end
from
(select * from
(select graNO,graName,sum(isnull(graScore,0)) graScore from table1 group by graNo,graName)a
left join
(select stuNo,stuName,sum(isnull(stuScore,0)) stuScore from table2 group by stuNo,stuName)b
on a.graNO = b.stuNo and a.graName = b.stuName)T
group by graNO,graName
right join:反之;
full join:左、右表全得有,谁没有谁就用null凑。