34,590
社区成员
发帖
与我相关
我的任务
分享
--函数中不能用临时表 ,不好做
declare @ret varchar(30)
declare @type varchar(20)
select @type='A'
select id=identity(int,1,1),* into #temp from tb where 标志=@type
select
id=(select count(1) from (select t.id,r.number,
code=isnull(cast(substring(t.数量,r.number,charindex(' ',t.数量+' ',r.number)- r.number) as int),0)
from master..spt_values r,#temp t
where r.type='P' and r.number between 1 and len(t.数量)
and substring(' '+t.数量,r.number,1)=' '
) h where id=f.id and number<=f.number),
code
into #work
from
(
select t.id,r.number,
code=isnull(cast(substring(t.数量,r.number,charindex(' ',t.数量+' ',r.number)- r.number) as int),0)
from master..spt_values r,#temp t
where r.type='P' and r.number between 1 and len(t.数量)
and substring(' '+t.数量,r.number,1)=' '
) f
select @ret=isnull(@ret+' ','')+ltrim(code)
from (select sum(code) as code from #work group by id) t
select @type,@ret
drop table #temp,#work
-----------------------------
A 6 14 26 0 20
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-01-18 11:59:40
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([标志] varchar(1),[数量] VARCHAR(200))
insert #TB
select 'A','3 5 13 0 9' union all
select 'A','3 9 13 0 11'
--------------开始查询--------------------------
select ID=IDENTITY(INT,1,1),* INTO #T from #TB
--SELECT * FROM #T
SELECT ID, 标志,
SUBSTRING(REPLACE(数量,' ',','),
NUMBER,CHARINDEX(',',REPLACE(数量,' ',',')+',',NUMBER)-NUMBER)AS 数量
FROM #T ,
MASTER..SPT_VALUES WHERE TYPE='P' AND SUBSTRING(','+REPLACE(数量,' ',','),NUMBER,1)=','
ID 标志 数量
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A 3
1 A 5
1 A 13
1 A 0
1 A 9
2 A 3
2 A 9
2 A 13
2 A 0
2 A 11
(所影响的行数为 10 行)
--DROP TABLE #T
----------------结果----------------------------
/*
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO
--分段截取
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
if not object_id('tb') is null
drop table tb
Go
create table tb(标志 varchar(10),数量 varchar(20))
Insert tb
select N'A','3 5 13 0 9' union all
select N'A','3 9 13 0 11'
select 标志,
sum(cast(dbo.f_GetStr(数量,1,' ')as int))A,
sum(cast(dbo.f_GetStr(数量,2,' ')as int))B,
sum(cast(dbo.f_GetStr(数量,3,' ')as int))C,
sum(cast(dbo.f_GetStr(数量,4,' ')as int))D,
sum(cast(dbo.f_GetStr(数量,5,' ')as int))E
from tb
group by 标志
/*
标志 A B C D E
---------- ----------- ----------- ----------- ----------- -----------
A 6 14 26 0 20
(1 個資料列受到影響)
*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([标志] varchar(20),[数量] varchar(30))
insert [tb]
select 'A','3 5 13 0 9' union all
select 'A','3 9 13 0 11'
--函数
create function dbo.getvalues(@type varchar(20))
returns varchar(30)
as
begin
declare @ret varchar(30)
declare @tb table(id int identity(1,1),数量 varchar(30))
insert into @tb
select 数量 from tb where 标志=@type
declare @work table(id int,code int)
insert into @work
select
id=(select count(1) from (select t.id,r.number,
code=cast(substring(t.数量,r.number,charindex(' ',t.数量+' ',r.number)- r.number) as int)
from master..spt_values r,@tb t
where r.type='P' and r.number between 1 and len(t.数量)
and substring(' '+t.数量,r.number,1)=' '
) h where id=f.id and number<=f.number),
code
from
(
select t.id,r.number,
code=cast(substring(t.数量,r.number,charindex(' ',t.数量+' ',r.number)- r.number) as int)
from master..spt_values r,@tb t
where r.type='P' and r.number between 1 and len(t.数量)
and substring(' '+t.数量,r.number,1)=' '
) f
select @ret=isnull(@ret+' ','')+ltrim(code)
from (select sum(code) as code from @work group by id) t
return @ret
end
--查询
select [标志],dbo.getvalues([标志]) as [数量]
from tb
group by [标志]
--结果
-----------------------------------
A 6 14 26 0 20
drop table t_tag
--创建表
create table t_tag
(
tag varchar(1),
code varchar(100)
)
--truncate table t_tag
insert into t_tag select 'A', '3 5 13 0 9' union all select 'A', '3 9 13 0 11'
--一句SQL的写法.......
select tag,code=rtrim(replace(replace(
(
select sum(cast(code as int)) as [code] from
(
select tag, number,code=substring(code,number,charindex(' ',code+' ',number)- number)
from master..spt_values,t_tag
where type='p'
and substring(' '+code,number,1)=' ' and number<>1 and number <= len(code)
) t
where t.code<>' ' group by number,t.tag for xml path('')
),'<code>',''),'</code>',' ')
)
from
t_tag group by tag
--基于SQL SERVER 测试通过
tag code
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A 6 14 26 0 20
CREATE TABLE line(a varchar(20),b varchar(100))
INSERT line SELECT 'A','3 5 13 0 9'
UNION ALL SELECT 'A','3 9 13 0 11'
UNION ALL SELECT 'B','1 3 7 0 51'
go
CREATE FUNCTION getLine(@b varchar(20),@pos int)
RETURNS INT
AS
BEGIN
DECLARE @t varchar(200),@p INT
SELECT @p=charindex(' ',@b),@t=left(@b,@p-1),@b=ltrim(right(@b,len(@b)-@p))
IF @pos>1
set @t=PARSENAME(replace(@b,' ','.'),5-@pos+1)
RETURN @t*1
END
GO
SELECT a,
cast(sum(dbo.getLine(b,1)) AS VARCHAR(20))+' '+
cast(sum(dbo.getLine(b,2)) AS varchar(20))+' '+
cast(sum(dbo.getLine(b,3)) AS varchar(20))+' '+
cast(sum(dbo.getLine(b,4)) AS varchar(20))+' '+
cast(sum(dbo.getLine(b,5)) as varchar(20)) [sum] FROM line
GROUP BY a
--result
/*a sum
-------------------- ------------------------------
A 6 14 26 0 20
B 1 3 7 0 51
(所影响的行数为 2 行)
*/