22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT *, DENSE_RANK()OVER(ORDER BY 城市, _r)
FROM(
SELECT *,
SUM(CASE WHEN 时间差值 > 24 THEN 1 END) OVER(PARTITION BY 城市 ORDER BY 序号) as _r
FROM #t
)a
ORDER BY 城市, 序号
-- 10 万测试数据
SELECT TOP(10000*10)
序号 = IDENTITY(int, 1, 1), 城市 = O.name, 时间差值=ABS(CHECKSUM(NEWID()))%47
INTO #t
FROM sys.all_objects O, sys.all_objects B
;
-- 查询( LAG 函数取上条记录值,在 >=2012 的版本中支持)
SELECT *, SUM(_r)OVER(ORDER BY 城市, 序号)
FROM(
SELECT *,
CASE CASE WHEN 时间差值 > 24 THEN 1 ELSE 0 END
WHEN LAG(CASE WHEN 时间差值 > 24 THEN 1 ELSE 0 END, 1, 2)OVER(PARTITION BY 城市 ORDER BY 序号)
THEN 0 ELSE 1
END as _r
FROM #t
)A
ORDER BY 城市, 序号
GO
DROP TABLE #t
use Tempdb
go
if not object_id(N'Tempdb..#tabA') is null
drop table #tabA
Go
Create table #tabA(id int identity(1,1),[城市] nvarchar(20),[时间差值] float)
INSERT INTO #tabA VALUES ('北京','0')
INSERT INTO #tabA VALUES ('北京','150.1')
INSERT INTO #tabA VALUES ('北京','15.1')
INSERT INTO #tabA VALUES ('北京','60.2')
INSERT INTO #tabA VALUES ('上海','0')
INSERT INTO #tabA VALUES ('上海','22.3')
INSERT INTO #tabA VALUES ('南京','0')
INSERT INTO #tabA VALUES ('南京','5.6')
INSERT INTO #tabA VALUES ('深圳','0')
INSERT INTO #tabA VALUES ('深圳','22.1')
Go
select id,城市,时间差值,标识ID=dense_rank() over (order by 城市,时间差) from
(select a.* , 时间差 =case when abs(a.时间差值-b.时间差值)>24 then a.id else 0 end from #tabA as a left join #tabA as b on a.城市=b.城市 and a.id=b.id+1) c
IF OBJECT_ID(N'tempdb..#t') IS NOT NULL
DROP TABLE #t
GO
CREATE TABLE #t
(
序号 INT
, 城市 VARCHAR(10)
, 时间减值 FLOAT
)
INSERT INTO #t VALUES
(1,'北京',0),
(2,'北京',150.1),
(3,'北京',15.1),
(4,'北京',60.2),
(5,'上海',0),
(6,'上海',22.3),
(7,'南京',0),
(8,'南京',5.6),
(9,'深圳',0),
(10,'深圳',22.1);
WITH CTE
AS
(
SELECT A.*
, B.[城市] AS 城市B
FROM #t AS A
LEFT JOIN #t AS B
ON A.序号 = B.序号 + 1
)
, CTEB
AS
(
SELECT *
, CASE
WHEN 城市 = 城市B AND 时间减值 >= 24 THEN 1
WHEN 城市 != 城市B THEN 1
ELSE 0
END 标识ID2
FROM CTE
)
SELECT A.序号
, A.城市
, A.时间减值
, 标识ID = (
SELECT SUM(标识ID2) + 1
FROM CTEB AS B
WHERE B.序号 <= A.序号
)
FROM CTEB AS A