最近,在论坛中,遇到了不少比较难的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 all
select 2,'002',85,'2013-11-10' union all
select 3,'003',87,'2013-11-10' union all
select 4,'001',95,'2013-11-15' union all
select 5,'002',83,'2013-11-15' union all
select 6,'003',89,'2013-11-15' union all
select 7,'001',92,'2013-11-20' union ALL
select 8,'002',83,'2013-11-20' union all
select 9,'003',88,'2013-11-20'
go
;with t
as
(
select *,
ROW_NUMBER() over(partition by [Name] order by [Date] desc) rownum
from 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 flag
from t t1
left join t t2
on t1.Name = t2.Name and t1.rownum = t2.rownum - 1
and t2.rownum = 2
where t1.rownum = 1
/*
Name Result flag
001 92 下降
002 83 持平
003 88 下降
*/
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
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数据库,稍微有点麻烦:
FLAG-Z FLAG-L
1 4
2 6
3 7
4 8
我的解法,最关键的是如何生成行号,分别对不同的数据产生行号,由于是2000数据库,稍微有点麻烦:
drop table t
create table t(No int,FLAG varchar(10));
insert into t
select 1 , 'Z' union all
select 2 , 'Z' union all
select 3 , 'Z' union all
select 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.no
from
(
select *,
(select count(*) from t t2 where t2.no <= t1.no and t2.flag = 'z') as rownum
from t t1
where flag = 'z'
)z
inner join
(
select *,
(select count(*) from t t2 where t2.no <= t1.no and t2.flag = 'L') as rownum
from t t1
where flag = 'L'
)L
on z.rownum = L.rownum
/*
no no
1 4
2 6
3 7
5 8
*/
如果是2005数据库,那么就简单多了:
drop table t
create table t(No int,FLAG varchar(10));
insert into t
select 1 , 'Z' union all
select 2 , 'Z' union all
select 3 , 'Z' union all
select 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.no
from
(
select *,
row_number() over(partition by flag order by no) as rownum
from t
)t1
inner join
(
select *,
row_number() over(partition by flag order by no) as rownum
from t
)t2
on t1.rownum = t2.rownum
and t1.flag = 'z'
and t2.flag = 'L'
/*
no no
1 4
2 6
3 7
5 8
*/