62,046
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[id] INT
,[name] NVARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1',N'小明')
INSERT INTO dbo.[t] VALUES(N'2',N'小红')
INSERT INTO dbo.[t] VALUES(N'3',N'小王')
-------- 以上为测试表及测试数据 --------
;WITH cte AS (
SELECT *,ROW_NUMBER() OVER (ORDER BY id) AS rid
FROM t
)
SELECT id,[name],CASE WHEN rid=1 THEN (SELECT MAX(rid) FROM cte) ELSE 0 END AS [count]
FROM cte
/*
id name count
1 小明 3
2 小红 0
3 小王 0
*/