学习SQL，大神来指导下

[避风港]

create database stus
create table total_score
(
	stuid char(6),
	sum_score char(4)
)
create table stuInfo
(
	stuid char(6),
	stuName char(8)
)
create table Chinese
(
	stuid char(6),
	score char(4)
)
create table Math
(
	stuid char(6),
	score char(4)
)
create table English
(
	stuid char(6),
	score char(4)
)

insert into total_score values('100001','225')
insert into total_score values('100002','210')
insert into total_score values('100003','195')

insert into total_score values('100001','张三')
insert into total_score values('100002','李四')
insert into total_score values('100003','王五')

insert into Chinese values('100001','80')
insert into Chinese values('100002','90')
insert into Chinese values('100003','95')

insert into Math values('100001','80')
insert into Math values('100002','85')
insert into Math values('100003','92')

insert into English values('100001','70')
insert into English values('100002','78')
insert into English values('100003','98')

--用一条语句求出语文成绩+英语成绩+数学成绩和总分不符的记录
--结果包含stuInfo.stuid,stuInfo.stuName,total_score.sum_score,Chinese.score,Math.score,English.score

[避风港]

这个例子不太好 唉 语文数学英语 的分数可能是几门课程的分加起来的

[發糞塗牆]

数据做好一点，stuinfo表都没数据的

[避风港]

create database stus
create table total_score
(
	stuid char(6),
	sum_score char(4)
)
create table stuInfo
(
	stuid char(6),
	stuName char(8)
)
create table Chinese
(
	stuid char(6),
	score char(4)
)
create table Math
(
	stuid char(6),
	score char(4)
)
create table English
(
	stuid char(6),
	score char(4)
)

insert into total_score values('100001','225')
insert into total_score values('100002','210')
insert into total_score values('100003','195')

insert into stuInfo values('100001','张三')
insert into stuInfo values('100002','李四')
insert into stuInfo values('100003','王五')

insert into Chinese values('100001','40') --作文
insert into Chinese values('100002','20') --选择题
insert into Chinese values('100003','30') --判断题
insert into Chinese values('100001','30')
insert into Chinese values('100002','20')
insert into Chinese values('100003','40')
insert into Chinese values('100001','30')
insert into Chinese values('100002','35')
insert into Chinese values('100003','20')


insert into Math values('100001','25')
insert into Math values('100002','25')
insert into Math values('100003','25')
insert into Math values('100001','25')
insert into Math values('100002','25')
insert into Math values('100003','25')
insert into Math values('100001','25')
insert into Math values('100002','25')
insert into Math values('100003','25')

insert into English values('100001','25')
insert into English values('100002','25')
insert into English values('100003','25')
insert into English values('100001','25')
insert into English values('100002','25')
insert into English values('100003','25')
insert into English values('100001','25')
insert into English values('100002','25')
insert into English values('100003','25')

--用一条语句求出语文成绩+英语成绩+数学成绩和总分不符的记录
--结果包含stuInfo.stuid,stuInfo.stuName,total_score.sum_score,Chinese.score,Math.score,English.score

这样再看看

[發糞塗牆]

再整理一下 1、分数你用char干嘛？换int 2、 insert into Chinese values('100001','40') --作文 insert into Chinese values('100002','20') --选择题 insert into Chinese values('100003','30') --判断题 这三条，不应该是同一个人的吗？为什么stuid还分3个？

[避风港]

我再整理下吧 有点乱了现在 - -

[昵称90天可改一次]

select a.stuid,a.stuName,b.sum_score,c.score,d.score,e.score
from stuInfo a
inner join total_score b
on a.stuid=b.stuid
inner join (select stuid,score=SUM(convert(int,score)) from Chinese group by stuid) c
on a.stuid=c.stuid
inner join (select stuid,score=SUM(convert(int,score))  from Math group by stuid) d
on a.stuid=d.stuid
inner join (select stuid,score=SUM(convert(int,score))  from English group by stuid) e
on a.stuid=e.stuid
where b.sum_score<>(c.score+d.score+e.score)

/*
100001	张三    	225 	100	75	75
100002	李四    	210 	75	75	75
100003	王五    	195 	90	75	75
*/

[避风港]

谢谢你，感觉这个例子不太能准确说明问题，不过你的语法值得借鉴，我再研究下了 不好意思一激动 关贴了 没能给分 很抱歉