SQL字符串，拆分并相加的問題，謝謝幫忙！

jackbaby 2009-02-10 03:53:49

料號
4NA67-012 1589PC , 4NB00-005 12460PC , 4NB00-006 4799PC
4NA67-012 1589PC
.
.
.

這樣的數據形式，我想取出每行凡后綴是PC的字符串，把前面的數字加總，請問有什么方法沒？？謝謝！
...全文
865 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
jackbaby 2009-02-10
  • 打赏
  • 举报
 回复
好強的邏輯。。看不懂中，確實能用。。。謝謝高手，慢慢研究中，謝謝
liangCK 2009-02-10
  • 打赏
  • 举报
 回复 
--2005 一句解决



DECLARE @t TABLE(料號 varchar(1000))

INSERT @t VALUES('7J136-006EWA  107PC , 7JZF1-001EWB1  150PC') 

INSERT @t VALUES('1022501-LF-PYR  3600PC') 

INSERT @t VALUES('981-000-014  1PC , 981-000-017  1PC , 981-000-035  1PC , 981-001-237  1PC , 991-000-353  1PC , 991-000-954  1PC') 



;WITH Liang AS

(

    SELECT

        料號,

        LTRIM(RTRIM(B.x.value('.','varchar(500)'))) AS data

    FROM(

        SELECT 

            料號,

            CONVERT(XML,'<v>'+REPLACE(料號,',','</v><v>')+'</v>') AS data

        FROM @t

    ) AS A

        CROSS APPLY A.data.nodes('//v') AS B(x)

),

Liang2 AS

(

    SELECT

        料號,

        LEFT(data,CHARINDEX(' ',data)-1) AS s1,

        RIGHT(data,CHARINDEX(' ',REVERSE(data))-1) AS s2

    FROM Liang

)

SELECT 

    料號,

    总和=SUM(CAST(LEFT(s2,CHARINDEX('PC',s2)-1) AS INT))

FROM Liang2

GROUP BY 料號



/*

料號                                                                    总和

----------------------------------------------------------- -----------

7J136-006EWA  107PC , 7JZF1-001EWB1  150PC                     257

1022501-LF-PYR  3600PC                                         3600

981-000-014  1PC , 981-000-017  1PC , 981-000-035  1PC , 981-001-237  1PC , 991-000-353  1PC , 991-000-954  1PC                   6



(3 行受影响)



*/
liangCK 2009-02-10
  • 打赏
  • 举报
 回复 
CREATE FUNCTION dbo.f_StringFormatSum(@料號 varchar(1000))

    RETURNS int

AS

BEGIN

    DECLARE @t TABLE

    (

        data varchar(500),

        s1 AS LEFT(data,CHARINDEX(' ',data)-1),

        s2 AS RIGHT(data,CHARINDEX(' ',REVERSE(data))-1)

    )

    DECLARE @num TABLE(id int identity(1,1),flag bit)

    

    INSERT @num(flag)

        SELECT TOP 1000

            0

        FROM syscolumns,sysobjects

        

    INSERT @t(data)

        SELECT

            LTRIM(RTRIM(SUBSTRING(@料號,A.id,CHARINDEX(',',@料號+',',A.id)-A.id))) AS data

        FROM @num AS A

        WHERE SUBSTRING(','+@料號,A.id,1)=','

    

    RETURN (

        SELECT

            SUM(CAST(LEFT(s2,CHARINDEX('PC',s2)-1) AS INT))

        FROM @t

    )

END

GO



DECLARE @t TABLE(料號 varchar(1000))

INSERT @t VALUES('7J136-006EWA  107PC , 7JZF1-001EWB1  150PC') 

INSERT @t VALUES('1022501-LF-PYR  3600PC') 

INSERT @t VALUES('981-000-014  1PC , 981-000-017  1PC , 981-000-035  1PC , 981-001-237  1PC , 991-000-353  1PC , 991-000-954  1PC') 



SELECT

    料號,

    dbo.f_StringFormatSum(料號) AS 总和

FROM @t



GO

DROP FUNCTION dbo.f_StringFormatSum



/*

料號                                                                    总和

----------------------------------------------------------- -----------

7J136-006EWA  107PC , 7JZF1-001EWB1  150PC                     257

1022501-LF-PYR  3600PC                                         3600

981-000-014  1PC , 981-000-017  1PC , 981-000-035  1PC , 981-001-237  1PC , 991-000-353  1PC , 991-000-954  1PC                   6



(3 行受影响)

*/
liangCK 2009-02-10
  • 打赏
  • 举报
 回复
那是2005的。
2000的要改改。
jackbaby 2009-02-10
  • 打赏
  • 举报
 回复
謝謝dawugui的幫助

特別謝謝liangCK，看結果好像是這樣的思路，但是我看不懂函數，能否解釋下呢？？謝謝



特別是
B.x.value
A.data.nodes('//v')
APPLY
B(x)

