2表联合查询的问题

[郁闷龙]

对比2天排名在100万内上升网站的问题
有a,b两天的网站排名数据表
都有2个字段:domain(文本型,存储网站名),rank(整数型,存储当天网站排名)
要查询出第2天排名上升10000名的网站,如果前一天没有排名的网站,可默认为排名为100万,谢谢啦.

[Tiger_Zhao]

/* 测试数据
WITH a(domain,rank)AS(
    SELECT 'A',30000 UNION ALL
    SELECT 'B',20000 
)
,b(domain,rank)AS(
    SELECT 'A',18000 UNION ALL
    SELECT 'B',15000 UNION ALL
    SELECT 'C',990000 UNION ALL
    SELECT 'D',990001
)*/
    SELECT b.domain,
           a.rank prev_rank,
           b.rank this_rank,
           ISNULL(a.rank,1000000)-b.rank upgrade
      FROM b
 LEFT JOIN a
        ON b.domain = a.domain
     WHERE ISNULL(a.rank,1000000)-b.rank >= 10000

domain   prev_rank   this_rank     upgrade
------ ----------- ----------- -----------
A            30000       18000       12000
C             NULL      990000       10000

[Landa_Tiny_cui]

CREATE TABLE l1
(
	domain VARCHAR(500),
	[RANK]  INT
)
INSERT INTO l1
VALUES ('a',15000),('b',17153),('c',18153),('d',200)

GO

CREATE TABLE l2
(
	domain VARCHAR(500),
	[RANK]  INT
)
INSERT INTO l2
VALUES ('a',5000),('b',7153),('c',18),('d',350),('e',200000)
GO
SELECT l2.domain,l2.[rank]
FROM l2 left JOIN l1 ON l1.domain = l2.domain
WHERE isnull(l1.[RANK],1000000)-l2.[RANK]>=10000