22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
[name] NVARCHAR(10) NOT NULL DEFAULT('张三'),
visit_time DATETIME
)
GO
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-01 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-02 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-04 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-05 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-06 08:00:00')
--
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-01 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-03 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-04 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-05 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-06 08:00:00')
;WITH cte1 AS(
SELECT *,
DATEDIFF(DAY, (SELECT MIN(visit_time) FROM t), visit_time) AS rid
FROM t
),cte2 AS(
SELECT NAME,
rid,
rid -ROW_NUMBER()OVER(PARTITION BY [name] ORDER BY rid) AS diff
FROM cte1
)
,cte3 AS (
SELECT NAME,
MIN(rid) AS start_range,
MAX(rid) AS end_range
FROM cte2
GROUP BY
diff,
NAME
)
SELECT
cte1.id
,cte1.[name]
,cte1.[visit_time]
,cte1.rid -start_range + 1 AS flag
FROM cte3
INNER JOIN cte1
ON cte1.name = cte3.name
AND cte1.rid BETWEEN cte3.start_range AND cte3.end_range
ORDER BY
cte1.name DESC,
cte1.visit_time
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
[name] NVARCHAR(10) NOT NULL DEFAULT('张三'),
visit_time DATETIME
)
GO
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-01 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-02 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-04 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-05 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('张三','2018-12-06 08:00:00')
--
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-01 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-03 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-04 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-05 08:00:00')
INSERT INTO t(name,visit_time) VALUES ('李四','2018-12-06 08:00:00')
;WITH cte1 AS(
SELECT *,
DATEDIFF(DAY, (SELECT MIN(visit_time) FROM t), visit_time) AS rid
FROM t
),cte2 AS(
SELECT NAME,
rid,
rid -ROW_NUMBER()OVER(PARTITION BY [name] ORDER BY rid) AS diff
FROM cte1
)
,cte3 AS (
SELECT NAME,
MIN(rid) AS start_range,
MAX(rid) AS end_range
FROM cte2
GROUP BY
diff,
NAME
)
SELECT
cte1.id
,cte1.[name]
,cte1.[visit_time]
,cte1.rid -start_range + 1 AS flag
FROM cte3
INNER JOIN cte1
ON cte1.name = cte3.name
AND cte1.rid BETWEEN cte3.start_range AND cte3.end_range
ORDER BY
cte1.name DESC,
cte1.visit_time
版主威武!!!!