SQL表加入问题 [英] SQL table joining issue

问题描述

我有两个表，我在SQL中尝试了内连接，但它没有显示四行，而是显示八行。

请检查表格和所需的输出如下。

  SELECT  
 LocID，
 ScheduledCompletion 
  FROM  table1 
  GROUP  < span class =code-keyword> BY  
 LocID，
 ScheduledCompletion 
 
  SELECT  
 LocID1，
 ScheduledCompletion1 
  FROM  table2 
  GROUP   BY  
 LocID1，
 ScheduledCompletion1 
 

 LocID ScheduledCompletion 
 100 14/01/2013 16:30 
 100 29/01/2013 16:30 
 100 30/01/2013 16:30 
 100 14/02/2013 16:30 
 
 
 
 LocID1 ScheduledCompletion1 
 100 07/12/2012 20:30 
 100 17/01/2013 20:04 
 100 29/01/2013 23:27 
 100 31/01/2013 20:26 

所需输出

 LocID ScheduledCompletion LocID1 ScheduledCompletion1 
 100 14/01/2013 16:30 100 07/12/2012 20： 30 
 100 29/01/2013 16:30 100 17/01/2013 20:04 
 100 30/01/2013 16:30 100 29/01/2013 23:27 
 100 14/02/2013 16:30 100 31/01/2013 20:26 

非常感谢任何帮助。在此先感谢。

解决方案

你可以这样做内连接

  SELECT  
 LocID，ScheduledCompletion，LocID1，ScheduledCompletion1 
  FROM  table1 t1 < span class =code-keyword> inner   join  table2 t2  on  t1.LocID = t2.LocID1 

但上面的查询将返回16行，因为

table1包含4行LocID为100和

table2包含4行，其中LocID1为100



因此它将执行 4 * 4 操作和返回16行

------------------ -------------------------------------------------- ----------------------------------------------

要获得你需要的结果，可以尝试这样的结果..







<预lang =sql> 声明 @ table1 table （LocID int ，ScheduledCompletion varchar （ 33 ））
声明 @ table2 table （LocID1 int ，ScheduledCompletion1 varchar （ 33 ））

插入 进入 @ table1 （LocID，ScheduledCompletion）值（ 100 ，' aa'）
insert into @ table1 （LocID，ScheduledCompletion）值（ 100 ，' bb'）
insert into @ table1 （LocID，ScheduledCompletion） values （ 100 ，' cc'）
< span class =code-keyword> insert into @ table1 （LocID，ScheduledCompletion） 值（ 100 ，' dd'）

insert into @ table2 （LocID1，ScheduledCompletion1）值（ 100 ，' ee'）
insert into @ table2 （LocID1，ScheduledCompletion1）值（ 100 ，< span class =code-string>' ff'）
insert into @ table2 （LocID1，ScheduledCompletion1） values （ 100 ，' gg'）
插入 进入 @ table2 （LocID1，ScheduledCompletion1）值（ 100 ，' hh'）




; T1（RowNum，LocID，ScheduledCompletion） as （
选择 row_number（） over （ order by LocID）RowNum，LocID，ScheduledCompletion
来自 @ table1
），
T2（RowNum，LocID1，ScheduledCompletion1） as （
< span class =code-keyword> select row_number（） over （ order by LocID1）RowNum，LocID1，ScheduledCompletion1
来自 @ table2
）
选择 a.LocID，a.ScheduledCompletion，b.LocID1，b.ScheduledCompletion1
来自 T1 a
inner join T2 b on a.RowNum = b .RowNum

你好，

我可以看到两个结果之间没有任何关系。但我们可以通过以下代码带来欲望输出。你需要向表声明运行时间，从中你可以获得欲望结果。在这里他是解决方案。

 声明  @ tab1   table （ID  int ，LocID  int ，ScheduledCompletion  varchar （ 100 ））
 插入 进入  @ tab1  
 从中选择 * （选择 ROW_NUMBER（）  over （ order   by  locID） as  ID，locid，ScheduledCompletion 来自 a1） as  tab1 
 
 声明  @ tab2  表（ID  int ，LocID1  int ，ScheduledCompletion1  varchar （ 100 ））
 插入 进入  @ tab2  
 从选择 * （选择 ROW_NUMBER（） over （ order   by  locID1） as  ID，locID1，ScheduledCompletion1 来自 a2） as  tab2 
 
 选择 LocID，ScheduledCompletion，ScheduledCompletion1 来自  @ tab1  t1 完整 外部  join   @ tab2  t2  on  t1.ID = t2.ID 

