比如 id date A 1 2010-12-13 1000 1 2010-12-14 2000 1 2010-12-11 2000求等于id 相同的 最新的 两个 求A 的 差值 出来结果 应该是 1 , 1000
加入这张表名为table select ( select t.a from( select row_number() over(order by date desc) as num,a from table ) t where t.num =1 - select t.a from( select row_number() over(order by date desc) as num,a from table ) t where t.num =2 )
--sql 2000 select m.id , m.a - n.a from (select t.* , px = (select count(1) from tb where id = t.id and date > t.date) + 1 from tb t) m, (select t.* , px = (select count(1) from tb where id = t.id and date > t.date) + 1 from tb t) n where m.id = n.id and m.px = 1 and n.px = 2--sql 2005 select m.id , m.a - n.a from (select t.* , px = row_number() over(partition by id order by date desc) from tb t) m, (select t.* , px = row_number() over(partition by id order by date desc) from tb t) n where m.id = n.id and m.px = 1 and n.px = 2
--sql 2000 create table tb(id int,date datetime,A int) insert into tb values(1 ,'2010-12-13', 1000) insert into tb values(1 ,'2010-12-14', 2000) insert into tb values(1 ,'2010-12-11', 2000) go--sql 2000 select m.id , m.a - n.a from (select t.* , px = (select count(1) from tb where id = t.id and date > t.date) + 1 from tb t) m, (select t.* , px = (select count(1) from tb where id = t.id and date > t.date) + 1 from tb t) n where m.id = n.id and m.px = 1 and n.px = 2drop table tb/* id ----------- ----------- 1 1000(所影响的行数为 1 行)*/ --sql 2005 create table tb(id int,date datetime,A int) insert into tb values(1 ,'2010-12-13', 1000) insert into tb values(1 ,'2010-12-14', 2000) insert into tb values(1 ,'2010-12-11', 2000) go--sql 2005 select m.id , m.a - n.a from (select t.* , px = row_number() over(partition by id order by date desc) from tb t) m, (select t.* , px = row_number() over(partition by id order by date desc) from tb t) n where m.id = n.id and m.px = 1 and n.px = 2drop table tb/* id ----------- ----------- 1 1000(1 行受影响)*/
create table TTb ( id int, date datetime, A int ) insert into TTb values(1,'2010-12-13',1000) insert into TTb values(1,'2010-12-14',2000) insert into TTb values(1,'2010-12-11',3000) insert into TTb values(2,'2010-11-13',3000) insert into TTb values(2,'2010-11-14',5000) insert into TTb values(2,'2010-11-11',7000) insert into TTb values(3,'2010-11-11',7000) insert into TTb values(3,'2010-11-12',7000) insert into TTb values(4,'2010-11-12',4000) create table #TTb ( num int, id int, A int )insert into #TTb select num,id, A from ( select *, (select count(*) from ttb where id = t.id and date > t.date)+1 as num from ttb t ) tt select a.id ,a.A-isnull(b.A,0) as A from (select * from #TTb where num=1) a left join (select * from #TTb where num=2) b on a.id=b.id
--新建数据表 CREATE TABLE #tt (id INT,date DATETIME,A INT) INSERT INTO #tt SELECT '1','2010-12-22 16:41:44.653','12' UNION ALL SELECT '1','2010-12-21 15:41:44.653','52' UNION ALL SELECT '1','2010-12-25 13:41:44.653','52' GO--查询数据表 SELECT * FROM #tt --所满足的查询语句 select m.id , m.a - n.a from (select t.* , px = row_number() over(partition by id order by date desc) from #tt t) m, (select t.* , px = row_number() over(partition by id order by date desc) from #tt t) n WHERE m.px = 1 and n.px = 2
id date A
1 2010-12-13 1000
1 2010-12-14 2000
1 2010-12-11 2000求等于id 相同的 最新的 两个 求A 的 差值
出来结果 应该是 1 , 1000
select (
select t.a from(
select row_number() over(order by date desc) as num,a
from table ) t
where t.num =1
-
select t.a from(
select row_number() over(order by date desc) as num,a
from table ) t
where t.num =2
)
select m.id , m.a - n.a from
(select t.* , px = (select count(1) from tb where id = t.id and date > t.date) + 1 from tb t) m,
(select t.* , px = (select count(1) from tb where id = t.id and date > t.date) + 1 from tb t) n
where m.id = n.id and m.px = 1 and n.px = 2--sql 2005
select m.id , m.a - n.a from
(select t.* , px = row_number() over(partition by id order by date desc) from tb t) m,
(select t.* , px = row_number() over(partition by id order by date desc) from tb t) n
where m.id = n.id and m.px = 1 and n.px = 2
create table tb(id int,date datetime,A int)
insert into tb values(1 ,'2010-12-13', 1000)
insert into tb values(1 ,'2010-12-14', 2000)
insert into tb values(1 ,'2010-12-11', 2000)
go--sql 2000
select m.id , m.a - n.a from
(select t.* , px = (select count(1) from tb where id = t.id and date > t.date) + 1 from tb t) m,
(select t.* , px = (select count(1) from tb where id = t.id and date > t.date) + 1 from tb t) n
where m.id = n.id and m.px = 1 and n.px = 2drop table tb/*
id
----------- -----------
1 1000(所影响的行数为 1 行)*/
--sql 2005
create table tb(id int,date datetime,A int)
insert into tb values(1 ,'2010-12-13', 1000)
insert into tb values(1 ,'2010-12-14', 2000)
insert into tb values(1 ,'2010-12-11', 2000)
go--sql 2005
select m.id , m.a - n.a from
(select t.* , px = row_number() over(partition by id order by date desc) from tb t) m,
(select t.* , px = row_number() over(partition by id order by date desc) from tb t) n
where m.id = n.id and m.px = 1 and n.px = 2drop table tb/*
id
----------- -----------
1 1000(1 行受影响)*/
create table TTb
(
id int,
date datetime,
A int
)
insert into TTb values(1,'2010-12-13',1000)
insert into TTb values(1,'2010-12-14',2000)
insert into TTb values(1,'2010-12-11',3000)
insert into TTb values(2,'2010-11-13',3000)
insert into TTb values(2,'2010-11-14',5000)
insert into TTb values(2,'2010-11-11',7000)
insert into TTb values(3,'2010-11-11',7000)
insert into TTb values(3,'2010-11-12',7000)
insert into TTb values(4,'2010-11-12',4000)
create table #TTb
(
num int,
id int,
A int
)insert into #TTb
select num,id, A from
(
select *, (select count(*) from ttb where id = t.id and date > t.date)+1 as num from ttb t
) tt select a.id ,a.A-isnull(b.A,0) as A from
(select * from #TTb
where num=1) a
left join
(select * from #TTb
where num=2) b on a.id=b.id
CREATE TABLE #tt (id INT,date DATETIME,A INT)
INSERT INTO #tt
SELECT '1','2010-12-22 16:41:44.653','12' UNION ALL
SELECT '1','2010-12-21 15:41:44.653','52' UNION ALL
SELECT '1','2010-12-25 13:41:44.653','52'
GO--查询数据表
SELECT * FROM #tt
--所满足的查询语句
select m.id , m.a - n.a from
(select t.* , px = row_number() over(partition by id order by date desc) from #tt t) m,
(select t.* , px = row_number() over(partition by id order by date desc) from #tt t) n
WHERE m.px = 1 and n.px = 2