1、mysql4.1及以上版本用group_concat函数实现,sql server一般用一个自定义函数实现mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| tmp1 |
+----------------+
2 rows in set (0.31 sec)mysql> create table t1(no int not null,name varchar(10));
Query OK, 0 rows affected (0.51 sec)mysql> insert into t1 values
-> (1,'a'),(1,'b'),(1,'c'),(1,'d'),(2,'a'),(2,'b'),(2,'c'),(3,'d');
Query OK, 8 rows affected (0.13 sec)
Records: 8 Duplicates: 0 Warnings: 0mysql> select * from t1;
+----+------+
| no | name |
+----+------+
| 1 | a |
| 1 | b |
| 1 | c |
| 1 | d |
| 2 | a |
| 2 | b |
| 2 | c |
| 3 | d |
+----+------+
8 rows in set (0.16 sec)mysql> select no,group_concat(name separator ' ') from t1 group by no;
+----+----------------------------------+
| no | group_concat(name separator ' ') |
+----+----------------------------------+
| 1 | a b c d |
| 2 | a b c |
| 3 | d |
+----+----------------------------------+
3 rows in set (1.36 sec)
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| tmp1 |
+----------------+
2 rows in set (0.31 sec)mysql> create table t1(no int not null,name varchar(10));
Query OK, 0 rows affected (0.51 sec)mysql> insert into t1 values
-> (1,'a'),(1,'b'),(1,'c'),(1,'d'),(2,'a'),(2,'b'),(2,'c'),(3,'d');
Query OK, 8 rows affected (0.13 sec)
Records: 8 Duplicates: 0 Warnings: 0mysql> select * from t1;
+----+------+
| no | name |
+----+------+
| 1 | a |
| 1 | b |
| 1 | c |
| 1 | d |
| 2 | a |
| 2 | b |
| 2 | c |
| 3 | d |
+----+------+
8 rows in set (0.16 sec)mysql> select no,group_concat(name separator ' ') from t1 group by no;
+----+----------------------------------+
| no | group_concat(name separator ' ') |
+----+----------------------------------+
| 1 | a b c d |
| 2 | a b c |
| 3 | d |
+----+----------------------------------+
3 rows in set (1.36 sec)
create table t(ID int,content varchar(20))
insert into t select 1,'aa'
insert into t select 2,'bb'
insert into t select 3,'cc'
insert into t select 1,'dd'
insert into t select 2,'ee'
insert into t select 3,'ff'
go--创建用户定义函数
create function f_str(@ID int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + content from t where id=@id
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行查询
select ID,content=dbo.f_str(ID) from t group by ID--输出结果
ID content
---- -------
1 aa,dd
2 bb,ee
3 cc,ff