22,206
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
userName NVARCHAR(10),
datecol DATETIME
)
GO
SET NOCOUNT OFF
INSERT INTO t VALUES('李四','2020-01-02');
INSERT INTO t VALUES('李四','2020-01-06');
INSERT INTO t VALUES('王五','2020-01-03');
INSERT INTO t VALUES('王五','2020-01-04');
INSERT INTO t VALUES('王五','2020-01-05');
INSERT INTO t VALUES('张三','2020-01-01');
INSERT INTO t VALUES('张三','2020-01-07');
INSERT INTO t VALUES('张三','2020-01-08');
INSERT INTO t VALUES('张三','2020-01-09');
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
SELECT t.userName,t.datecol
INTO #tmp
FROM t
GROUP BY t.userName,t.datecol
ORDER BY t.userName,t.datecol
SELECT *
,CAST((SELECT COUNT(1) FROM #tmp AS b WHERE a.userName=b.userName AND b.datecol<=a.datecol) AS VARCHAR(10))
+a.userName AS 新增列
FROM #tmp AS a
/*
userName datecol 新增列
---------- ----------------------- --------------------
李四 2020-01-02 00:00:00.000 1李四
李四 2020-01-06 00:00:00.000 2李四
王五 2020-01-03 00:00:00.000 1王五
王五 2020-01-04 00:00:00.000 2王五
王五 2020-01-05 00:00:00.000 3王五
张三 2020-01-01 00:00:00.000 1张三
张三 2020-01-07 00:00:00.000 2张三
张三 2020-01-08 00:00:00.000 3张三
张三 2020-01-09 00:00:00.000 4张三
*/