如下表:
id name pid type
24366 aa 233 主产品
24377 bb 233 配件
24388 cc 233 赠品
24389 dd 233 赠品现在要得到如下的结果:
id name1 name2
24366 aa+bb cc+dd
规则:显示出主产品一行。如果是配件,则name1为:原商品名称(aa)+配件名称(bb)
如果是赠品,则name2为:赠品+赠品(cc+dd)差了很多资料,至今没做出来啊。请大家帮忙!谢谢了。
id name pid type
24366 aa 233 主产品
24377 bb 233 配件
24388 cc 233 赠品
24389 dd 233 赠品现在要得到如下的结果:
id name1 name2
24366 aa+bb cc+dd
规则:显示出主产品一行。如果是配件,则name1为:原商品名称(aa)+配件名称(bb)
如果是赠品,则name2为:赠品+赠品(cc+dd)差了很多资料,至今没做出来啊。请大家帮忙!谢谢了。
(id int,name varchar(10),pid int,type varchar(10))
insert #test1 select 24366,'aa', 233 ,'主产品'
insert #test1 select 24377,'bb', 233 ,'配件'
insert #test1 select 24388,'cc', 233 ,'赠品'
insert #test1 select 24389,'dd', 233 ,'赠品'
select a.id
,a=stuff((select '+'+name from #test1 b where a.pid=b.pid and b.type<>'赠品' order by id for xml path('')),1,1,'')
,b=stuff((select '+'+name from #test1 b where a.pid=b.pid and b.type='赠品' order by id for xml path('')),1,1,'')
from #test1 a
where type='主产品'
group by a.id,pid id a b
----------- ------------- ------------
24366 aa+bb cc+dd(1 行受影响)
drop table tb
go
create table tb (id int,name varchar(10) ,pid int ,type varchar(10))
insert tb select
24366, 'aa', 233, '主产品' union select
24377, 'bb', 233, '配件'union select
24388, 'cc', 233, '赠品'union select
24389, 'dd', 233 ,'赠品'
go
select ID=max(case when [type]='主产品' then ID END),
NAME1=stuff((select '+'+name from tb where a.pid=pid and type<>'赠品' order by id for xml path('')),1,1,''),
NAME2=stuff((select '+'+name from tb where a.pid=pid and type='赠品' order by id for xml path('')),1,1,'')
from tb a
group by pid
精华里多的是
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[name] varchar(2),[pid] int,[type] varchar(6))
insert [tb]
select 24366,'aa',233,'主产品' union all
select 24377,'bb',233,'配件' union all
select 24388,'cc',233,'赠品' union all
select 24389,'dd',233,'赠品'
--2000
--创建函数f1
if object_id('f1') is not null
drop function f1
go
create function f1(@pid int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+'+','')+[name] from tb where [pid]=@pid and [type] <>'赠品' order by id
return @S
end
go
--创建函数f2
if object_id('f2') is not null
drop function f2
go
create function f2(@pid int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+'+','')+[name] from tb where [pid]=@pid and [type] ='赠品' order by id
return @S
end
go select min(id) as id , name1=dbo.f1(pid), name2=dbo.f2(pid) from tb
group by dbo.f1(pid),dbo.f2(pid)
/*
id name1 name2
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
24366 aa+bb cc+dd(1 行受影响)*/
select
a.id,
a=stuff((select '+'+name from tb b where a.pid=b.pid and b.type<>'赠品' order by id for xml path('')),1,1,''),
b=stuff((select '+'+name from tb b where a.pid=b.pid and b.type='赠品' order by id for xml path('')),1,1,'')
from
tb a
where
type='主产品'
group by
a.id,pid