INSERT @t(data)
SELECT
LTRIM(RTRIM(B.x.value('.','varchar(500)'))) AS data
FROM(
SELECT
CONVERT(XML,'<v>'+REPLACE(@料號,',','</v><v>')+'</v>') AS data
) AS A
CROSS APPLY A.data.nodes('//v') AS B(x)
liangCK 2009-02-10
  • 打赏
  • 举报
 回复 
CREATE FUNCTION dbo.f_StringFormatSum(@料號 varchar(1000))

    RETURNS int

AS

BEGIN

    DECLARE @t TABLE(data varchar(500),s1 varchar(500),s2 varchar(500))

    

    INSERT @t(data)

        SELECT

            LTRIM(RTRIM(B.x.value('.','varchar(500)'))) AS data

        FROM(

            SELECT 

                CONVERT(XML,'<v>'+REPLACE(@料號,',','</v><v>')+'</v>') AS data

        ) AS A

            CROSS APPLY A.data.nodes('//v') AS B(x)

    

    UPDATE @t SET

        s1=LEFT(data,CHARINDEX(' ',data)-1),

        s2=RIGHT(data,CHARINDEX(' ',REVERSE(data))-1)

    

    RETURN (

        SELECT

            SUM(CAST(LEFT(s2,CHARINDEX('PC',s2)-1) AS INT))

        FROM @t

    )

END

GO



DECLARE @t TABLE(料號 varchar(1000))

INSERT @t VALUES('7J136-006EWA  107PC , 7JZF1-001EWB1  150PC') 

INSERT @t VALUES('1022501-LF-PYR  3600PC') 

INSERT @t VALUES('981-000-014  1PC , 981-000-017  1PC , 981-000-035  1PC , 981-001-237  1PC , 991-000-353  1PC , 991-000-954  1PC') 



SELECT

    料號,

    dbo.f_StringFormatSum(料號) AS 总和

FROM @t



GO

DROP FUNCTION dbo.f_StringFormatSum





/*

料號                                                                    总和

----------------------------------------------------------- -----------

7J136-006EWA  107PC , 7JZF1-001EWB1  150PC                     257

1022501-LF-PYR  3600PC                                         3600

981-000-014  1PC , 981-000-017  1PC , 981-000-035  1PC , 981-001-237  1PC , 991-000-353  1PC , 991-000-954  1PC                   6



(3 行受影响)

*/
dawugui 2009-02-10
  • 打赏
  • 举报
 回复
如果一行超过三个逗号就难办了,给个例,自己参考.



/*

标题：分解字符串并查询相关数据

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 

时间：2008-03-18

地点：广东深圳

说明：通过使用函数等方法分解字符串查询相关数据。



问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。

例如 @str = '1,2,3',查询下表得到记录1,4,5,6

ID TypeID

1  1,2,3,4,5,6,7,8,9,10,11,12

2  2,3 

3  3,7,8,9 

4  2,6 

5  4,5

6  6,7 

*/

-----------------------------

create table tb (ID int , TypeID varchar(30)) 

insert into tb values(1 , '1,2,3,4,5,6,7,8,9,10,11,12') 

insert into tb values(2 , '2,3') 

insert into tb values(3 , '3,7,8,9') 

insert into tb values(4 , '2,6') 

insert into tb values(5 , '4,5')

insert into tb values(6 , '6,7')

go

-----------------------------

--如果仅仅是一个,如@str = '1'.

declare @str as varchar(30)

set @str = '1'

select * from tb where charindex(',' + @str + ',' , ',' + TypeID + ',') > 0

select * from tb where ',' + TypeID + ',' like '%,' + @str + ',%'

/*

ID          TypeID                         

----------- ------------------------------ 

1           1,2,3,4,5,6,7,8,9,10,11,12

（所影响的行数为 1 行）

*/



-----------------------------

--如果包含两个,如@str = '1,2'.

declare @str as varchar(30)

set @str = '1,2'

select * from tb where charindex(',' + left(@str , charindex(',' , @str) - 1) + ',' , ',' + typeid + ',') > 0 or 

  charindex(',' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',' , ',' + typeid + ',') > 0

select * from tb where ',' + typeid + ',' like '%,' + left(@str , charindex(',' , @str) - 1) + ',%' or 

  ',' + typeid + ',' like '%,' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',%'

/*

ID          TypeID                         

----------- ------------------------------ 

1           1,2,3,4,5,6,7,8,9,10,11,12

2           2,3

4           2,6

（所影响的行数为 3 行）

*/



-------------------------------------------

--如果包含三个或四个，用PARSENAME函数来处理.

declare @str as varchar(30)

set @str = '1,2,3,4'

select * from tb where 

  charindex(',' + parsename(replace(@str , ',' , '.') , 4) + ',' , ',' + typeid + ',') > 0 or

  charindex(',' + parsename(replace(@str , ',' , '.') , 3) + ',' , ',' + typeid + ',') > 0 or

  charindex(',' + parsename(replace(@str , ',' , '.') , 2) + ',' , ',' + typeid + ',') > 0 or

  charindex(',' + parsename(replace(@str , ',' , '.') , 1) + ',' , ',' + typeid + ',') > 0 

