select max(node) from .....
如果node数据库存的是字符串，怎么取这个node的最大值？？？
------解决方案--------------------
- SQL code
--各种字符串分函数if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_splitSTR]GO--3.2.1 循环截取法CREATE FUNCTION f_splitSTR(@s varchar(8000), --待分拆的字符串@split varchar(10) --数据分隔符)RETURNS @re TABLE(col varchar(100))ASBEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2 WHILE CHARINDEX(@split,@s)>0 BEGIN INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) SET @s=STUFF(@s,1,CHARINDEX(@split,@s)[email protected],'') END INSERT @re VALUES(@s) RETURNENDGO/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_splitSTR]GO--3.2.3.1 使用临时性分拆辅助表法CREATE FUNCTION f_splitSTR(@s varchar(8000), --待分拆的字符串@split varchar(10) --数据分隔符)RETURNS @re TABLE(col varchar(100))ASBEGIN --创建分拆处理的辅助表(用户定义函数中只能操作表变量) DECLARE @t TABLE(ID int IDENTITY,b bit) INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@[email protected],ID)-ID) FROM @t WHERE ID<=LEN(@s+'a') AND CHARINDEX(@split,@[email protected],ID)=ID RETURNENDGO/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_splitSTR]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)drop table [dbo].[tb_splitSTR]GO--3.2.3.2 使用永久性分拆辅助表法--字符串分拆辅助表SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTRFROM syscolumns a,syscolumns bGO--字符串分拆处理函数CREATE FUNCTION f_splitSTR(@s varchar(8000), --待分拆的字符串@split varchar(10) --数据分隔符)RETURNS TABLEASRETURN( SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@[email protected],ID)-ID) as varchar(100)) FROM tb_splitSTR WHERE ID<=LEN(@s+'a') AND CHARINDEX(@split,@[email protected],ID)=ID)GO/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_splitSTR]GO--3.2.5 将数据项按数字与非数字再次拆份CREATE FUNCTION f_splitSTR(@s varchar(8000), --待分拆的字符串@split varchar(10) --数据分隔符)RETURNS @re TABLE(No varchar(100),Value varchar(20))ASBEGIN --创建分拆处理的辅助表(用户定义函数中只能操作表变量) DECLARE @t TABLE(ID int IDENTITY,b bit) INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b INSERT @re SELECT No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')), Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1)) FROM( SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@[email protected],ID)-ID)) FROM @t WHERE ID<=LEN(@s+'a') AND CHARINDEX(@split,@[email protected],ID)=ID)a RETURNENDGO/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_splitSTR]GO--3.2.6 分拆短信数据CREATE FUNCTION f_splitSTR(@s varchar(8000))RETURNS @re TABLE(split varchar(10),value varchar(100))ASBEGIN DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split)) INSERT @splits(split) SELECT 'AC' UNION ALL SELECT 'BC' UNION ALL SELECT 'CC' UNION ALL SELECT 'DC' DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int SELECT TOP 1 @pos1=1,@split=split,@splitlen=splitlen FROM @splits WHERE @s LIKE split+'%' WHILE @pos1>0 BEGIN SELECT TOP 1 @pos2=CHARINDEX(split,@s,@splitlen+1) FROM @splits WHERE CHARINDEX(split,@s,@splitlen+1)>0 ORDER BY CHARINDEX(split,@s,@splitlen+1) IF @@ROWCOUNT=0 BEGIN INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,'')) RETURN END ELSE BEGIN INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@[email protected])) SELECT TOP 1 @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'') FROM @splits WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%' END END RETURNENDGO