34,837
社区成员
发帖
与我相关
我的任务
分享两表连接问题（非常规，非常雷）
大家好，我又来问Sql了。
哈哈。先准备数据
问题来了。
现在要得到以下结果
条件：
1.Master中的记录一定比Detail的多
2.Master与Detail中No1和No2相同的可以放在同一行
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'
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' ,'',''
来试一下吧。
...全文
请发表友善的回复…
发表回复
DavidNoWay 2009-10-28
- 打赏
- 举报
采用poofly方法，谢谢大家。
dawugui 2009-10-27
- 打赏
- 举报
--sql 2000
--sql 2005用row_number() over(partition no1 order by material1)
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.px
DROP 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)
ProgrammerNO1 2009-10-27
- 打赏
- 举报
ms-sql讨论真激烈。。可惜DB都还给老师了，惭愧
XMLaihaozhe 2009-10-27
- 打赏
- 举报
来瞻仰大牛的了。
zhengzeng 2009-10-27
- 打赏
- 举报
路过
xiequan2 2009-10-27
- 打赏
- 举报
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 #Detail
select 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
--小F-- 2009-10-27
- 打赏
- 举报
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 #t1
DROP 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 行受影响)
*/
bancxc 2009-10-27
- 打赏
- 举报
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 行受影响)
*/
华夏小卒 2009-10-27
- 打赏
- 举报
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 t
select 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
lgxyz 2009-10-27
- 打赏
- 举报
出现ORDER BY 是不行了吧,你这样可能改变原有 表里的记录顺序了吧
bancxc 2009-10-27
- 打赏
- 举报
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 行受影响)
*/
SQL77 2009-10-27
- 打赏
- 举报
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 T
SELECT ID=(SELECT COUNT(*) FROM #Detail WHERE NO2=T.NO2 AND material2<=T.material2),
* INTO #T1 FROM #Detail T
SELECT 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 行）
feixianxxx 2009-10-27
- 打赏
- 举报
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
水族杰纶 2009-10-27
- 打赏
- 举报
看看
SQL77 2009-10-27
- 打赏
- 举报
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 T
SELECT ID=(SELECT COUNT(*) FROM #Detail WHERE NO2=T.NO2 AND material2<=T.material2),
* INTO #T1 FROM #Detail T
SELECT * FROM #T T LEFT JOIN #T1 T1 ON T.ID=T1.ID AND T.no1=T1.no2
ID 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' ,'','' feixianxxx 2009-10-27
- 打赏
- 举报
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 行受影响)
