统计表取值问题

xiangchengboy 2011-07-20 09:08:41
业务模型：统计表前半行为人员信息，后半行为合同信息，因为一个人员会对应多条合同信息，按状态分类显示。在数据库表A中保存有每一条合同信息，信息字段包括身份证，合同状态等信息。
问题：怎么从表A中取出统计表信息
...全文
96 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
AcHerat 元老 2011-07-20
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 xiangchengboy 的回复:]
引用 3 楼 cd731107 的回复:

没看明白，借用牛人的话就是

最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?



如何更有效地在SQL Server论坛上提问
http://topic.csdn.net/u/20100716/19/6f132f16-20e4-418c-8dee-b99d5f86d320……
[/Quote]




有表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 個資料列受到影響)
--小F-- 2011-07-20
  • 打赏
  • 举报
 回复 
/****************************************************************************************************************************************************** 

合并分拆表数据 



整理人：中国风(Roy) 



日期:2008.06.06 

******************************************************************************************************************************************************/ 



--> --> (Roy)生成測試數據 



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 行受影响) 

*/
--小F-- 2011-07-20
  • 打赏
  • 举报
 回复
有测试数据么？？说出想要的结果
xiangchengboy 2011-07-20
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 cd731107 的回复:]

没看明白，借用牛人的话就是

最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?



如何更有效地在SQL Server论坛上提问
http://topic.csdn.net/u/20100716/19/6f132f16-20e4-418c-8dee-b99d5f86d320.html?75910
[/Quote]
数据：
张三 a
张三 b
张三 c

查找结果：
张三 a b c

就是想实现上述结果
cd731107 2011-07-20
  • 打赏
  • 举报
 回复
没看明白，借用牛人的话就是

最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?



如何更有效地在SQL Server论坛上提问
http://topic.csdn.net/u/20100716/19/6f132f16-20e4-418c-8dee-b99d5f86d320.html?75910
xiangchengboy 2011-07-20
  • 打赏
  • 举报
 回复
高手们帮忙啊
AcHerat 元老 2011-07-20
  • 打赏
  • 举报
 回复
请给出具体的表结构 数据 和 测试结果。

PS： 看到前半部后半部感觉楼主应该用 union all 来将多个select的结果集拼凑到一起。
EasyUI应用（项目实战）
jquery入门基础，jquery赋值取值，事件处理和绑定，提交数据，datagrid控件显示数据，分页，事件处理；Tabs控件动态创建多标签；datebox日期时间控件的赋值和取值；window弹出窗口应用；tree创建树形菜单，部门列表等,panel控件的使用；progressbar进度条的应用；dialog控件的使用；
SQL统计某一属性中不同取值的数量
如果一个数据库中的数据量比较大，我们需要选取最多的数据进行分析，可以使用以下语句来统计，然后将统计结果转化为dataframe格式，并写到本地的excel表格中： sql_num = "SELECT business_id, count(1) AS ...
【C语言】char类型的取值范围
每一个bit位可以存0和1两个数字，8个bit位一共有 2^8 种排列方法，即区间 [00000000, 11111111]首先...的话，表示出来的数据取值范围是 [0, 255]最终输出结果为 255，我们来分析一下为什么。，取值范围是 [-128，127]
pandas 打开有公式的excel表取值错误的解决办法
pandas 打开有公式的excel表取值错误的解决办法 pandas 打开有公式的表格，如果直接读取，会出现有公式的单元格为空或零的情况， 参见 https://www.cnblogs.com/ubuntu1987/p/11491727.html ...wb = openpyxl.load_...
如何用数据库表中的列名来设计统计图
在统计图中，绝大多数情况下，分类值与系列值都是来源于表中的数据，也就是说，基于数据库表中的记录来设计统计图。但有这样一种特殊情况，那就是统计图中的分类值与系列值是以数据库中的列名来取值的。那么这样的...
MS-SQL Server

34,575

社区成员

254,585

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章