有这样一张表ID Prefix Name
A HA TianJin
B HA BeiJing
C HA ShangHai
D HA HongKong
E HA AoMen
F MI BaoTou
G BR LanZhou
H OA Canada在MySQL里,我用这条语句SELECT prefix,
CONCAT(prefix,' (', GROUP_CONCAT(clinicname SEPARATOR ', '), ')') AS valueString
FROM clinic GROUP BY prefix可以得到如下效果prefix valueString
BR BR (LanZhou)
HA HA (TianJin,BeiJing,ShangHai,HongKong,AoMen)
MI MI (BaoTou)
OA OA (Canada)用SQL Server 如何得到相同的效果? 该用什么函数?MySQL 是我用过的最难用的数据库,天,快疯掉了多谢
A HA TianJin
B HA BeiJing
C HA ShangHai
D HA HongKong
E HA AoMen
F MI BaoTou
G BR LanZhou
H OA Canada在MySQL里,我用这条语句SELECT prefix,
CONCAT(prefix,' (', GROUP_CONCAT(clinicname SEPARATOR ', '), ')') AS valueString
FROM clinic GROUP BY prefix可以得到如下效果prefix valueString
BR BR (LanZhou)
HA HA (TianJin,BeiJing,ShangHai,HongKong,AoMen)
MI MI (BaoTou)
OA OA (Canada)用SQL Server 如何得到相同的效果? 该用什么函数?MySQL 是我用过的最难用的数据库,天,快疯掉了多谢
returns varchar(100)
as
begin
declare @back varchar(100)
set @back = ''
select @back = Name+',' from tst where Prefix = @prefix
set @back = @prefix + '('+left(@back,len(@back)-1)+')'
return @back
end
create table tst
(
id varchar(10),
prefix varchar(10),
name varchar(20)
)
insert into tst select 'a','ha','tianjin'select prefix,valueString=dbo.concat_for(prefix) from tst clinic group by prefix
drop table tst
/*
prefix valueString
---------- ----------------------------------------------------------------------------------------------------
ha ha(tianjin)(所影响的行数为 1 行)
*/
只能通过自己去手工连接!