create function f_str(@ID int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + contentn from table
set @ret = stuff(@ret,1,1,'')
return @ret
end
select ID,dbo.f_str(ID) from table group by ID
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + contentn from table
set @ret = stuff(@ret,1,1,'')
return @ret
end
select ID,dbo.f_str(ID) from table group by ID
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
create table q
(ID int,
content varchar(20))
insert q
select 1,'aa'
union
select 2,'bb'
union
select 3,'cc'
union
select 1,'dd'
union
select 2,'ee'
union
select 3,'ff'
--------------
自定义函数create function GetC(@ID int)
Returns Nvarchar(2000)
AS
Begin
Declare @sql nvarchar(4000)
Set @sql=''
select @sql=@sql+','+content from q where id=@id
Return (Stuff(@sql,1,1,''))
End
----------
执行语句
select distinct id,dbo.GetC(id) as content from q
---------
执行结果id content
1 aa,dd
2 bb,ee
3 cc,ff
这是类似细目表合成为合成表的问题,目前通用的时这种处理方法(采用辅助函数,再groupby。其中的函数跟表有关联,不能通用)。
相对应的合成表转化为细目表。就是由这个结果返回成原来的记录,处理方法为:增加带自增型id的辅助表,来实现。
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+content from table1 where id=@a
return(stuff(@r,1,1,''))
end
go
--调用实现查询
select id,content=dbo.f1(id) from table1 group by id
完整句法如下:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
[SEPARATOR str_val])这个函数在 MySQL 4.1 中被加入。函数返回一个字符串结果,该结果由分组中的值连接组合而成:
mysql> SELECT student_name,
-> GROUP_CONCAT(test_score)
-> FROM student
-> GROUP BY student_name;
or
mysql> SELECT student_name,
-> GROUP_CONCAT(DISTINCT test_score
-> ORDER BY test_score DESC SEPARATOR " ")
-> FROM student
-> GROUP BY student_name;在 MySQL 中,你可以得到表达式结合体的连结值。通过使用 DISTINCT 可以排除重复值。如果希望对结果中的值进行排序,可以使用 ORDER BY 子句。为了以倒序排序,可以在 ORDER BY 子句中用于排序的列名后添加一个 DESC (递减 descending) 关键词。缺省为升序;这也可以通过使用 ASC 关键词明确指定。 SEPARATOR 是一个字符串值,它被用于插入到结果值中。缺省为一个逗号 (",")。你可以通过指定 SEPARATOR "" 完全地移除这个分隔符。 在你的配置中,通过变量 group_concat_max_len 要以设置一个最大的长度。在运行时执行的句法如下:
SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;如果最大长度被设置,结果值被剪切到这个最大长度。 GROUP_CONCAT() 函数是一个增强的 Sybase SQL Anywhere 支持的基本 LIST() 函数。如果只有一个列,并且没有其它选项被指定,GROUP_CONCAT() 是向后兼容有极大限制的 LIST() 函数。 LIST() 有一个缺省的排序次序。
示例(译者注): mysql> CREATE TABLE `ta` (
-> `id` smallint(5) unsigned NOT NULL default '0',
-> `name` char(60) NOT NULL default '',
-> KEY `id` (`id`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO `ta` VALUES("1", "a"),("1", "b"),
-> ("1", "c"),("1", "d"),("2", "a"),
-> ("2", "b"),("2", "c"),("3", "d");
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0mysql> SELECT * FROM `ta`;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 1 | b |
| 1 | c |
| 1 | d |
| 2 | a |
| 2 | b |
| 2 | c |
| 3 | d |
+----+------+
8 rows in set (0.00 sec)mysql> SELECT `id`,
-> GROUP_CONCAT(`name`)
-> FROM `ta`
-> GROUP BY `id`;
+----+----------------------+
| id | GROUP_CONCAT(`name`) |
+----+----------------------+
| 1 | a c b d |
| 2 | a c b |
| 3 | d |
+----+----------------------+
3 rows in set (0.03 sec)# SEPARATOR 缺省是一个空格而不是一个逗号mysql> SELECT `id`,
-> GROUP_CONCAT(DISTINCT `name`
-> ORDER BY `name` DESC SEPARATOR ",") AS Result
-> FROM `ta`
-> GROUP BY `id`;
+----+---------+
| id | Result |
+----+---------+
| 1 | d,c,b,a |
| 2 | c,b,a |
| 3 | d |
+----+---------+
3 rows in set (0.00 sec)* 以上结果在 MySQL 4.1 中测试示例结束(译者注)
select id1 as [id],content1+','+content2 as content from
(select a.[id] as id1,a.content as content1,b.[id] as id2,b.content as content2 from @s a,@s b where a.[id]=b.[id]) as m
where id1=id2 and content1<content2测试结果为:
id content
----------- ---------
1 aa,dd
2 bb,ee
3 cc,ff