27,579
社区成员
发帖
与我相关
我的任务
分享
use tempdb
go
if OBJECT_ID('t') is not null drop table t
go
create table t(
goods nvarchar(20),
num int,
theTime datetime
)
go
insert into t values('桌子',5,'2018-01-01')
insert into t values('桌子',25,'2018-01-31')
insert into t values('桌子',15,'2018-02-01')
insert into t values('椅子',20,'2018-02-03')
insert into t values('椅子',40,'2018-02-05')
GO
;WITH cte AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY goods ORDER BY theTime) rn1,
ROW_NUMBER() OVER (PARTITION BY goods ORDER BY theTime DESC) rn2
FROM t
)
SELECT a.goods,
b.num - a.num AS 差值
FROM cte a
JOIN cte b
ON b.goods = a.goods
WHERE b.rn2 = 1
AND a.rn1 = 1;
use tempdb
go
if OBJECT_ID('t') is not null drop table t
go
create table t(
goods nvarchar(20),
num int,
theTime datetime
)
go
insert into t values('桌子',5,'2018-01-01')
insert into t values('桌子',25,'2018-01-31')
insert into t values('桌子',15,'2018-02-01')
insert into t values('椅子',20,'2018-02-03')
insert into t values('椅子',40,'2018-02-05')
go
;with cte as(
select goods,max(theTime) as maxTime,min(theTime) as minTime from t group by goods
)
select
*
,(select top 1 t.num from t where t.theTime=cte.maxTime) as maxTimeNum
,(select top 1 t.num from t where t.theTime=cte.minTime) as minTimeNum
,(select top 1 t.num from t where t.theTime=cte.maxTime)
-
(select top 1 t.num from t where t.theTime=cte.minTime) as subNum
from cte