使用右联接无效的结果 [英] Invalid results using right join

问题描述

表用户= [用户名，名字，姓氏，资格，reg_no];
表注册= [Reg_Record_No，user_id，reg_no]与表User
多对多
用户数据:

Table User =[ user_id, firstname, lastname, qualification, reg_no ];
Table Registration = [Reg_Record_No, user_id, reg_no] many to many with table User
​
User Data:

user_id, firstname, qualification, reg_no
1,smith,Msc,A-3146
1,smith,Bsc,A-3146
1,smith,Fsc,A-3146
1,smith,SSc,A-3146

注册数据:

Registration Data:

Reg_Record_No, user_id, reg_no
700,1,A-3146
701,1,A-3146
702,1,A-3146
703,1,A-3146
704,1,A-3146

应该是这样的:

it should be like this:

Select user_id, firstname,qualification,reg_no, Reg_Record_No

1,smith,Msc,A-3146,700
1,smith,Bsc,A-3146,701
1,smith,Fsc,A-3146,702
1,smith,SSc,A-3146,703
NULL,NULL,NULL,NULL,704​

我尝试过的事情:

What I have tried:

Select  u.user_id, u.firstname,u.qualification,u.reg_no, r.Reg_Record_No
FROM User as u
RIGHT JOIN Registration as r
ON r.reg_no=u.reg_no

它产生如下结果:

it produces result like:

1,smith,Msc,A-3146,700
1,smith,Msc,A-3146,701
1,smith,Msc,A-3146,702
1,smith,Msc,A-3146,703
1,smith,Msc,A-3146,704
1,smith,Bsc,A-3146,700
1,smith,Bsc,A-3146,701
1,smith,Bsc,A-3146,702
1,smith,Bsc,A-3146,703
1,smith,Bsc,A-3146,704
........

推荐答案

首先，您在这些表之间的关联非常弱.这些表之间的当前关系永远不会产生这样的结果.但是，如果您确定这些表之间具有正确的关系，则可以通过技巧获得这种结果.

试试这个-

First of all you have very weak relation between these table. The current relation between these table can never yield such result. However if you are sure about that you have correct relationship between these tables then you may do a trick to get such result.

Try this-

SELECT u.[user_id], u.[firstname], u.[qualification], u.[reg_no],r.[Reg_Record_No]
FROM
(
  SELECT [user_id], [firstname], [qualification], [reg_no],ROW_NUMBER() OVER(ORDER BY [user_id], [firstname], [qualification], [reg_no]) AS RowNum
  FROM [User]
) AS u
RIGHT JOIN 
(
  SELECT [Reg_Record_No], [user_id], [reg_no], ROW_NUMBER() OVER(ORDER BY [Reg_Record_No], [user_id], [reg_no]) AS RowNum
  FROM [Registration]
) AS r
ON r.[reg_no]=u.[reg_no] AND r.RowNum=u.RowNum

希望对您有帮助:)

更新:
为了进行测试，您可以检查使用表变量创建的以下脚本.

Hope, it helps :)

UPDATE:
For test you can check following script created using table variable.

declare @User as Table ([user_id] int, [firstname] varchar(50),[qualification] varchar(50),[reg_no] varchar(50))

insert into @User
select * from 
(
select 1 a,'smith' b,'Msc' c,'A-3146' d
union all
select 1,'smith','Bsc','A-3146'
union all
select 1,'smith','Fsc','A-3146'
union all
select 1,'smith','SSc','A-3146'
) as t

declare @Registration as table([Reg_Record_No] int,[user_id] int, [reg_no] varchar(50))

insert into @Registration
select * from 
(
select 700 a,1 b,'A-3146' c
union all
select 701,1,'A-3146'
union all
select 702,1,'A-3146'
union all
select 703,1,'A-3146'
union all
select 704,1,'A-3146'
) as t

SELECT u.[user_id], u.[firstname], u.[qualification], u.[reg_no],r.[Reg_Record_No]
FROM
(
  SELECT [user_id], [firstname], [qualification], [reg_no],ROW_NUMBER() OVER(ORDER BY [user_id], [firstname], [qualification], [reg_no]) AS RowNum
  FROM @User
) AS u
RIGHT JOIN 
(
  SELECT [Reg_Record_No], [user_id], [reg_no], ROW_NUMBER() OVER(ORDER BY [Reg_Record_No], [user_id], [reg_no]) AS RowNum
  FROM @Registration
) AS r
ON r.[reg_no]=u.[reg_no] AND r.RowNum=u.RowNum

似乎，您必须回到基础知识:

Seems, you have to back to basics: Visual Representation of SQL Joins[^]

This statement: FROM User as u RIGHT JOIN Registration as rs means: get all records from Registration table and join only those records which correspond to the table on the left.
Because user_id (1) exists in both tables, you''ll never reach result as expected.

这篇关于使用右联接无效的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！