关于INNER JOIN

[fanren945]

SELECT dbo.TBL_Result1.Quest2Ans AS Q2, dbo.TBL_Result1.Quest1Ans AS Q1,
dbo.TBL_Result1.Quest4Ans AS Q4, dbo.TBL_Result1.Quest3Ans AS Q3,
dbo.TBL_Result1.Quest5Ans AS Q5, dbo.TBL_Result1.Quest6Ans AS Q6,
dbo.TBL_Result1.Quest7Ans AS Q7, dbo.TBL_Result1.Quest8Ans AS Q8,
dbo.TBL_Result1.Quest9Ans AS Q9, dbo.TBL_Result1.Quest10Ans AS Q10,
dbo.TBL_Student.StudentClass, dbo.TBL_Student.StudentGrade,
dbo.TBL_Student.StudentSex,dbo.TBL_Student.SID,
dbo.TBL_Student.SchoolId AS SchoolId, dbo.TBL_Student.ClassId AS ClassId,
dbo.TBL_Student.AreaId AS AreaId, dbo.TBL_School.SchoolName AS SchoolN,
dbo.TBL_Class.ClassName AS ClassN, dbo.TBL_Area.AreaName AS AreaN
FROM dbo.TBL_Student INNER JOIN
dbo.TBL_Result1 ON dbo.TBL_Student.SID = dbo.TBL_Result1.SID INNER JOIN
dbo.TBL_School ON
dbo.TBL_Student.SchoolId = dbo.TBL_School.SchoolId INNER JOIN
dbo.TBL_Class ON
dbo.TBL_Student.ClassId = dbo.TBL_Class.ClassId INNER JOIN
dbo.TBL_Area ON
dbo.TBL_Student.AreaId = dbo.TBL_Area.AreaId Where (1=1)
and (dbo.TBL_Student.AreaId=370200)
and (dbo.TBL_Student.SchoolId=103)
and (dbo.TBL_Student.StudentGrade='2005级')


以上语句执行后会返回很多的重复记录为什么??

[fanren945]

Select DISTINCT SID from (
SELECT dbo.TBL_Result1.Quest2Ans AS Q2, dbo.TBL_Result1.Quest1Ans AS Q1,
dbo.TBL_Result1.Quest4Ans AS Q4, dbo.TBL_Result1.Quest3Ans AS Q3,
dbo.TBL_Result1.Quest5Ans AS Q5, dbo.TBL_Result1.Quest6Ans AS Q6,
dbo.TBL_Result1.Quest7Ans AS Q7, dbo.TBL_Result1.Quest8Ans AS Q8,
dbo.TBL_Result1.Quest9Ans AS Q9, dbo.TBL_Result1.Quest10Ans AS Q10,
dbo.TBL_Student.StudentClass, dbo.TBL_Student.StudentGrade,
dbo.TBL_Student.StudentSex,dbo.TBL_Student.SID,
dbo.TBL_Student.SchoolId AS SchoolId, dbo.TBL_Student.ClassId AS ClassId,
dbo.TBL_Student.AreaId AS AreaId, dbo.TBL_School.SchoolName AS SchoolN,
dbo.TBL_Class.ClassName AS ClassN, dbo.TBL_Area.AreaName AS AreaN
FROM dbo.TBL_Student INNER JOIN
dbo.TBL_Result1 ON dbo.TBL_Student.SID = dbo.TBL_Result1.SID INNER JOIN
dbo.TBL_School ON
dbo.TBL_Student.SchoolId = dbo.TBL_School.SchoolId INNER JOIN
dbo.TBL_Class ON
dbo.TBL_Student.ClassId = dbo.TBL_Class.ClassId INNER JOIN
dbo.TBL_Area ON
dbo.TBL_Student.AreaId = dbo.TBL_Area.AreaId
Where (1=1)
AND dbo.TBL_Student.SchoolId=103
AND dbo.TBL_Student.StudentGrade='2005级'
) e

这样是ok的

