表A有两个字段,culum1和culum2
culum1 culum2
-.M*INIQK@NLSS#R^DLR^ 2.50
-.M*INIQK@NLSS#R^DLR^ 5.75
K.AITSLLGGGS#[email protected] 0.84
K.AK@PAMPQDSVPS#PR^.S 1.21
K.AK@PAMPQDSVPS#PR^.S 1.21
想计算在字段1中重复的字符串条目在字段2中对应数值的乘积再开方,即求几何平均数。想得到类似的结果: culum1 culum2 culum3
-.M*INIQK@NLSS#R^DLR^ 2.50 3.73 (2.5*5.57再开方)
-.M*INIQK@NLSS#R^DLR^ 5.75 3.73
K.AITSLLGGGS#[email protected] 0.84 0.84
K.AK@PAMPQDSVPS#PR^.S 1.21 1.21
K.AK@PAMPQDSVPS#PR^.S 1.21 1.21
culum1 culum2
-.M*INIQK@NLSS#R^DLR^ 2.50
-.M*INIQK@NLSS#R^DLR^ 5.75
K.AITSLLGGGS#[email protected] 0.84
K.AK@PAMPQDSVPS#PR^.S 1.21
K.AK@PAMPQDSVPS#PR^.S 1.21
想计算在字段1中重复的字符串条目在字段2中对应数值的乘积再开方,即求几何平均数。想得到类似的结果: culum1 culum2 culum3
-.M*INIQK@NLSS#R^DLR^ 2.50 3.73 (2.5*5.57再开方)
-.M*INIQK@NLSS#R^DLR^ 5.75 3.73
K.AITSLLGGGS#[email protected] 0.84 0.84
K.AK@PAMPQDSVPS#PR^.S 1.21 1.21
K.AK@PAMPQDSVPS#PR^.S 1.21 1.21
--> 测试数据: @s
declare @s table (culum1 varchar(21),culum2 numeric(3,2))
insert into @s
select '-.M*INIQK@NLSS#R^DLR^',2.50 union all
select '-.M*INIQK@NLSS#R^DLR^',5.75 union all
select 'K.AITSLLGGGS#[email protected]',0.84 union all
select 'K.AK@PAMPQDSVPS#PR^.S',1.21 union all
select 'K.AK@PAMPQDSVPS#PR^.S',1.21
select culum1,cast(sqrt(cast(exp(sum(log(culum2))) as numeric(8,2))) as numeric(8,2)) from @s
group by culum1
CREATE TABLE TB (culum1 VARCHAR(40),culum2 DECIMAL(10,2))
INSERT TB SELECT '-.M*INIQK@NLSS#R^DLR^',2.50
UNION ALL SELECT '-.M*INIQK@NLSS#R^DLR^',5.75
UNION ALL SELECT 'K.AITSLLGGGS#[email protected]',0.84
UNION ALL SELECT 'K.AK@PAMPQDSVPS#PR^.S',1.21
UNION ALL SELECT 'K.AK@PAMPQDSVPS#PR^.S',1.21 CREATE FUNCTION F_CALCNUM(@COLUM VARCHAR(40))
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @I DECIMAL(10,2)
IF (SELECT COUNT(1) FROM TB WHERE culum1=@COLUM )>1
BEGIN
SELECT @I=(ISNULL(@I,1)*culum2) FROM TB WHERE culum1=@COLUM
SET @I=SQRT(@I)
END
ELSE SELECT @I=culum2 FROM TB WHERE culum1=@COLUM
RETURN @I
ENDSELECT culum1,culum2,culum3=DBO.F_CALCNUM(culum1) FROM TB
/*
culum1 culum2 culum3
---------------------------------------- ------------ ------------
-.M*INIQK@NLSS#R^DLR^ 2.50 3.79
-.M*INIQK@NLSS#R^DLR^ 5.75 3.79
K.AITSLLGGGS#[email protected] .84 .84
K.AK@PAMPQDSVPS#PR^.S 1.21 1.21
K.AK@PAMPQDSVPS#PR^.S 1.21 1.21(所影响的行数为 5 行)
*/
--> 测试数据: @s
declare @s table (culum1 varchar(21),culum2 numeric(3,2))
insert into @s
select '-.M*INIQK@NLSS#R^DLR^',2.50 union all
select '-.M*INIQK@NLSS#R^DLR^',5.75 union all
select 'K.AITSLLGGGS#[email protected]',0.84 union all
select 'K.AK@PAMPQDSVPS#PR^.S',1.21 union all
select 'K.AK@PAMPQDSVPS#PR^.S',1.21
select a.culum1,pp from @s a,
(select culum1,pp=cast(sqrt(cast(exp(sum(log(culum2))) as numeric(8,2))) as numeric(8,2)) from @s
group by culum1)b
where a.culum1=b.culum1