求字符型字段的求和公式？

2008-11-09 05:20:38 453 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

用Power Query删除字段名中的多余字符提到字符型数据的清洗方法，相信不少朋友会想到Trim函数，很多编程语言中也会有Trim或类Trim方法。先简单介绍一下Trim：该函数可移除字符串两侧的空白字符或其他预定义字符，但仅能删除开头或结尾的字符，不能删除中间部分的字符。所以本文将会介绍一种利用Power Query删除字段名中间多余字符的方法（以空格为例）。 1示例# 从Power Query自动生成的代码中可以看到，该表的字段名中间确实有或多或少的空格。 2步骤详解（不想看过程的朋友可直接跳到下一部分“步骤整合”） Step1：首先，如. 阅读详情

对数值字段求和可用sum(column)... group by 实现，可是当我要对字符型字段求和（把每一列的内容连接起来）怎么实现？？

...全文

Tableau学习 - 计算字段根据数据源字段（维度、度量、参数），使用函数或和运算符构造公式，自定义的字段。可以拖拽至工作区构建视图，也可以用来构建新的字段。返回值分为数值型、字符型等。新建的计算字段会出现在维度、度量、参数面板中，其中维度和度量的数据会保存新的一列至源数据源。阅读详情

thncn

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

等不到来世 2008-11-10

打赏
举报

if not object_id('Tab') is null

    drop table Tab

Go

Create table Tab([Col1] int,[Col2] nvarchar(1))

Insert Tab

select 1,N'a' union all

select 1,N'b' union all

select 1,N'c' union all

select 2,N'd' union all

select 2,N'e' union all

select 3,N'f'

Go



合并表:



SQL2000用函数:



go

if object_id('F_Str') is not null

    drop function F_Str

go

create function F_Str(@Col1 int)

returns nvarchar(100)

as

begin

    declare @S nvarchar(100)

    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1

    return @S

end

go

Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab



go



SQL2005用XML:



方法1:



select 

    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')

from 

    (select distinct COl1 from Tab) a

Cross apply

    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b



方法2:



select 

    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))

from 

    (select distinct COl1 from Tab) a

cross apply

    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE)

                .query('<Tab>

                {for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}

                {concat("",string(/Tab[last()]/@COl2))}

                </Tab>')

                )b



SQL05用CTE:



;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)

,Roy2 as

(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 

union all 

select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)

select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)





生成结果:

/*

Col1        COl2

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

1           a,b,c

2           d,e

3           f



(3 行受影响)

*/

水族杰纶 2008-11-10

打赏
举报

--參考這裡

SQL code问题描述：

无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，

  所以，当我们在处理下列要求时，会比较麻烦：

有表tb, 如下：

id    value

----- ------

1     aa

1     bb

2     aaa

2     bbb

2     ccc

需要得到结果：

id     values

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

1      aa,bb

2      aaa,bbb,ccc

即， group by id, 求 value 的和（字符串相加）



1. 旧的解决方法



-- 1. 创建处理函数

CREATE FUNCTION dbo.f_str(@id int)

RETURNS varchar(8000)

AS

BEGIN

    DECLARE @r varchar(8000)

    SET @r = ''

    SELECT @r = @r + ',' + value

    FROM tb

    WHERE id=@id

    RETURN STUFF(@r, 1, 1, '')

END

GO

-- 调用函数



SELECt id, values=dbo.f_str(id) 

FROM tb 

GROUP BY id



-- 2. 新的解决方法 

-- 示例数据

DECLARE @t TABLE(id int, value varchar(10))

INSERT @t SELECT 1, 'aa'

UNION ALL SELECT 1, 'bb'

UNION ALL SELECT 2, 'aaa'

UNION ALL SELECT 2, 'bbb'

UNION ALL SELECT 2, 'ccc'



-- 查询处理

SELECT *

FROM(

    SELECT DISTINCT 

        id

    FROM @t

)A

OUTER APPLY(

    SELECT 

        [values]= STUFF(REPLACE(REPLACE(

            (

                SELECT value FROM @t N

                WHERE id = A.id

                FOR XML AUTO

            ), '<N value="', ','), '"/>', ''), 1, 1, '')

)N



/*--结果

id          values

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

1           aa,bb

2           aaa,bbb,ccc

(2 行受影响)

--*/



--各种字符串分函数



--3.3.1 使用游标法进行字符串合并处理的示例。

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'b',3



--合并处理

--定义结果集表变量

DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))



--定义游标并进行合并处理

DECLARE tb CURSOR LOCAL

FOR

SELECT col1,col2 FROM tb ORDER BY  col1,col2

DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)

OPEN tb

FETCH tb INTO @col1,@col2

SELECT @col1_old=@col1,@s=''

WHILE @@FETCH_STATUS=0

BEGIN

    IF @col1=@col1_old

        SELECT @s=@s+','+CAST(@col2 as varchar)

    ELSE

    BEGIN

        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))

        SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1

    END

    FETCH tb INTO @col1,@col2

END

INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))

CLOSE tb

DEALLOCATE tb

--显示结果并删除测试数据

SELECT * FROM @t

DROP TABLE tb

/*--结果

col1       col2

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

a          1,2

b          1,2,3

--*/