select * from tb where 

  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 4) + ',%' or

  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 3) + ',%' or

  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 2) + ',%' or

  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 1) + ',%'

/*

ID          TypeID                         

----------- ------------------------------ 

1           1,2,3,4,5,6,7,8,9,10,11,12

2           2,3

3           3,7,8,9

4           2,6

5           4,5

（所影响的行数为 5 行）

*/



---------------------------------------

--如果超过四个，则只能使用函数或动态SQL来分解并查询数据。

/*

名称：fn_split函数.

功能：实现字符串分隔功能的函数

*/

create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))

returns @temp table (a varchar(200))

as 

begin

  declare @i int

  set @inputstr = rtrim(ltrim(@inputstr))

  set @i = charindex(@seprator , @inputstr)

  while @i >= 1

  begin

    insert @temp values(left(@inputstr , @i - 1))

    set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)

    set @i = charindex(@seprator , @inputstr)

  end

  if @inputstr <> '\'

  insert @temp values(@inputstr)

  return 

end

go



--调用

declare @str as varchar(30)

set @str = '1,2,3,4,5'



select distinct m.* from tb m,

(select * from dbo.fn_split(@str,',')) n

where charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0



drop table tb

drop function dbo.fn_split 



/*

ID          TypeID                         

----------- ------------------------------ 

1           1,2,3,4,5,6,7,8,9,10,11,12

2           2,3

3           3,7,8,9

4           2,6

5           4,5

（所影响的行数为 5 行）

*/



------------------------------------------

--使用动态SQL的语句。

declare @str varchar(200)

declare @sql as varchar(1000)

set @str = '1,2,3,4,5'

set @sql = 'select ''' + replace(@str , ',' , ''' as id union all select ''')

set @sql = @sql + ''''

set @sql = 'select distinct a.* from tb a , (' + @sql + ') b where charindex(' + ''','' + b.id + ' + ''',''' + ' , ' + ''','' + a.typeid + ' + ''',''' + ') > 0 '

exec (@sql)

/*

ID          TypeID                         

----------- ------------------------------ 

1           1,2,3,4,5,6,7,8,9,10,11,12

2           2,3

3           3,7,8,9

4           2,6

5           4,5

（所影响的行数为 5 行）

*/
jackbaby 2009-02-10
  • 打赏
  • 举报
 回复
7J136-006EWA 107PC , 7JZF1-001EWB1 150PC
1022501-LF-PYR 3600PC
981-000-014 1PC , 981-000-017 1PC , 981-000-035 1PC , 981-001-237 1PC , 991-000-353 1PC , 991-000-954 1PC



都是可以的
jackbaby 2009-02-10
  • 打赏
  • 举报
 回复
不固定，前面是號碼，后面是數量（帶上PC的單位）
如果有很多這樣的不同號碼，每一行中可以用“，”隔開


我現在不知道怎么辦？？
想得到每行中的數量總和，凡后綴為PC的，該字符串前面的都是數量
dawugui 2009-02-10
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 liangCK 的回复:]
这个格式是固定的吗？
[/Quote].
liangCK 2009-02-10
  • 打赏
  • 举报
 回复
这个格式是固定的吗？
dawugui 2009-02-10
  • 打赏
  • 举报
 回复
每行最多两个逗号?六个数据吗?
liangCK 2009-02-10
  • 打赏
  • 举报
 回复
可以的。
sql 分隔字符串并相加
1.第一种方法 ：oracle 创建函数 CREATE OR REPLACE FUNCTION sumstrsplitBySep(str VARCHAR2 ,sep VARCHAR2) RETURN VARCHAR2 IS RES VARCHAR2(100); TEMP VARCHAR2(100) :=STR; S_INDEX NUMBER; BEGIN LOOP S_INDEX := NVL(INSTR(TEMP,SEP),0); IF S_INDEX=0 OR LENGTH(TEMP)
转：SQL Server字符串拆分（split）
转：SQL Server字符串拆分（split） 转发自：https://www.iteye.com/blog/1017401036-2396054 第一种拆分方法 --支持分割符多字节 --使用方法 --select * from dbo.split('abc-def-ghi-jkl','-') --select * from dbo.split('abc----def----ghi----jkl','----') CREATE FUNCTION [dbo].[spli
SQL Server 字符串拆分指定字符(%)前的数字(整数)和字符
SQL Server 字符串拆分指定字符(%)前的数字(整数)和字符
Sqlserver 数值类型与字符串类型相加、合计
sql 字符串分割求和
功能说明： 要求某个字符串如：1,2,3,4 或者1.11,2.1,4的和 1、函数创建 USE [wqq] GO /****** Object: UserDefinedFunction [dbo].[getSum] Script Date: 07/17/2013 22:04:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIF
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章