DECLARE @s VARCHAR(100)='1,2,3,4,5,6' SELECT t.c.value('.','int') AS col INTO #tt from (SELECT CAST('<x>'+REPLACE(@s,',','</x><x>')+'</x>' AS XML ).query('.') AS name) AS a CROSS APPLY a.name.nodes('/x') T(c)
SELECT MAX(col),AVG(col),MIN(col),SUM(col) FROM #tt
IF OBJECT_ID('tempdb..[##tmp]') is NOT NULL DROP TABLE ##tmp GO declare @string varchar(1000) set @string='1,2,3,4' set @string='select * into ##tmp from (select '+replace(@string,',',' as str union all select ')+') a' Exec(@string) ------------------------------------------------查询---------------------------------------- SELECT MAX([str]) '最大值',MIN([str]) '最小值',AVG([str]) '最小值' ,SUM([str]) '求和' from ##tmp
IF OBJECT_ID('tempdb..[##tmp]') is NOT NULL DROP TABLE ##tmp GO declare @string varchar(1000) set @string='1,2,3,4' set @string='select * into ##tmp from (select '+replace(@string,',',' as str union all select ')+') a' Exec(@string) ------------------------------------------------查询---------------------------------------- SELECT MAX([str]) '最大值',MIN([str]) '最小值',AVG([str]) '平均值' ,SUM([str]) '求和' from ##tmp
SELECT t.c.value('.','int') AS col
INTO #tt
from
(SELECT CAST('<x>'+REPLACE(@s,',','</x><x>')+'</x>' AS XML ).query('.') AS name) AS a
CROSS APPLY a.name.nodes('/x') T(c)
SELECT MAX(col),AVG(col),MIN(col),SUM(col)
FROM #tt
DROP TABLE ##tmp
GO
declare @string varchar(1000)
set @string='1,2,3,4'
set @string='select * into ##tmp from (select '+replace(@string,',',' as str union all select ')+') a'
Exec(@string)
------------------------------------------------查询----------------------------------------
SELECT MAX([str]) '最大值',MIN([str]) '最小值',AVG([str]) '最小值' ,SUM([str]) '求和' from ##tmp
------------------------------------------------结果----------------------------------------
/*
最大值 最小值 最小值 求和
----------- ----------- ----------- -----------
4 1 2 10(1 行受影响)
*/
DROP TABLE ##tmp
GO
declare @string varchar(1000)
set @string='1,2,3,4'
set @string='select * into ##tmp from (select '+replace(@string,',',' as str union all select ')+') a'
Exec(@string)
------------------------------------------------查询----------------------------------------
SELECT MAX([str]) '最大值',MIN([str]) '最小值',AVG([str]) '平均值' ,SUM([str]) '求和' from ##tmp
------------------------------------------------结果----------------------------------------
/*
最大值 最小值 平均值 求和
----------- ----------- ----------- -----------
4 1 2 10(1 行受影响)
*/
太感激了,代码很精辟!不过有一点点局限,不能实现@s VARCHAR(100)='1+1,2,3,4*2,5,6' 这类的。