Select DISTINCT * from (
SELECT dbo.TBL_Result1.Quest2Ans AS Q2, dbo.TBL_Result1.Quest1Ans AS Q1,
dbo.TBL_Result1.Quest4Ans AS Q4, dbo.TBL_Result1.Quest3Ans AS Q3,
dbo.TBL_Result1.Quest5Ans AS Q5, dbo.TBL_Result1.Quest6Ans AS Q6,
dbo.TBL_Result1.Quest7Ans AS Q7, dbo.TBL_Result1.Quest8Ans AS Q8,
dbo.TBL_Result1.Quest9Ans AS Q9, dbo.TBL_Result1.Quest10Ans AS Q10,
dbo.TBL_Student.StudentClass, dbo.TBL_Student.StudentGrade,
dbo.TBL_Student.StudentSex,dbo.TBL_Student.SID,
dbo.TBL_Student.SchoolId AS SchoolId, dbo.TBL_Student.ClassId AS ClassId,
dbo.TBL_Student.AreaId AS AreaId, dbo.TBL_School.SchoolName AS SchoolN,
dbo.TBL_Class.ClassName AS ClassN, dbo.TBL_Area.AreaName AS AreaN
FROM dbo.TBL_Student INNER JOIN
dbo.TBL_Result1 ON dbo.TBL_Student.SID = dbo.TBL_Result1.SID INNER JOIN
dbo.TBL_School ON
dbo.TBL_Student.SchoolId = dbo.TBL_School.SchoolId INNER JOIN
dbo.TBL_Class ON
dbo.TBL_Student.ClassId = dbo.TBL_Class.ClassId INNER JOIN
dbo.TBL_Area ON
dbo.TBL_Student.AreaId = dbo.TBL_Area.AreaId
Where (1=1)
AND dbo.TBL_Student.SchoolId=103
AND dbo.TBL_Student.StudentGrade='2005级'
) e

这样就有n多重复记录了

[paoluo]

因為你的表是一對多關係，所以重復紀錄很正常。


假設數據如下：
A表
ID
1

B表
ID Name
1 aa
1 bb
1 cc


Select A.ID,B.Name From A Inner Join B OnA.ID=B.ID
兩表關聯，數據就會重復，重復的ID就有三條。

這時，可以這樣削除重復
Select A.ID,B.Name From A Inner Join (Select ID,Min(Name) As Name From B Group By ID) C On A.ID=C.ID

[fanren945]

SELECT dbo.TBL_Result1.Quest2Ans AS Q2,
dbo.TBL_Result1.Quest1Ans AS Q1, dbo.TBL_Result1.Quest4Ans AS Q4,
dbo.TBL_Result1.Quest3Ans AS Q3, dbo.TBL_Result1.Quest5Ans AS Q5,
dbo.TBL_Result1.Quest6Ans AS Q6, dbo.TBL_Result1.Quest7Ans AS Q7,
dbo.TBL_Result1.Quest8Ans AS Q8, dbo.TBL_Result1.Quest9Ans AS Q9,
dbo.TBL_Result1.Quest10Ans AS Q10, dbo.TBL_Student.StudentClass,
dbo.TBL_Student.StudentGrade, dbo.TBL_Student.StudentSex, dbo.TBL_Student.SID,
dbo.TBL_Student.SchoolId, dbo.TBL_Student.ClassId, dbo.TBL_Student.AreaId
FROM dbo.TBL_Student INNER JOIN
dbo.TBL_Result1 ON dbo.TBL_Student.SID = dbo.TBL_Result1.SID
WHERE (dbo.TBL_Student.SchoolId = 103) AND
(dbo.TBL_Student.StudentGrade = '2005级')

（所影响的行数为 133 行）

即便是1对1仍然有重复记录啊 为什么

SELECT COUNT(*) FROM TBL_Student WHERE (SchoolId = 103) AND (StudentGrade = '2005级')
正常是47

[paoluo]

在TBL_Result1中，一個SID只有一條紀錄？？

[fanren945]

是的 与TBL_Student的 SID相对应的

[quanyi]

你要确定哪个是一对多的关系

[paoluo]

try:

Select SID From TBL_Result1
Select Distinct SID From TBL_Result1

看這兩條語句的紀錄條數是不是一樣的？？

[jasonren]

表间是一对多关系

[新鲜鱼排]

我觉得这个关系你自己都需要考虑很长时间，我们刚看就更没有头绪了。
我觉得你应该以dbo.TBL_Student为基础表,然后一个表一个表的添加,看看问题出在哪里.

[itblog]

最重要的是要保证你的表是一对一的话,一对多的关系就会出现重复记录~

[云中客]

应该以某个表为基础表,来执行联接

[fanren945]

回复paoluo(一天到晚游泳的鱼)大侠, 两数不一样````
其中SID相同者甚多```` 是前任设计数据库的时候不够严谨造成的```` 恨死
实际上如果数据库中没有重复数据的话我发的那个应该得出的记录数是实际记录数的2倍
现在知道为什么了,散分

