如何在使用内连接时解决数据重复问题? [英] how to slove data duplication when using inner join?
问题描述
我是sql sevre 2008的新手Actuvally我每张桌子都有3张桌子我想要显示一些像桌子一样的场地
studentregistration:
stdRid stdRClass ... so on
1 A
2 B
3 C
4 A
5 B
studentDetails:
stdDid stdDname stdDMarks ..so on
1 xx 30
2 yy 50
3 zz 43
4 uu 21
5 ll 36
studentAdress
stdAid dist Aera。 .......儿子在
1 khamm busstand
2 RR lingampally
3 wrgl temple
i写了这样的qury
SELECT SR.stdRClass ,SD.stdDname,SD.stdDMarks,SA.dist,SA.Aer a
FROM studentregistration SR
INNER JOIN studentDetails SD ON SR.stdRid = SD.stdDid
INNER < span class =code-keyword> JOIN studentAdress SA ON SD.stdDid = SA.stdAid
其中 stdRid = ' 1' 和
(中的SR.stdRclass ( select distinct SR.StdRclass 来自 studentregistration 其中 stdRid = ' 1'))和
(SD。 stdDname in (seelct distinct SD.stdDname 来自 Studentdetails 其中 stdDid = ' 1'))和
(SD 中的.stdDmarks (selcet distinct SD.StdDMarks 来自 studentDetails 其中 stdDid = ' 1'))和
(SA.dist in (选择 distinct SA.dist 来自 StudentAddress 其中 stdAid = ' 1'))和
(SA.Aera in (选择 distinct SA.Aera 来自 studentAdress where stdAid = ' 1'))
group by SR.stdRClass,SD.stdDname,SD.stdDMarks,SA.dist,SA.Aera;
但是通过这样做所有重复即将来临并告诉我PLZ帮助我
请考虑下面这个桌子而不是那个sry我没有提到。谢谢adavance帮我PLZ
StdententDetails:
stdDid StdDname stdDmarks
1 xx 50
1 xx 30
1 xx 40
2 yy 31
2 yy 32
2 yy 43
3 zz 7 ..so on
studentAdress
StdAid dist Aera
1 kham busstand
1 khamm temple
1 kham医院
2 RR lingampally
2 RR chandanagar
3 wrgl temple
3 wrgl chorastha
i想要这样
o / p:注意;这只适用于stdid = 1
StdRclassA StdDname StdMarks dist Aera
A xx 50 kham busstand
A XX 30 kham temple >
一个xx 40公顷的医院
帮助我
但是它会是这样的
StdRclassA StdDname StdMarks dist Aera
A XX 50 kham Bustand
A XX 50 kham寺庙
A XX 50 kham医院
A XX 30 kham bustand
一个xx 30 kham寺庙
一个xx 30 kham医院
A XX 40 kham bustand
一个xx 40 kham寺庙
一个xx 40 kham医院
这样来帮助我
试试这个
SELECT SR.stdRClass,SD.stdDname,SD.stdDMarks,SA.dist,SA.Aera
FROM studentregistration SR
INNER JOIN studentDetails SD ON SR.stdRid = SD.stdDid
INNER JOIN studentAdress SA ON SD .stdDid = SA.stdAid
WHERE SR.stdRid = ' 1' 和 SD.stdDid = ' 1' 和 SA.stdAid = ' 1'
尝试在选择查询中使用关键字Distinct。 。 。
您好,
查看:
< pre lang =sql> 创建 table codepro(id int ,name varchar ( 20 ),标记 int )
创建 表 Codepro1(id int ,dist varchar ( 20 ),Area < span class =code-keyword> varchar ( 20 ))
将 插入 codepro 值( 1 ,' davud', 50 ),( 1 ,' davud', 30 ),( 1 ,' davud', 40 ),( 2 ,' davud', 50 ),( 2 ,' davud', 30 )
insert Codepro1 values ( 1 ,' nama',' selli'),( 1 ,' namakkal',' selliyayee'),( 1 ,' nama',' karai' ),( 2 ,' nama' ,' selli'),( 1 ,' nama',' selliyayee')
select * 来自 codepro
选择 * 来自 codepro1;
WITH CTE(id,marks,name,Rowid)
AS
(
SELECT id,marks,name,ROW_NUMBER() over ( partition by C.id ORDER BY C.id)' Rowid' FROM codepro C
),
CTE1(id,area,dist,Rowid1)
as
(
SELECT id,Area,dist,ROW_NUMBER() over ( partition by c1.id ORDER BY c1.id)' Rowid' FROM codepro1 c1
)
选择 C.id,C.marks,C.name,C1。 area,C1.dist 来自 CTE C INNER JOIN CTE1 C1 ON C.Rowid = C1.Rowid1 AND C.id = C1.ID order by C.id
hi i am new to sql sevre 2008 Actuvally i have 3 table from each table i want to display some feild like table
studentregistration:
stdRid stdRClass ... so on
1 A
2 B
3 C
4 A
5 B
studentDetails:
stdDid stdDname stdDMarks ..so on
1 xx 30
2 yy 50
3 zz 43
4 uu 21
5 ll 36
studentAdress
stdAid dist Aera........son on
1 khamm busstand
2 RR lingampally
3 wrgl temple
i have writen qury like this
SELECT SR.stdRClass , SD.stdDname , SD.stdDMarks , SA.dist, SA.Aera
FROM studentregistration SR
INNER JOIN studentDetails SD ON SR.stdRid = SD.stdDid
INNER JOIN studentAdress SA ON SD.stdDid = SA.stdAid
where stdRid='1' and
(SR.stdRclass in (select distinct SR.StdRclass from studentregistration where stdRid='1'))and
(SD.stdDname in (seelct distinct SD.stdDname from Studentdetails where stdDid='1'))and
(SD.stdDmarks in (selcet distinct SD.StdDMarks from studentDetails where stdDid='1'))and
(SA.dist in (select distinct SA.dist from StudentAddress where stdAid='1'))and
(SA.Aera in (select distinct SA.Aera from studentAdress where stdAid='1'))
group by SR.stdRClass ,SD.stdDname ,SD.stdDMarks ,SA.dist, SA.Aera;
but by doing like all dupication is coming plz ingore this and tell me plz help me
plz consider below this tables instead of that sry i was not mention . thanks adavance help me plz
StdententDetails:
stdDid StdDname stdDmarks
1 xx 50
1 xx 30
1 xx 40
2 yy 31
2 yy 32
2 yy 43
3 zz 7 ..so on
studentAdress
StdAid dist Aera
1 kham busstand
1 khamm temple
1 kham Hospital
2 RR lingampally
2 RR chandanagar
3 wrgl temple
3 wrgl chorastha
i want to like this
o/p:note;this is only for stdid=1
StdRclassA StdDname StdMarks dist Aera
A xx 50 kham busstand
A XX 30 kham temple
A xx 40 khamm Hospital
help me
but its coming like this
StdRclassA StdDname StdMarks dist Aera
A XX 50 kham Bustand
A XX 50 kham temple
A XX 50 kham Hospital
A XX 30 kham bustand
A xx 30 kham temple
A xx 30 kham hospital
A XX 40 kham bustand
A xx 40 kham temple
A xx 40 kham hospital
like this coming help me
Try this
SELECT SR.stdRClass ,SD.stdDname ,SD.stdDMarks ,SA.dist,SA.Aera FROM studentregistration SR INNER JOIN studentDetails SD ON SR.stdRid = SD.stdDid INNER JOIN studentAdress SA ON SD.stdDid = SA.stdAid WHERE SR.stdRid='1' and SD.stdDid ='1' and SA.stdAid ='1'
Try Using keyword Distinct in select query. . .
Hi,
Check this:
create table codepro(id int,name varchar(20),marks int) create table Codepro1(id int,dist varchar(20),Area varchar(20)) insert into codepro values(1,'davud',50),(1,'davud',30),(1,'davud',40),(2,'davud',50),(2,'davud',30) insert into Codepro1 values(1,'nama','selli'),(1,'namakkal','selliyayee'),(1,'nama','karai'),(2,'nama','selli'),(1,'nama','selliyayee') select * from codepro select * from codepro1; WITH CTE(id,marks,name,Rowid) AS ( SELECT id,marks,name,ROW_NUMBER()over(partition by C.id ORDER BY C.id)'Rowid' FROM codepro C ), CTE1(id,area,dist,Rowid1) as ( SELECT id,Area,dist,ROW_NUMBER()over(partition by c1.id ORDER BY c1.id)'Rowid' FROM codepro1 c1 ) select C.id,C.marks,C.name,C1.area,C1.dist from CTE C INNER JOIN CTE1 C1 ON C.Rowid=C1.Rowid1 AND C.id=C1.ID order by C.id
这篇关于如何在使用内连接时解决数据重复问题?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!