日期:2014-05-17 浏览次数:20484 次
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[name] VARCHAR(1))
INSERT [tb]
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'B' UNION ALL
SELECT 4,'B' UNION ALL
SELECT 4,'C'
--------------开始查询--------------------------
; WITH cte AS
(
SELECT * ,row_id=ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY id) FROM [tb]
)
SELECT [id] ,[name],CASE WHEN row_id =1 THEN 1 ELSE 0 END FROM [cte]
----------------结果----------------------------
/*
id name (无列名)
1 A 1
2 B 1
3 B 0
4 B 0
4 C 1
*/