字符串:12,-1.0,N;13,-1.0,N;14,-1.0,N;15,-1.0,N;16,-1.0,N;17,-1.0,N;18,-1.0,N;19,-1.0,N;20,-1.0,N;21,-1.0,N;22,-1.0,N;23,-1.0,N;00,-1.0,N;01,-1.0,N;02,-1.0,N;03,-1.0,N;04,-1.0,N;05,-1.0,N;06,-1.0,N;07,-1.0,N;08,-1.0,N;09,-1.0,N;10,-1.0,N;11,-1.0,N;
需求:写一个函数先以 ;分割,然后再取出第二个值(上例中每个;分割下来的-1),最后把这些值avg 再返回一个值
实现:函数,存储过程都可以,只要能实现这个功能就行,想了好久不知道怎么实现,看了网上的例子分割实现了,但是再分割的时候就蒙了。。附一个参考例子地址:
http://wenku.baidu.com/view/4b0dcd5ebe23482fb4da4cdc.html滴水之恩,无以回报,先谢谢诸位仁兄了。数据库
需求:写一个函数先以 ;分割,然后再取出第二个值(上例中每个;分割下来的-1),最后把这些值avg 再返回一个值
实现:函数,存储过程都可以,只要能实现这个功能就行,想了好久不知道怎么实现,看了网上的例子分割实现了,但是再分割的时候就蒙了。。附一个参考例子地址:
http://wenku.baidu.com/view/4b0dcd5ebe23482fb4da4cdc.html滴水之恩,无以回报,先谢谢诸位仁兄了。数据库
DROP FUNCTION dbo.Split
SELECT * from dbo.Split(@s,';')
*/
CREATE FUNCTION dbo.Split
(
@SplitString varchar(8000),-- nvarchar(4000)
@Separator varchar(2) = ','
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] varchar(8000)-- nvarchar(4000)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText varchar(8000);-- nvarchar(4000)
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); INSERT INTO @SplitStringsTable([value])
VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
ENDdeclare @s nvarchar(4000)
set @s='12,-1.0,N;13,-1.0,N;14,-1.0,N;15,-1.0,N;16,-1.0,N;17,-1.0,N;18,-1.0,N;19,-1.0,N;20,-1.0,N;21,-1.0,N;22,-1.0,N;23,-1.0,N;00,-1.0,N;01,-1.0,N;02,-1.0,N;03,-1.0,N;04,-1.0,N;05,-1.0,N;06,-1.0,N;07,-1.0,N;08,-1.0,N;09,-1.0,N;10,-1.0,N;11,-1.0,N;'SELECT * from dbo.Split(@s,';')
/*
id value
------------------------------
1 12,-1.0,N
2 13,-1.0,N
3 14,-1.0,N
4 15,-1.0,N
5 16,-1.0,N
6 17,-1.0,N
7 18,-1.0,N
8 19,-1.0,N
9 20,-1.0,N
10 21,-1.0,N
11 22,-1.0,N
12 23,-1.0,N
13 00,-1.0,N
14 01,-1.0,N
15 02,-1.0,N
16 03,-1.0,N
17 04,-1.0,N
18 05,-1.0,N
19 06,-1.0,N
20 07,-1.0,N
21 08,-1.0,N
22 09,-1.0,N
23 10,-1.0,N
24 11,-1.0,N
*/第一步实现了,还有“最后把这些值avg 再返回一个值”,这句话什么理解,想要avg哪个值 ?
id value
------------------------------
1 12,-1.0,N
2 13,-1.0,N
3 14,-1.0,N
4 15,-1.0,N
不是这样的,还得查出来 12,-1.0,N 中的 -1.0这个值,最后返回的是这个值的avg
GO
IF OBJECT_ID('dbo.Arrays') IS NOT NULL
DROP TABLE dbo.Arrays;
GOCREATE TABLE dbo.Arrays
(
arrid VARCHAR(10) NOT NULL PRIMARY KEY,
array VARCHAR(8000) NOT NULL
)INSERT INTO Arrays(arrid, array) VALUES('A', '20,22,25,25,14');
INSERT INTO Arrays(arrid, array) VALUES('B', '30,33,28');
INSERT INTO Arrays(arrid, array) VALUES('C', '12,10,8,12,12,13,12,14,10,9');
INSERT INTO Arrays(arrid, array) VALUES('D', '-4,-6,-4,-2');SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
ENDINSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;WITH SplitCTE AS
(
SELECT arrid, 1 AS pos, 1 AS startpos,
CHARINDEX(',', array + ',') - 1 AS endpos
FROM dbo.Arrays
WHERE LEN(array) > 0 UNION ALL SELECT Prv.arrid, Prv.pos + 1, Prv.endpos + 2,
CHARINDEX(',', Cur.array + ',', Prv.endpos + 2) - 1
FROM SplitCTE AS Prv
JOIN dbo.Arrays AS Cur
ON Cur.arrid = Prv.arrid
AND CHARINDEX(',', Cur.array + ',', Prv.endpos + 2) > 0
)
SELECT A.arrid, pos,
CAST(SUBSTRING(array, startpos, endpos-startpos+1) AS INT) AS element
FROM dbo.Arrays AS A
JOIN SplitCTE AS S
ON S.arrid = A.arrid
ORDER BY arrid, pos;
给你个相似的例子,自己看看吧。
set @s='12,-1.0,N;13,-1.0,N;14,-1.0,N;15,-1.0,N;16,-1.0,N;17,-1.0,N;18,-1.0,N;19,-1.0,N;20,-1.0,N;21,-1.0,N;22,-1.0,N;23,-1.0,N;00,-1.0,N;01,-1.0,N;02,-1.0,N;03,-1.0,N;04,-1.0,N;05,-1.0,N;06,-1.0,N;07,-1.0,N;08,-1.0,N;09,-1.0,N;10,-1.0,N;11,-1.0,N;'select avg(cast(col2 as decimal)) as col2
from
(
select id,col1,SUBSTRING(col2,1,charindex(',',col2)-1) as col2,
RIGHT(col2,LEN(col2)-LEN(SUBSTRING(col2,1,charindex(',',col2)-1))-1) as col3
from
(select id,SUBSTRING(value,1,charindex(',',value)-1) as col1,
RIGHT(value,LEN(value)-LEN(SUBSTRING(value,1,charindex(',',value)-1))-1) as col2
from (SELECT * from dbo.Split(@s,';'))a
)b
)t/*
col2
-1.000000
*/