＋设为首页 ＋添加到收藏夹
求一更新的sql语句
表A
store_id remark
11 aaa
11 bbb
11 aa
11 aaa
12 yy
12 xy
要求：按store_id 将remark字段(文本)汇总，同样或已包含的值过滤掉
结果
store_id remark
11 [aaa][bbb]
12 [yy][xy]
CREATE FUNCTION dbo.uf_xd (@parm int)
RETURNS varchar(2000) AS
BEGIN
declare @return varchar(2000)
select @return=isnull(@return,)+[+isnull(remark,)+] from 表A where store_id=@parm
return (@return)
END
select dbo.uf_xd(sorte_id) from 表A group by sort_id
select distinct * from table A into
where not exists(select 1 from table where remark like A.remark+_%)
--过虑掉重复和包含值
11 aaa
11 bbb
12 xy
12 yy
create function getresult(@store_id varchar(10))
returns varchar(1000)
as
begin
declare @result varchar(100)
set @result=[
select @result=@result+remark+] [ from
(select distinct * from rr A
where not exists(select 1 from rr where remark like A.remark+_%))a
where a.store_id =@store_id
return left(@result,len(@result)-1)
end
select distinct store_id ,dbo.getresult(store_id ) from
(select distinct * from rr A
where not exists(select 1 from rr where remark like A.remark+_%))a
--rr换成table名
create function getstr(@content int)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=
select @str=@str+[+rtrim(remark)+] from a where store_id=@content group by remark
return @str
end
go
语句：
select distinct store_id,dbo.getstr(store_id) from a
select distinct * from table A --没有into的 打错了 sorry
where not exists(select 1 from table where remark like A.remark+_%)
--过虑掉重复和包含值
create function getresult(@store_id varchar(10))
returns varchar(1000)
as
begin
declare @result varchar(100)
set @result=[
select @result=@result+remark+] [ from
(select distinct * from rr A
where not exists(select 1 from rr where remark like A.remark+_%))a
where a.store_id =@store_id
return left(@result,len(@result)-1)
end
go
select distinct store_id ,dbo.getresult(store_id ) from
(select distinct * from rr A
where not exists(select 1 from rr where remark like A.remark+_%))a
--rr换成table名
