日期:2014-05-18 浏览次数:20626 次
;with cte1 as (select rn=ROW_NUMBER() over(partition by fdate order by id),* from #t1) ,cte2 as (select rn=ROW_NUMBER() over(partition by fdate order by id),* from #t2) select isnull(a.fdate,b.fdate) fdate,a.id ,b.id as bid from cte1 a full join cte2 b on a.fdate=b.fdate and a.rn=b.rn /* 注意 fdate 一般情况下可能不相等 你用 convert(varchar(10),a.fdate,120)=convert(varchar(10),b.fdate,120) 还有partion by的地方也用convert(varchar(10),fdate,120) fdate id bid ----------------------- ----------- ----------- 2012-07-23 20:14:58.270 1 NULL 2012-07-23 20:14:58.283 2 NULL 2012-07-23 20:14:58.283 3 NULL 2012-07-23 20:14:58.283 4 NULL 2012-07-24 20:14:58.283 1 11 2012-07-24 20:14:58.283 2 12 2012-07-24 20:14:58.283 NULL 13 2012-07-24 20:14:58.283 NULL 14 2012-07-25 20:14:58.283 NULL 11 2012-07-25 20:14:58.300 NULL 12 */
------解决方案--------------------
select isnull(a.fdate,b.fdate) as fdate,a.id,b.id from ( select *,rn=(select count(1) from #t1 where fdate=t.fdate and id<=t.id) from #t1 t ) a full join ( select *,rn=(select count(1) from #t2 where fdate=t.fdate and id<=t.id) from #t2 t ) b on a.fdate=b.fdate and a.rn=b.rn order by fdate,isnull(a.id,b.id) /** fdate id id ----------------------- ----------- ----------- 2012-07-23 20:53:37.343 1 NULL 2012-07-23 20:53:37.343 2 NULL 2012-07-23 20:53:37.343 3 NULL 2012-07-23 20:53:37.343 4 NULL 2012-07-24 20:53:37.343 1 11 2012-07-24 20:53:37.343 2 12 2012-07-24 20:53:37.343 NULL 13 2012-07-24 20:53:37.343 NULL 14 2012-07-25 20:53:37.343 NULL 11 2012-07-25 20:53:37.343 NULL 12 (10 行受影响) **/