GOOD LUCK ..

Hi,
I have two tables and I have tried inner joins in SQL, but instead of showing four lines, it shows eight lines.
please check the tables and desired output as below.

SELECT
LocID,
     ScheduledCompletion
FROM  table1
GROUP BY
      LocID,
      ScheduledCompletion

 SELECT
LocID1,
     ScheduledCompletion1
 FROM  table2
 GROUP BY
       LocID1,
       ScheduledCompletion1

LocID   ScheduledCompletion
100 14/01/2013 16:30
100 29/01/2013 16:30
100 30/01/2013 16:30
100 14/02/2013 16:30



LocID1  ScheduledCompletion1
100 07/12/2012 20:30
100 17/01/2013 20:04
100 29/01/2013 23:27
100 31/01/2013 20:26

Desired output

LocID	ScheduledCompletion	LocID1	ScheduledCompletion1
100	14/01/2013 16:30	           100	07/12/2012 20:30
100	29/01/2013 16:30	           100    17/01/2013 20:04
100	30/01/2013 16:30	           100	29/01/2013 23:27
100	14/02/2013 16:30	           100	31/01/2013 20:26

Would really appreciate any help. Thanks in advance.

解决方案

You can do inner join like this

SELECT
    LocID,   ScheduledCompletion,LocID1,  ScheduledCompletion1
    FROM  table1 t1  inner join  table2   t2 on t1.LocID = t2.LocID1

But the above query will return 16 Rows, since the
table1 contains 4 rows with LocID as 100 and
table2 contains 4 rows with LocID1 as 100

so it will Perform 4*4 operation and returns 16 Rows
------------------------------------------------------------------------------------------------------------------
TO get the result which u need you can try like this..

declare @table1 table ( LocID int , ScheduledCompletion varchar(33))
declare @table2 table ( LocID1 int , ScheduledCompletion1 varchar(33))

insert into @table1 ( LocID, ScheduledCompletion) values ( 100 , 'aa')
insert into @table1 ( LocID, ScheduledCompletion) values ( 100 , 'bb')
insert into @table1 ( LocID, ScheduledCompletion) values ( 100 , 'cc')
insert into @table1 ( LocID, ScheduledCompletion) values ( 100 , 'dd')

insert into @table2 ( LocID1, ScheduledCompletion1) values ( 100 , 'ee')
insert into @table2 ( LocID1, ScheduledCompletion1) values ( 100 , 'ff')
insert into @table2 ( LocID1, ScheduledCompletion1) values ( 100 , 'gg')
insert into @table2 ( LocID1, ScheduledCompletion1) values ( 100 , 'hh')




      ;with T1 (RowNum,LocID,ScheduledCompletion) as (
    select row_number() over (order by LocID) RowNum, LocID , ScheduledCompletion
    from @table1
),
T2 (RowNum,LocID1,ScheduledCompletion1)as (
    select row_number() over (order by LocID1) RowNum, LocID1 , ScheduledCompletion1
    from @table2
)
select a.LocID, a.ScheduledCompletion , b.LocID1 , b.ScheduledCompletion1
from T1 a
   inner join T2  b on a.RowNum = b.RowNum

hello,
As much as I can see there is no relation between both result. but we can bring desire output by the following code. you need to declare to table for run time and from them you can take desire result. here he the solution for it.

Declare @tab1 table (ID int, LocID int , ScheduledCompletion varchar(100))
insert into @tab1
Select * from (select ROW_NUMBER() over(order by locID) as ID, locid, ScheduledCompletion from a1) as tab1

Declare @tab2 table (ID int, LocID1 int , ScheduledCompletion1 varchar(100))
insert into @tab2
Select * from (select ROW_NUMBER() over(order by locID1) as ID, locID1, ScheduledCompletion1 from a2) as tab2

select LocID, ScheduledCompletion, ScheduledCompletion1 from @tab1 t1 full outer join @tab2 t2 on t1.ID = t2.ID

GOOD LUCK..

这篇关于SQL表加入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！