select isnull(m.id,n.id),m.data,n.data from @m as m
full join @n as n on m.nrow=n.nrow
--------------------------------------------------------- 写错了,应当是:
select isnull(m.id,n.id),m.data,n.data from @m as m
full join @n as n on m.id=n.id and m.nrow=n.nrow
我解释给你:所要的目标结果不能仅仅用 m.id和n.id关联,同时还要关联他们在每一个id值分组中的行号。
full join @n as n on m.nrow=n.nrow
--------------------------------------------------------- 写错了,应当是:
select isnull(m.id,n.id),m.data,n.data from @m as m
full join @n as n on m.id=n.id and m.nrow=n.nrow
我解释给你:所要的目标结果不能仅仅用 m.id和n.id关联,同时还要关联他们在每一个id值分组中的行号。
declare @m table(row int identity primary key,nrow int,id varchar(20),data varchar(20))
---定义表变量@n 可以当作一个表用
declare @n table(row int identity primary key,nrow int,id varchar(20),data varchar(20))
--取出mtable 表中mid,mdate 插入到@m中去
insert @m(id,data) select mid,mdata from mTable
--修改@m 将@m中的nrow 设置为 row 减去该id下最小row(用了一个自连接)
update @m set nrow=row-g.row from @m as m
inner join (select id,min(row) as row from @m group by id) as g on m.id=t.id
--将ntable 的数据插入到@n中
insert @n(id,data) select nid,ndata from nTable
----修改@n 将@m中的nrow 设置为 row 减去该id下最小row(用了一个自连接)
update @n set nrow=row-g.row from @n as n
inner join (select id,min(row) as row from @n group by id) as g on n.id=t.id
--做一全连接,取出m与n 相等的行
select isnull(m.id,n.id),m.data,n.data from @m as m
full join @n as n on m.nrow=n.nrow
很欣赏你的认真精神!