谢谢各位关注

[hyrongg]

哪个表之间是一对多的关系？

[hyrongg]

create table tbl_area(AreaId int,AreaName nvarchar(10),Organization nvarchar(10))
insert into tbl_area select 1,'Area1','O1'
union all select 2,'Area2','O2'
union all select 3,'Area3','O3'
union all select 4,'Area4','O4'

create table tbl_result1(SID nvarchar(5),Quest1Ans nvarchar(10),Quest2Ans nvarchar(10),Quest3Ans nvarchar(15),
Quest4Ans nvarchar(10),Quest5Ans nvarchar(15),Quest6Ans nvarchar(15),Quest7Ans nvarchar(15),
Quest8Ans nvarchar(15),Quest9Ans nvarchar(10),Quest10Ans nvarchar(10))

insert into tbl_result1 select 'A1','Q1','Q2','','','','','','','','Q10'
union all select 'A2','Q1','Q2','','','','','','','Q9','Q10'
union all select 'A3','Q1','Q2','','','','','','Q8','Q9','Q10'
union all select 'A4','Q1','Q2','q3','','','','','Q8','Q9','Q10'
union all select 'A5','Q1','Q2','q3','q4','','','','Q8','Q9','Q10'

create table tbl_school(SchoolId int,SchoolName nvarchar(20),AreaId int)
insert into tbl_school select 1,'SchoolA',1
union all select 2,'SchoolB',2
union all select 3,'SchoolC',3
union all select 4,'SchoolD',4
union all select 5,'SchoolE',1
union all select 6,'SchoolF',1
union all select 7,'SchoolG',2


create table tbl_student(SID nvarchar(5),StudentName nvarchar(10),StudentClass nvarchar(10),StudentGrade nvarchar(10),StudentSex nvarchar(10),
SchoolId int,ClassId nvarchar(5),AreaId int)
insert into tbl_student select 'A1','Name1','c1','2005','male',1,'C001',1
union all select 'A2','Name2','c2','2001','fmale',2,'C002',2
union all select 'A3','Name3','c3','2005','male',2,'C003',3
union all select 'A4','Name3','c3','2005','male',2,'C003',1
union all select 'A5','Name3','c3','2005','male',2,'C003',1

create table tbl_class(classid nvarchar(5),classname nvarchar(15))
insert into tbl_class select 'C001','cname1'
union all select 'C002','cname2'
union all select 'C003','cname3'



SELECT dbo.TBL_Result1.Quest2Ans AS Q2, dbo.TBL_Result1.Quest1Ans AS Q1,
dbo.TBL_Result1.Quest4Ans AS Q4, dbo.TBL_Result1.Quest3Ans AS Q3,
dbo.TBL_Result1.Quest5Ans AS Q5, dbo.TBL_Result1.Quest6Ans AS Q6,
dbo.TBL_Result1.Quest7Ans AS Q7, dbo.TBL_Result1.Quest8Ans AS Q8,
dbo.TBL_Result1.Quest9Ans AS Q9, dbo.TBL_Result1.Quest10Ans AS Q10,
dbo.TBL_Student.StudentClass, dbo.TBL_Student.StudentGrade,
dbo.TBL_Student.StudentSex,dbo.TBL_Student.SID,
dbo.TBL_Student.SchoolId AS SchoolId, dbo.TBL_Student.ClassId AS ClassId,
dbo.TBL_Student.AreaId AS AreaId, dbo.TBL_School.SchoolName AS SchoolN,
dbo.TBL_Class.ClassName AS ClassN, dbo.TBL_Area.AreaName AS AreaN
FROM dbo.TBL_Student
INNER JOIN dbo.TBL_Result1 ON dbo.TBL_Student.SID = dbo.TBL_Result1.SID
INNER JOIN dbo.TBL_School ON dbo.TBL_Student.SchoolId = dbo.TBL_School.SchoolId
INNER JOIN dbo.TBL_Class ON dbo.TBL_Student.ClassId = dbo.TBL_Class.ClassId
INNER JOIN dbo.TBL_Area ON dbo.TBL_Student.AreaId = dbo.TBL_Area.AreaId Where (1=1)
--and (dbo.TBL_Student.AreaId=1)
--and (dbo.TBL_Student.SchoolId=1)
--and (dbo.TBL_Student.StudentGrade='2005')



