34,594
社区成员
发帖
与我相关
我的任务
分享
declare @s varchar(100)
create table tb(a float,b float,c float,d float)
set @s='1#23#0.98#9527'
set @s='insert into tb select '+replace(@s,'#',',')
execute (@s)
set @s='88#2#1#0'
set @s='insert into tb select '+replace(@s,'#',',')
execute (@s)
select convert(varchar,sum(a))+'#'+convert(varchar,sum(b))+'#'+convert(varchar,sum(c))+'#'+convert(varchar,sum(d)) from tb
/*
89#25#1.98#9527
(1 行受影响)
*/
go
drop table tb
--声明变量
declare @x varchar(1000)
declare @y varchar(1000)
declare @x1 int
declare @y1 int
declare @z varchar(1000)
set @z = ' '
--赋值变量
set @x = '1#23#0.98#9527'
set @y = '88#2#1#0'
set @x = @x+'#'
set @y = @y+'#'
--计算测试
while(len(@x)<>0 and len(@y)<>0)
begin
set @x1 = charindex('#',@x)-1
set @y1 =charindex('#',@y)-1
if(@x1<=0)
set @x1 = len(@x)
if(@y1<=0) set @y1 = len(@y)
select @z=@z+ cast(cast(substring(@x,1,@x1)as decimal(18,2))+cast(substring(@y,1,@y1)as decimal(18,2)) as varchar)+'#'
set @x = right(@x,len(@x)-charindex('#',@x))
set @y = right(@y,len(@y)-charindex('#',@y))
if(@x='#') set @x = ''
if(@y='#') set @y = ''
end
select replace(@z,'.00','')
---结果展示
/*
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
89#25#1.98#9527#
(1 行受影响)
*/
--拆分函数
--CREATE FUNCTION fn_GetSplit(@s varchar(50))
-- RETURNS @t TABLE(rowid int IDENTITY(1,1), value varchar(50))
--AS
--BEGIN
-- ;WITH c1 AS
-- (
-- SELECT b.v
-- FROM
-- (
-- SELECT CAST(('<v>' + REPLACE(@s, '#', '</v><v>') + '</v>') AS Xml) s
-- )a
-- CROSS APPLY
-- (
-- SELECT t.v.value('.','varchar(10)') v
-- FROM a.s.nodes('//v') t(v)
-- )b
-- )
-- INSERT INTO @t(value)
-- SELECT v
-- FROM c1
-- RETURN;
--END
--GO
--先把拆分函数fn_GetSplit添加到数据库中
;WITH c1 AS
(
SELECT CAST(f1.value as decimal(18,2)) + CAST(f2.value as decimal(18,2)) d1
FROM fn_GetSplit('1#23#0.98#9527') f1
JOIN fn_GetSplit('88#2#1#0') f2 ON f1.rowid = f2.rowid
)
SELECT STUFF((SELECT '#'+CAST(d1 AS VARCHAR(50))
FROM c1
FOR XML PATH('')),1,1,'')
--------------------------
89.00#25.00#1.98#9527.00
(1 行受影响)