两表连接问题（非常规，非常雷）

Question

大家好，我又来问Sql了。
哈哈。先准备数据
CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO  #Master
SELECT '090901','A1'  UNION 
SELECT '090901','A2'  UNION 
SELECT '090901','A3'  UNION 
SELECT '090902','B1'  UNION 
SELECT '090902','B2'  UNION 
SELECT '090902','B3'    
INSERT INTO #Detail   
SELECT '090901','C1'  UNION 
SELECT '090901','C2'  UNION 
SELECT '090902','C3'    DROP TABLE          #Master   
DROP TABLE          #Detail   问题来了。
现在要得到以下结果SELECT '090901','A1' ,'090901','C1' UNION 
SELECT '090901','A2' ,'090901','C2' UNION 
SELECT '090901','A3' ,'','' UNION 
SELECT '090902','B1' ,'090902','C1' UNION 
SELECT '090902','B2' ,'','' UNION 
SELECT '090902','B3' ,'',''   条件：
1.Master中的记录一定比Detail的多
2.Master与Detail中No1和No2相同的可以放在同一行
3.排序无关系，这样的结果也是正确的SELECT '090901','A1' ,'090901','C1' UNION 
SELECT '090901','A2' ,'','' UNION 
SELECT '090901','A3' ,'090901','C2' UNION 
SELECT '090902','B1' ,'','' UNION 
SELECT '090902','B2' ,'090902','C1' UNION 
SELECT '090902','B3' ,'',''   来试一下吧。

Answer 1

CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO  #Master
SELECT '090901','A1'  UNION 
SELECT '090901','A2'  UNION 
SELECT '090901','A3'  UNION 
SELECT '090902','B1'  UNION 
SELECT '090902','B2'  UNION 
SELECT '090902','B3'    
INSERT INTO #Detail   
SELECT '090901','C1'  UNION 
SELECT '090901','C2'  UNION 
SELECT '090902','C3'    
select a.no1,a.material1,isnull(b.no2,''),isnull(b.material2,'')
from (select ROW_NUMBER() over(partition by no1 order by material1 ) as rn,* from #Master)  a 
left join (select ROW_NUMBER() over(partition by no2 order by material2 ) as rn,* from #Detail ) b 
on a.rn=b.rn 
and  a.no1=b.no2
no1                  material1                                 
-------------------- -------------------- -------------------- --------------------
090901               A1                   090901               C1
090901               A2                   090901               C2
090901               A3                                        
090902               B1                   090902               C3
090902               B2                                        
090902               B3                                        (6 行受影响)

Answer 2

CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO  #Master
SELECT '090901','A1'  UNION 
SELECT '090901','A2'  UNION 
SELECT '090901','A3'  UNION 
SELECT '090902','B1'  UNION 
SELECT '090902','B2'  UNION 
SELECT '090902','B3'    
INSERT INTO #Detail   
SELECT '090901','C1'  UNION 
SELECT '090901','C2'  UNION 
SELECT '090902','C3'    SELECT ID=(SELECT COUNT(*) FROM #Master WHERE NO1=T.NO1 AND material1<=T.material1),
* INTO #T FROM #Master TSELECT ID=(SELECT COUNT(*) FROM #Detail WHERE NO2=T.NO2 AND material2<=T.material2),
* INTO #T1 FROM #Detail TSELECT * FROM #T T LEFT JOIN #T1 T1 ON  T.ID=T1.ID AND T.no1=T1.no2ID          no1                  material1            ID          no2                  material2            
----------- -------------------- -------------------- ----------- -------------------- -------------------- 
1           090901               A1                   1           090901               C1
2           090901               A2                   2           090901               C2
3           090901               A3                   NULL        NULL                 NULL
1           090902               B1                   1           090902               C3
2           090902               B2                   NULL        NULL                 NULL
3           090902               B3                   NULL        NULL                 NULL（所影响的行数为 6 行）--DROP TABLE #t,#t1
SELECT '090901','A1' ,'090901','C1' UNION 
SELECT '090901','A2' ,'090901','C2' UNION 
SELECT '090901','A3' ,'','' UNION 
SELECT '090902','B1' ,'090902','C1' UNION 
SELECT '090902','B2' ,'','' UNION 
SELECT '090902','B3' ,'','' 

Answer 3

CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO  #Master
SELECT '090901','A1'  UNION 
SELECT '090901','A2'  UNION 
SELECT '090901','A3'  UNION 
SELECT '090902','B1'  UNION 
SELECT '090902','B2'  UNION 
SELECT '090902','B3'    
INSERT INTO #Detail   
SELECT '090901','C1'  UNION 
SELECT '090901','C2'  UNION 
SELECT '090902','C3'    
select a.no1,a.material1,isnull(b.no2,''),isnull(b.material2,'')
from (select ROW_NUMBER() over(partition by no1 order by material1 ) as rn,* from #Master)  a 
left join (select ROW_NUMBER() over(partition by no2 order by material2 ) as rn,* from #Detail ) b 
on a.rn=b.rn 
and  a.no1=b.no2
no1                  material1                                 
-------------------- -------------------- -------------------- --------------------
090901               A1                   090901               C1
090901               A2                   090901               C2
090901               A3                                        
090902               B1                   090902               C3
090902               B2                                        
090902               B3                                        (6 行受影响)
你的一条记录有点错 应该是 
090902               B1                   090902               C3

Answer 4

CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO  #Master
SELECT '090901','A1'  UNION 
SELECT '090901','A2'  UNION 
SELECT '090901','A3'  UNION 
SELECT '090902','B1'  UNION 
SELECT '090902','B2'  UNION 
SELECT '090902','B3'    
INSERT INTO #Detail   
SELECT '090901','C1'  UNION 
SELECT '090901','C2'  UNION 
SELECT '090902','C3'    SELECT ID=(SELECT COUNT(*) FROM #Master WHERE NO1=T.NO1 AND material1<=T.material1),
* INTO #T FROM #Master TSELECT ID=(SELECT COUNT(*) FROM #Detail WHERE NO2=T.NO2 AND material2<=T.material2),
* INTO #T1 FROM #Detail TSELECT no1,material1, 
isnull(ltrim(no2),'')no2 ,isnull(ltrim(material2),'')material2  
FROM #T T LEFT JOIN #T1 T1 ON  T.ID=T1.ID AND T.no1=T1.no2
--DROP TABLE #t,#t1
no1                  material1            no2                  material2            
-------------------- -------------------- -------------------- -------------------- 
090901               A1                   090901               C1
090901               A2                   090901               C2
090901               A3                                        
090902               B1                   090902               C3
090902               B2                                        
090902               B3                                        （所影响的行数为 6 行）

Answer 5

select No1,M1,M2
from 
(select ROW_NUMBER() Over(Partition by no1 order by no1) id,No1,material1 M1
from #Master) A 
left join 
(select ROW_NUMBER() Over(order by no2) id,No2,material2 M2
from #Detail) B on A.id=B.id and A.No1=B.No2/*No1                  M1                   M2
-------------------- -------------------- --------------------
090901               A1                   C1
090901               A2                   C2
090901               A3                   NULL
090902               B1                   NULL
090902               B2                   NULL
090902               B3                   C3(6 行受影响)*/

Answer 6

出现ORDER BY 是不行了吧,你这样可能改变原有 表里的记录顺序了吧

Answer 7

CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO  #Master
SELECT '090901','A1'  UNION 
SELECT '090901','A2'  UNION 
SELECT '090901','A3'  UNION 
SELECT '090902','B1'  UNION 
SELECT '090902','B2'  UNION 
SELECT '090902','B3'    
INSERT INTO #Detail   
SELECT '090901','C1'  UNION 
SELECT '090901','C2'  UNION 
SELECT '090902','C3'    
select *,id=(select count(*) from #Master where no1=t.no1 and material1<=t.material1) into #t1 from #Master t
select *,id=(select count(*) from #Detail where no2=t.no2 and material2<=t.material2) into #t2 from #Detail tselect no1,material1,no2=isnull(no2,''), material2=isnull(material2,'')
from #t1 a
left join #t2 b
on a.id=b.id and no1=no2 
 
no1                  material1            no2                  material2
-------------------- -------------------- -------------------- --------------------
090901               A1                   090901               C1
090901               A2                   090901               C2
090901               A3                                        
090902               B1                   090902               C3
090902               B2                                        
090902               B3                                        (6 行受影响)DROP TABLE          #Master   
DROP TABLE          #Detail   
DROP TABLE          #t1   
DROP TABLE          #t2  

Answer 8

select No1,M1,M2
from 
(select ROW_NUMBER() Over(Partition by no1 order by no1) id,No1,material1 M1
from #Master) A 
left join 
(select ROW_NUMBER() Over(Partition by no2 order by no2) id,No2,material2 M2
from #Detail) B on A.id=B.id and A.No1=B.No2/*No1                  M1                   M2
-------------------- -------------------- --------------------
090901               A1                   C1
090901               A2                   C2
090901               A3                   NULL
090902               B1                   C3
090902               B2                   NULL
090902               B3                   NULL(6 行受影响)*/

Answer 9

CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO  #Master
SELECT '090901','A1'  UNION 
SELECT '090901','A2'  UNION 
SELECT '090901','A3'  UNION 
SELECT '090902','B1'  UNION 
SELECT '090902','B2'  UNION 
SELECT '090902','B3'    
INSERT INTO #Detail   
SELECT '090901','C1'  UNION 
SELECT '090901','C2'  UNION 
SELECT '090902','C3'  
select
   (select count(1) from #Master where NO1=T.NO1 and material1<=T.material1) as id,* 
into 
   #T 
FROM 
    #Master t
select 
  (select count(1) from#Detail WHERE NO2=T.NO2 and material2<=T.material2) as id,* 
into 
  #T1 
from 
  #Detail t
select 
  no1,material1,isnull(no2,'') as no2, isnull(material2,'') as material2 
from 
  #t a 
left join #t1 b on  
  a.ID=b.ID and a.no1=b.no2
  
drop table #t
drop table #t1DROP TABLE    #Master   
DROP TABLE    #Detail /*no1                  material1            no2                  material2
-------------------- -------------------- -------------------- --------------------
090901               A1                   090901               C1
090901               A1                   090901               C2
090901               A2                                        
090901               A3                                        
090902               B1                   090902               C3
090902               B2                                        
090902               B3                                        (7 行受影响)*/

Answer 10

CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO  #Master
SELECT '090901','A1'  UNION 
SELECT '090901','A2'  UNION 
SELECT '090901','A3'  UNION 
SELECT '090902','B1'  UNION 
SELECT '090902','B2'  UNION 
SELECT '090902','B3'    
INSERT INTO #Detail   
SELECT '090901','C1'  UNION 
SELECT '090901','C2'  UNION 
SELECT '090902','C3'    --select * from #Master
--select * from #Detailselect no1,material1,no2,material2 from
(
select flag=row_number() over(partition by no1 order by no1), * from  #Master
) m1  left join(
select flag=row_number() over(partition by no2 order by no2),* from #Detail
)m2
on no1=no2 and  m1.flag=m2.flag/*
no1                  material1            no2                  material2
-------------------- -------------------- -------------------- --------------------
090901               A1                   090901               C1
090901               A2                   090901               C2
090901               A3                   NULL                 NULL
090902               B1                   090902               C3
090902               B2                   NULL                 NULL
090902               B3                   NULL                 NULL
*/
DROP TABLE          #Master   
DROP TABLE          #Detail    

Answer 11

ms-sql讨论真激烈。。可惜DB都还给老师了，惭愧

Answer 12

--sql 2000
CREATE TABLE #Master
(no1 VARCHAR(20),material1 NVARCHAR(20) )
CREATE TABLE #Detail
(no2 VARCHAR(20),material2 NVARCHAR(20) )
INSERT INTO #Master
SELECT '090901','A1' UNION 
SELECT '090901','A2' UNION 
SELECT '090901','A3' UNION 
SELECT '090902','B1' UNION 
SELECT '090902','B2' UNION 
SELECT '090902','B3' 
INSERT INTO #Detail 
SELECT '090901','C1' UNION 
SELECT '090901','C2' UNION 
SELECT '090902','C3' select m.no1 , m.material1 , isnull(n.material2,'') material2 from
(select * , px = (select count(1) from #Master where no1 = t.no1 and material1 < t.material1) + 1 from #Master t) m
left join
(select * , px = (select count(1) from #Detail where no2 = t.no2 and material2 < t.material2) + 1 from #Detail t) n
on m.no1 = n.no2 and m.px = n.pxDROP TABLE #Master 
DROP TABLE #Detail 
/*
no1                  material1            material2            
-------------------- -------------------- -------------------- 
090901               A1                   C1
090901               A2                   C2
090901               A3                   
090902               B1                   C3
090902               B2                   
090902               B3                   （所影响的行数为 6 行）
*/
--sql 2005用row_number() over(partition no1 order by material1)