GO





/*==============================================*/





--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'b',3

GO



--合并处理函数

CREATE FUNCTION dbo.f_str(@col1 varchar(10))

RETURNS varchar(100)

AS

BEGIN

    DECLARE @re varchar(100)

    SET @re=''

    SELECT @re=@re+','+CAST(col2 as varchar)

    FROM tb

    WHERE col1=@col1

    RETURN(STUFF(@re,1,1,''))

END

GO



--调用函数

SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1

--删除测试

DROP TABLE tb

DROP FUNCTION f_str

/*--结果

col1       col2

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

a          1,2

b          1,2,3

--*/

GO



/*==============================================*/





--3.3.3 使用临时表实现字符串合并处理的示例

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'b',3



--合并处理

SELECT col1,col2=CAST(col2 as varchar(100)) 

INTO #t FROM tb

ORDER BY col1,col2

DECLARE @col1 varchar(10),@col2 varchar(100)

UPDATE #t SET 

    @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,

    @col1=col1,

    col2=@col2

SELECT * FROM #t

/*--更新处理后的临时表

col1       col2

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

a          1

a          1,2

b          1

b          1,2

b          1,2,3

--*/

--得到最终结果

SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1

/*--结果

col1       col2

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

a          1,2

b          1,2,3

--*/

--删除测试

DROP TABLE tb,#t

GO





/*==============================================*/



--3.3.4.1 每组 <=2 条记录的合并

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'c',3



--合并处理

SELECT col1,

    col2=CAST(MIN(col2) as varchar)

        +CASE 

            WHEN COUNT(*)=1 THEN ''

            ELSE ','+CAST(MAX(col2) as varchar)

        END

FROM tb

GROUP BY col1

DROP TABLE tb

/*--结果

col1       col2      

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

a          1,2

b          1,2

c          3

--*/



--3.3.4.2 每组 <=3 条记录的合并

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'b',3

UNION ALL SELECT 'c',3



--合并处理

SELECT col1,

    col2=CAST(MIN(col2) as varchar)

        +CASE 

            WHEN COUNT(*)=3 THEN ','

                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)

            ELSE ''

        END

        +CASE 

            WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)

            ELSE ''

        END

FROM tb a

GROUP BY col1

DROP TABLE tb

/*--结果

col1       col2

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

a          1,2

b          1,2,3

c          3

--*/

GO

if not object_id('A') is null

    drop table A

Go

Create table A([id] int,[cname] nvarchar(2))

Insert A

select 1,N'张三' union all

select 2,N'李四' union all

select 3,N'王五' union all

select 4,N'蔡六'

Go

--> --> 

 

if not object_id('B') is null

    drop table B

Go

Create table B([id] int,[cname] nvarchar(5))

Insert B

select 1,N'1,2,3' union all

select 2,N'3,4'

Go

create function F_str(@cname nvarchar(100))

returns nvarchar(100)

as

begin 

select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0

return @cname

end

go

select [id],dbo.F_str([cname])[cname] from B



id          cname

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

1           张三,李四,王五

2           王五,蔡六



(2 個資料列受到影響)

thncn 2008-11-10

打赏
举报

7楼的大哥，就是你写的这样，能不能再加上分组计算，类似于sum(...)...group by ...的方式呢？

fcuandy 2008-11-09

打赏
举报

参考
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

水族杰纶 2008-11-09

打赏
举报

[Quote=引用 5 楼 thncn 的回复:]

补充说明一下，我的表里有一列是序列号，怎么样可以把序列号的字符连接起来，而不是可转化为数字的那种

[/Quote]

--？这样？

if object_id('tb')is not null drop table tb

go

create table tb(ID int identity(1,1),tagid varchar(10) )

insert tb select '2008001'

insert tb select '2008002'

insert tb select '2008003'

insert tb select '2008004'

declare @s varchar(8000)

select @s=isnull(@s,'')+','+tagid from tb

select stuff(@s,1,1,'')

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

2008001,2008002,2008003,2008004



（所影响的行数为 1 行）

*/

chenjunsheep 2008-11-09

打赏
举报

我来报个道

thncn 2008-11-09

打赏
举报

补充说明一下，我的表里有一列是序列号，怎么样可以把序列号的字符连接起来，而不是可转化为数字的那种

水族杰纶 2008-11-09

打赏
举报

--字段连接？

select cast( A as varchar(10))+ cast( B as varchar(10))+ cast( C as varchar(10))from tb 

--还是每列都是数值，求和后连接

select cast(sum(A) as varchar(10))+','+cast(sum(B) as varchar(10))+','cast(sum(C) as varchar(10)) from tb

ws_hgo 2008-11-09

打赏
举报

用Cast转换之后在求和

liangCK 2008-11-09

打赏
举报

SELECT CAST(SUM(CASE WHEN ISNUMERIC(字段1)=1 THEN 字段1 ELSE 0 END) AS VARCHAR)+' '+CAST(SUM(CASE WHEN ISNUMERIC(字段2)=1 THEN 字段2 ELSE 0 END) AS VARCHAR)

FROM tb

liangCK 2008-11-09

打赏
举报

字符型数据求和?

求字符型字段的求和公式？

相关推荐