一个SQL语句的写法,求思路,谢谢

TheEighthDay 2010-06-01 10:31:55
表有列col1,col2,col3
group by col1,col2,然后每个元组的列col3拼接在一起,谁能给个思路?

比如:
col1, col2, col3
小明 2班 品德好
小明 2班 身体差
小红 2班 品德好
小绿 1班 身体好

能够得到这样的结果集:

小明 2班 品德好+身体差
小红 2班 品德好
小绿 1班 身体好

...全文
103 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
--小F-- 2010-06-01
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2010-06-01 22:40:57

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

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

--> 测试数据：[tb]

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

go 

create table [tb]([col1] varchar(4),[col2] varchar(3),[col3] varchar(6))

insert [tb]

select '小明','2班','品德好' union all

select '小明','2班','身体差' union all

select '小红','2班','品德好' union all

select '小绿','1班','身体好'

--------------开始查询--------------------------

select

 col1,col2, [col3]=stuff((select '+'+[col3] from tb t where col1=tb.col1 for xml path('')), 1, 1, '') 

from

 tb 

group by

 col1,col2 

----------------结果----------------------------

/* col1 col2 col3

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

小红   2班   品德好

小绿   1班   身体好

小明   2班   品德好+身体差



(3 行受影响)



*/
--小F-- 2010-06-01
  • 打赏
  • 举报
 回复 
合并列值 

--*******************************************************************************************

表结构，数据如下： 

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. 旧的解决方法(在sql server 2000中只能用函数解决。) 

--=============================================================================

create table tb(id int, value varchar(10)) 

insert into tb values(1, 'aa') 

insert into tb values(1, 'bb') 

insert into tb values(2, 'aaa') 

insert into tb values(2, 'bbb') 

insert into tb values(2, 'ccc') 

go 

--1. 创建处理函数 

CREATE FUNCTION dbo.f_strUnite(@id int) 

RETURNS varchar(8000) 

AS 

BEGIN 

    DECLARE @str varchar(8000) 

    SET @str = '' 

    SELECT @str = @str + ',' + value FROM tb WHERE id=@id 

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

END 

GO 

-- 调用函数 

SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id 

drop table tb 

drop function dbo.f_strUnite 

go

/* 

id          value      

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

1          aa,bb 

2          aaa,bbb,ccc 

（所影响的行数为 2 行） 

*/ 

--===================================================================================

2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) 

create table tb(id int, value varchar(10)) 

insert into tb values(1, 'aa') 

insert into tb values(1, 'bb') 

insert into tb values(2, 'aaa') 

insert into tb values(2, 'bbb') 

insert into tb values(2, 'ccc') 

go 

-- 查询处理 

SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( 

        SELECT [values]= STUFF(REPLACE(REPLACE( 

            ( 

                SELECT value FROM tb N 

                WHERE id = A.id 

                FOR XML AUTO 

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

)N 

drop table tb 



/* 

id          values 

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

1          aa,bb 

2          aaa,bbb,ccc 



(2 行受影响) 

*/ 



--SQL2005中的方法2 

create table tb(id int, value varchar(10)) 

insert into tb values(1, 'aa') 

insert into tb values(1, 'bb') 

insert into tb values(2, 'aaa') 

insert into tb values(2, 'bbb') 

insert into tb values(2, 'ccc') 

go 



select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') 

from tb 

group by id 



/* 

id          values 

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

1          aa,bb 

2          aaa,bbb,ccc 



(2 row(s) affected) 



*/ 



drop table tb
pt1314917 2010-06-01
  • 打赏
  • 举报
 回复 


--合并分拆表

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

合并分拆表数据



整理人：中国风(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 行受影响)

*/
TheEighthDay 2010-06-01
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 pt1314917 的回复:]

引用 2 楼 neu_kt 的回复:
引用 1 楼 pt1314917 的回复:

SQL code

create function getstr(@col1 varchar(20))
returns varchar(100)
as
begin
declare @sql varchar(100)
select @sql=isnull(@sql+'+','')+col3 fr……
[/Quote]

唉...
数据库再升级就给升没了,算了,保有一个文档好了
我先试下,好用的话就结贴!
pt1314917 2010-06-01
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 neu_kt 的回复:]
引用 1 楼 pt1314917 的回复:

SQL code

create function getstr(@col1 varchar(20))
returns varchar(100)
as
begin
declare @sql varchar(100)
select @sql=isnull(@sql+'+','')+col3 from 表名 where col1=@col……
[/Quote]
是函数，sql2000中只能用函数去实现。。
TheEighthDay 2010-06-01
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 pt1314917 的回复:]

SQL code

create function getstr(@col1 varchar(20))
returns varchar(100)
as
begin
declare @sql varchar(100)
select @sql=isnull(@sql+'+','')+col3 from 表名 where col1=@col1
return ……
[/Quote]
非常感谢!有没有纯SQL版本的,上面的是存储过程吧
pt1314917 2010-06-01
  • 打赏
  • 举报
 回复 


create function getstr(@col1 varchar(20))

returns varchar(100)

as

begin

       declare @sql varchar(100)

       select @sql=isnull(@sql+'+','')+col3 from 表名 where col1=@col1

       return @sql

end

go



--调用：

select distinct col1,col2,dbo.getstr(col1) from 表名
表添加字段的SQL语句写法
表添加字段的SQL语句写法 SQL语句可以实现诸多我们需要实现的功能，下面介绍的SQL语句用于在数据表中添加一个字段，供您参考学习。 作者：佚名来源：互联网|2010-09-28 15:07  移动端  收藏   分享 如果要在数据表中添加一个字段，应该如何表示呢？下面就为您介绍表添加字段的SQL语句的写法，希望可以让您对SQL语句有更深的认
主流常见关系数据库分页sql语句写法limit、offset、fetch、rownum。MySQL、PostgreSQL、SQLite、Oracle、DB2、SQL Server。
select * from demo limit 10 offset 20; select * from demo limit 20, 10; -- PostgreSQL不支持该写法 select * from demo offset 20 rows fetch next 10 rows only; select * from demo fetch next 10 rows only;
数据库：原生sql语句写法
1.SQL语句 1.1什么是SQL语句： SQL:结构化查询语言。关系数据库语言的国际标准。 各个数据库厂商都支持ISO的SQL标准：如普通话 各个数据库厂商在标准基础做了自己的扩展：如方言 1.2 SQl分类 数据定义语言：DDL（Data Definiton Language）,用来定义数据库对象。关键字：数据库database，表table，列column等。关键字：创建crea...
一条sql语句查询多个条件的写法
1.一条sql语句查询多个条件的写法 (1)sql语句的使用 SELECT p1.count1 AS todayAdd,p2.count2 AS todayReturns,p3.count3 AS sameMonthAdd,p4.count4 AS lastMonthAdd FROM (SELECT COUNT(create_time)count1 FROM project_info WHERE TO_DAYS(create_time)=TO_DAYS(SYSDATE())) AS p
标准Sql语句的写法（一）
一、 简单查询 　　简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。 　　例如，下面的语句查询testtable表中姓名为"张三"的nickname字段和email字段。  　　SELECT nickname,email 　　FROM testtable 　　WHERE name='张三'
MS-SQL Server

34,838

社区成员

254,631

社区内容

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

加载中

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

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

+ 用AI写文章