如何使具有相同栏位值的行显示相同序列号
例如
NAME VALUE
--------------
NAME1 5
NAME1 4
NAME1 3
NAME2 5
NAME2 4
NAME2 3
想显示为
ID NAME VALUE
---------------------
1 NAME1 5
1 NAME1 4
1 NAME1 3
2 NAME2 5
2 NAME2 4
2 NAME2 3
今天完全不在状态，只好求教各位大神了，谢谢
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-05 16:25:07
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([NAME] varchar(5),[VALUE] int)
insert [huang]
select 'NAME1',5 union all
select 'NAME1',4 union all
select 'NAME1',3 union all
select 'NAME2',5 union all
select 'NAME2',4 union all
select 'NAME2',3
--------------开始查询--------------------------
;WITH cte AS (
select name,ROW_NUMBER()OVER(ORDER BY name)ID
from [huang]
GROUP BY name)
SELECT cte.id,huang.*
FROM huang FULL JOIN cte ON huang.NAME=cte.name
----------------结果----------------------------
/*
id NAME VALUE
-------------------- ----- -----------
1 NAME1 5
1 NAME1 4
1 NAME1 3
2 NAME2 5
2 NAME2 4
2 NAME2 3
*/
WITH CTE(NAME,VALUE)
AS (
SELECT 'NAME1', 5 UNION ALL
SELECT 'NAME1', 4 UNION ALL
SELECT 'NAME1', 3 UNION ALL
SELECT 'NAME2', 5 UNION ALL
SELECT 'NAME2', 4 UNION ALL
SELECT 'NAME2', 3
)
SELECT DENSE_RANK() OVER(ORDER BY NAME) AS ID
,*
FROM CTE
