分别有表1和表2,将表2的记录按layout_id向表1的记录匹配
如果表1中layout_id为1的记录共4条,表2中layout_id为1的记录共2条结果如下:表1:name layout_id
a 1
b 2
c 1
d 1
e 1
f 3表2:code layout_id
1001 1
1002 2
1003 2
1004 1
1005 3想得到结果name layout_id code
a 1 1001
b 2 1002
c 1 1004
d 1 null
e 1 null
f 3 1005求SQl
如果表1中layout_id为1的记录共4条,表2中layout_id为1的记录共2条结果如下:表1:name layout_id
a 1
b 2
c 1
d 1
e 1
f 3表2:code layout_id
1001 1
1002 2
1003 2
1004 1
1005 3想得到结果name layout_id code
a 1 1001
b 2 1002
c 1 1004
d 1 null
e 1 null
f 3 1005求SQl
insert @a select 'a', 1
union all select 'b', 2
union all select 'c', 1
union all select 'd', 1
union all select 'e', 1
union all select 'f', 3declare @b table(code int, layout_id int)
insert @b select 1001, 1
union all select 1002, 2
union all select 1003, 2
union all select 1004, 1
union all select 1005, 3select name,aa.layout_id,code from
(select top 100 percent id=(select count(1) from @a where layout_id=a.layout_id and name<=a.name),name,layout_id from @a a order by layout_id,name)aa
Left Join
(select top 100 percent id=(select count(1) from @b where layout_id=b.layout_id and code<=b.code),code,layout_id from @b b order by layout_id,code)bb
on aa.id=bb.id and aa.layout_id=bb.layout_id order by name
/*
name layout_id code
---------- ----------- -----------
a 1 1001
b 2 1002
c 1 1004
d 1 NULL
e 1 NULL
f 3 1005(所影响的行数为 6 行)
*/