表1 FENTRYID
2
2.1
2.2
2.2.1
2.2.2
4
4.1
4.2
8
8.1
8.2
8.3
通过查询得到如下结果FID FENTRYID
1 2
1.01 2.1
1.02 2.2
1.02.01 2.2.1
1.02.02 2.2.2
2 4
2.01 4.1
2.02 4.2
3 8
3.01 8.1
3.02 8.2
3.03 8.3
2
2.1
2.2
2.2.1
2.2.2
4
4.1
4.2
8
8.1
8.2
8.3
通过查询得到如下结果FID FENTRYID
1 2
1.01 2.1
1.02 2.2
1.02.01 2.2.1
1.02.02 2.2.2
2 4
2.01 4.1
2.02 4.2
3 8
3.01 8.1
3.02 8.2
3.03 8.3
2
2.1
2.2
2.2.1
2.2.2
4
4.1
4.2
8
8.1
8.2
8.3
上面的结果可能不容易看出来.修改了了下,大家看看
通过查询得到如下结果FID FENTRYID
1 2
1.01 2.1
1.02 2.2
1.02.01 2.2.1
1.02.02 2.2.2
2 4
2.01 4.1
2.02 4.2
3 8
3.01 8.1
3.02 8.2
3.03 8.3
INSERT @TB
SELECT '2' UNION ALL
SELECT '2.1' UNION ALL
SELECT '2.2' UNION ALL
SELECT '2.2.1' UNION ALL
SELECT '2.2.2' UNION ALL
SELECT '4' UNION ALL
SELECT '4.1' UNION ALL
SELECT '4.2' UNION ALL
SELECT '8' UNION ALL
SELECT '8.1' UNION ALL
SELECT '8.2' UNION ALL
SELECT '8.3';WITH CTE AS
(
SELECT *,DENSE_RANK() OVER (ORDER BY LEFT([FENTRYID], 1)) AS GRP FROM @TB
)
SELECT REPLACE(RTRIM(GRP)+STUFF([FENTRYID],1,1,''), '.', '.0') AS ID, [FENTRYID]
FROM CTE
/*
1 2
1.01 2.1
1.02 2.2
1.02.01 2.2.1
1.02.02 2.2.2
2 4
2.01 4.1
2.02 4.2
3 8
3.01 8.1
3.02 8.2
3.03 8.3
*/
go
insert #t1
select '2' union all
select '2.1' union all
select '2.2' union all
select '2.2.1' union all
select '2.2.2' union all
select '4' union all
select '4.1' union all
select '4.2' union all
select '8' union all
select '8.1' union all
select '8.2' union all
select '8.3'SELECT ID = REPLACE( CONVERT(varchar(2),DENSE_RANK() OVER(ORDER BY SUBSTRING(FENTRYID,1,1)))
+'.'+CONVERT(varchar(2),DENSE_RANK() OVER(ORDER BY SUBSTRING(FENTRYID,3,1))-1)
+'.'+CONVERT(varchar(2),DENSE_RANK() OVER(ORDER BY SUBSTRING(FENTRYID,5,1))-1)
,'.0','')
, FENTRYID
FROM #t1
ORDER BY IDID FENTRYID
1 2
1.1 2.1
1.2 2.2
1.2.1 2.2.1
1.2.2 2.2.2
2 4
2.1 4.1
2.2 4.2
3 8
3.1 8.1
3.2 8.2
3.3 8.3
SELECT *,SUBSTRING([FENTRYID], 1,LEN(CHARINDEX('.', [FENTRYID])-1)) AS GRP
INTO #
FROM @TB SELECT REPLACE(RTRIM(ID)+STUFF([FENTRYID],1,1,''), '.', '.0') AS ID, [FENTRYID]
FROM (
SELECT *,(SELECT COUNT(DISTINCT GRP) FROM # WHERE GRP<=T.GRP) AS ID
FROM # AS T
) T2DROP TABLE #
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-14 13:45:06
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([FENTRYID] NVARCHAR(10))
INSERT [tb]
SELECT '2' UNION ALL
SELECT '2.1' UNION ALL
SELECT '2.2' UNION ALL
SELECT '2.2.1' UNION ALL
SELECT '2.2.2' UNION ALL
SELECT '4' UNION ALL
SELECT '4.1' UNION ALL
SELECT '4.2' UNION ALL
SELECT '8' UNION ALL
SELECT '8.1' UNION ALL
SELECT '8.2' UNION ALL
SELECT '8.3'
GO
--SELECT * FROM [tb]-->SQL查询如下:
create function fn_getxh(@s varchar(20))
returns varchar(30)
as
begin
set @s=stuff(@s,1,charindex('.',@s+'.')-1,'')
set @s=replace(@s,'.','.0')
return @s
end
go
select id=ltrim((select count(distinct left([FENTRYID],charindex('.',[FENTRYID]+'.')-1)) from tb where [FENTRYID]<=t.[FENTRYID]))+dbo.fn_getxh([FENTRYID]),
*
from tb t
/*
id FENTRYID
------------------------------------------ ----------
1 2
1.01 2.1
1.02 2.2
1.02.01 2.2.1
1.02.02 2.2.2
2 4
2.01 4.1
2.02 4.2
3 8
3.01 8.1
3.02 8.2
3.03 8.3(12 行受影响)
*/2000的
INSERT @TB
SELECT '2' UNION ALL
SELECT '2.1' UNION ALL
SELECT '2.2' UNION ALL
SELECT '2.2.1' UNION ALL
SELECT '2.2.2' UNION ALL
SELECT '4' UNION ALL
SELECT '4.1' UNION ALL
SELECT '4.2' UNION ALL
SELECT '8' UNION ALL
SELECT '8.1' UNION ALL
SELECT '8.2' UNION ALL
SELECT '8.3'
SELECT *,SUBSTRING([FENTRYID], 1,LEN(CHARINDEX('.', [FENTRYID])-1)) AS GRP
INTO #
FROM @TB SELECT REPLACE(RTRIM(ID)+STUFF([FENTRYID],1,1,''), '.', '.0') AS ID, [FENTRYID]
FROM (
SELECT *,(SELECT COUNT(DISTINCT GRP) FROM # WHERE GRP<=T.GRP) AS ID
FROM # AS T
) T2DROP TABLE #