drop table tbl_area
drop table tbl_result1
drop table tbl_school
drop table tbl_student
drop table tbl_class
------------------------------------
Q2 Q1 Q10 c1 2005 male A1 1 C001 1 SchoolA cname1 Area1
Q2 Q1 q4 q3 Q8 Q9 Q10 c3 2005 male A5 2 C003 1 SchoolB cname3 Area1
Q2 Q1 Q9 Q10 c2 2001 fmale A2 2 C002 2 SchoolB cname2 Area2
Q2 Q1 Q8 Q9 Q10 c3 2005 male A3 2 C003 3 SchoolB cname3 Area3
Q2 Q1 q3 Q8 Q9 Q10 c3 2005 male A4 2 C003 1 SchoolB cname3 Area1



也没有见到重复的呀，把结构说清楚

[liangpei2008]

Inner Join:根据每个表共有的列的值匹配两个表中的行。

[zxbyhcsdn]

看看

[fanren945]

5个表 实际上是2个表或3个表有相同列 然后互相匹配的~~ 头疼

[fanren945]

TBL_Student这个表是基础表 是一对多的关系

表TBL_Student
"SID","StudentName","StudentClass","StudentGrade","StudentSex","SchoolId","ClassId","AreaId"

表TBL_Result1
"SID","Quest1Ans","Quest2Ans","Quest3Ans","Quest4Ans","Quest5Ans","Quest6Ans","Quest7Ans","Quest8Ans","Quest9Ans","Quest10Ans"

表TBL_Area
"AreaId","AreaName","Organization"

表TBL_School
"SchoolId","SchoolName","AreaId"

表TBL_Class
"ClassId","ClassName"

TBL_Student and TBL_Result1 通过TBL_Student.SID
TBL_Student and TBL_School 通过 TBL_Student.SchoolId
TBL_Student and TBL_Class 通过TBL_Student.ClassId
TBL_Student and TBL_Area 通过 TBL_Student.AreaId

如何排除重复记录?

[fanren945]

表TBL_Area
"AreaId","AreaName","Organization"

表TBL_Result1
"SID","Quest1Ans","Quest2Ans","Quest3Ans","Quest4Ans","Quest5Ans","Quest6Ans","Quest7Ans","Quest8Ans","Quest9Ans","Quest10Ans"

表TBL_School
"SchoolId","SchoolName","AreaId"

表TBL_Student
"SID","StudentName","StudentClass","StudentGrade","StudentSex","SchoolId","ClassId","AreaId"

表TBL_Class
"ClassId","ClassName"
主要是通过TBL_Student这个表进行联结

[fanren945]

执行 select count(*) from dbo.TBL_Student 一共才1121条记录
但是执行

Select count(*) from (
SELECT dbo.TBL_Result1.Quest2Ans AS Q2, dbo.TBL_Result1.Quest1Ans AS Q1,
dbo.TBL_Result1.Quest4Ans AS Q4, dbo.TBL_Result1.Quest3Ans AS Q3,
dbo.TBL_Result1.Quest5Ans AS Q5, dbo.TBL_Result1.Quest6Ans AS Q6,
dbo.TBL_Result1.Quest7Ans AS Q7, dbo.TBL_Result1.Quest8Ans AS Q8,
dbo.TBL_Result1.Quest9Ans AS Q9, dbo.TBL_Result1.Quest10Ans AS Q10,
dbo.TBL_Student.StudentClass, dbo.TBL_Student.StudentGrade,
dbo.TBL_Student.StudentSex,dbo.TBL_Student.SID,
dbo.TBL_Student.SchoolId AS SchoolId, dbo.TBL_Student.ClassId AS ClassId,
dbo.TBL_Student.AreaId AS AreaId, dbo.TBL_School.SchoolName AS SchoolN,
dbo.TBL_Class.ClassName AS ClassN, dbo.TBL_Area.AreaName AS AreaN
FROM dbo.TBL_Student INNER JOIN
dbo.TBL_Result1 ON dbo.TBL_Student.SID = dbo.TBL_Result1.SID INNER JOIN
dbo.TBL_School ON
dbo.TBL_Student.SchoolId = dbo.TBL_School.SchoolId INNER JOIN
dbo.TBL_Class ON
dbo.TBL_Student.ClassId = dbo.TBL_Class.ClassId INNER JOIN
dbo.TBL_Area ON
dbo.TBL_Student.AreaId = dbo.TBL_Area.AreaId Where (1=1)
) e

确有6867条记录,晕晕 哪来那么多啊