以下情况的sql 语句如何写
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)


希望得到
Fdatea.idb.id
2012-07-231NULL
2012-07-232NULL
2012-07-233NULL
2012-07-234NULL
2012-07-24 111
2012-07-24 212
2012-07-24NUlL13
2012-07-24NULL14
2012-07-25NULL11
2012-07-25NULL12

在一天之中，t2有t1没有或者t1有t2没有的情况可以用 full join 加 distinct 查出来
但是 2012-07-24 的情况不知道如何查出
2012-07-24 111
2012-07-24 212
2012-07-24NUlL13
2012-07-24NULL14
PS: a.id 与 b.id 之间是没有什么关系的 ，不可能存在 a.id + 10 = b.id
还有可以在 sql server 与 oracle 通用

[解决办法]

SQL code

;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           NULL2012-07-23 20:14:58.283 2           NULL2012-07-23 20:14:58.283 3           NULL2012-07-23 20:14:58.283 4           NULL2012-07-24 20:14:58.283 1           112012-07-24 20:14:58.283 2           122012-07-24 20:14:58.283 NULL        132012-07-24 20:14:58.283 NULL        142012-07-25 20:14:58.283 NULL        112012-07-25 20:14:58.300 NULL        12*/
[解决办法]
SQL code
select isnull(a.fdate,b.fdate) as fdate,a.id,b.idfrom(  select *,rn=(select count(1) from #t1 where fdate=t.fdate and id<=t.id)  from #t1 t) afull join(  select *,rn=(select count(1) from #t2 where fdate=t.fdate and id<=t.id)  from #t2 t) bon a.fdate=b.fdate and a.rn=b.rnorder by fdate,isnull(a.id,b.id)/**fdate                   id          id----------------------- ----------- -----------2012-07-23 20:53:37.343 1           NULL2012-07-23 20:53:37.343 2           NULL2012-07-23 20:53:37.343 3           NULL2012-07-23 20:53:37.343 4           NULL2012-07-24 20:53:37.343 1           112012-07-24 20:53:37.343 2           122012-07-24 20:53:37.343 NULL        132012-07-24 20:53:37.343 NULL        142012-07-25 20:53:37.343 NULL        112012-07-25 20:53:37.343 NULL        12(10 行受影响)**/