在论坛中出现的比较难的sql问题：31(row_number函数+子查询 月环比计算)

最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

1、同一表两条记录同一字段做比较的问题
一张sql表中的最新两条记录里的两个数字类型字段对比，最后一条比上一条的值大则输出上升，一样大输出持平 比上一条小则输出下降 这个数据查询怎么写？

if object_id('[tb]') is not null drop table [tb]go create table [tb]([Id] int,[Name] varchar(3),[Result] int,[Date] datetime)insert [tb]select 1,'001',90,'2013-11-10' union allselect 2,'002',85,'2013-11-10' union allselect 3,'003',87,'2013-11-10' union allselect 4,'001',95,'2013-11-15' union allselect 5,'002',83,'2013-11-15' union allselect 6,'003',89,'2013-11-15' union allselect 7,'001',92,'2013-11-20' union ALLselect 8,'002',83,'2013-11-20' union allselect 9,'003',88,'2013-11-20'go;with tas(select *,       ROW_NUMBER() over(partition by [Name] order by [Date] desc) rownumfrom tb)select t1.Name,t1.Result,       case when t1.[Result] > t2.[Result] then '上升'            when t1.[Result] = t2.[Result] then '持平'            when t1.[Result] < t2.[Result] then '下降'       end flagfrom t t1left join t t2       on t1.Name = t2.Name and t1.rownum = t2.rownum - 1          and t2.rownum = 2where t1.rownum = 1 /*NameResultflag00192下降00283持平00388下降*/

2、求sql，看似简单。

http://bbs.csdn.net/topics/390620423

No             FLAG  

1         Z         
2         Z         
3         Z         
4         L         
5         Z         
6         L         
7         L         
8         L         

SQL2000数据库，查询结果
FLAG-Z   FLAG-L
1          4
2          6
3          7
4          8

我的解法，最关键的是如何生成行号，分别对不同的数据产生行号，由于是2000数据库，稍微有点麻烦：

drop table tcreate table t(No int,FLAG varchar(10));insert into tselect 1        , 'Z' union all     select 2        , 'Z' union all         select 3        , 'Z' union allselect 4        , 'L' union all         select 5        , 'Z' union all         select 6        , 'L' union all         select 7        ,'L' union all         select 8        , 'L'select z.no,L.nofrom (select *,       (select count(*) from t t2 where t2.no <= t1.no and t2.flag = 'z') as rownumfrom t t1where flag = 'z')zinner join (select *,       (select count(*) from t t2 where t2.no <= t1.no and t2.flag = 'L') as rownumfrom t t1where flag = 'L')L on z.rownum = L.rownum/*nono14263758*/

如果是2005数据库，那么就简单多了：

drop table tcreate table t(No int,FLAG varchar(10));insert into tselect 1        , 'Z' union all     select 2        , 'Z' union all         select 3        , 'Z' union allselect 4        , 'L' union all         select 5        , 'Z' union all         select 6        , 'L' union all         select 7        ,'L' union all         select 8        , 'L'select t1.no,t2.nofrom (select *,       row_number() over(partition by flag order by no) as rownum from t)t1inner join (select *,       row_number() over(partition by flag order by no) as rownum from t)t2on t1.rownum = t2.rownum   and t1.flag = 'z'   and t2.flag = 'L'/*nono14263758*/