以下情况的sql 语句如何写

Question

CREATE TABLE #t1 (
fdate DATETIME,
id INT ) CREATE TABLE #t2 (
fdate DATETIME,
id INT ) INSERT INTO #t1 VALUES (GETDATE() -1 ,1) 
INSERT INTO #t1 VALUES (GETDATE() -1 ,2) 
INSERT INTO #t1 VALUES (GETDATE() -1 ,3) 
INSERT INTO #t1 VALUES (GETDATE() -1 ,4) 
INSERT INTO #t1 VALUES (GETDATE()  ,1) 
INSERT INTO #t1 VALUES (GETDATE()  ,2)INSERT INTO #t2 VALUES (GETDATE() ,11) 
INSERT INTO #t2 VALUES (GETDATE() ,12) 
INSERT INTO #t2 VALUES (GETDATE() ,13) 
INSERT INTO #t2 VALUES (GETDATE() ,14)
INSERT INTO #t2 VALUES (GETDATE() + 1 ,11) 
INSERT INTO #t2 VALUES (GETDATE() + 1 ,12) 
希望得到 
Fdate a.id b.id
2012-07-23 1 NULL
2012-07-23 2 NULL
2012-07-23 3 NULL
2012-07-23 4 NULL
2012-07-24  1 11
2012-07-24  2 12
2012-07-24 NUlL 13
2012-07-24 NULL 14
2012-07-25 NULL 11
2012-07-25 NULL 12在一天之中，t2有t1没有或者t1有t2没有的情况可以用 full join 加 distinct 查出来
但是 2012-07-24 的情况不知道如何查出 
2012-07-24  1 11
2012-07-24  2 12
2012-07-24 NUlL 13
2012-07-24 NULL 14
PS: a.id 与 b.id 之间是没有什么关系的 ，不可能存在 a.id + 10 = b.id 
还有可以在 sql server 与 oracle 通用 

Answer 1

;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 a.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
NULL                    NULL        13
NULL                    NULL        14
NULL                    NULL        11
NULL                    NULL        12
*/

Answer 2

;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
*/刚才少了点。

Answer 3

本帖最后由 josy 于 2012-07-24 20:59:54 编辑

  

Answer 4

如果考虑到用oracle 的rowid 或者sql server 2005 的 ROW_NUMBER() ，我也想过，也可以写出来，但是没有你这代码好，没有你代码短。

Answer 5

你这种方法非常好，我知道有 rowid 或者 row_number 之类的函数（不通用，版本问题)， 但没想到可以用自比的方法获得一个类似 rowid 的字段，“ where fdate=t.fdate and id<=t.id)” 给了我提示，我应该在实际中的情况，找一个满足这个条件的字段，或者尽管往这个条件靠近。
其实这个问题的实质就是根据日期与另一个字段找到 rowid 的，再用 full join 联接起来的。

Answer 6

--向树锅学习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)

Answer 7

--向树锅学习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)