合并表法

最新推荐文章于 2023-06-05 19:59:46 发布

iteye_3619

最新推荐文章于 2023-06-05 19:59:46 发布

阅读量87

点赞数

文章标签： C C++ C#

/*合并法系列*/
--合并法(函数)
createtabletb1
(
col1varchar(10),

col2int
)

insertintotb1select'a',1

unionallselect'a',2

unionallselect'b',1

unionallselect'b',2

unionallselect'b',3
createfunctiondbo.FC_Str(@col1varchar(100))
returnsvarchar(100)

as
begin
declare@ivarchar(100)

set@i=''

select@i=@i+','+cast(col2asvarchar)fromtb1wherecol1=@col1

return(stuff(@i,1,1,''))
end
selectcol1,dbo.FC_Str(col1)fromtb1groupbycol1
--固定行合并法
createtable#tb
(
col1varchar(10),

col2int
)

insertinto#tbselect'a',1

unionallselect'a',2

unionallselect'b',1

unionallselect'b',2

unionallselect'c',3
selectcol1,

col2=cast(min(col2)asvarchar)+

casewhencount(*)=1then''

else+','+cast(max(col2)asvarchar)endfrom#tb
groupbycol1
--临时表合并法

ifobject_id('tb')isnotnulldroptabletb
createtabletb
(
col1varchar(10),

col2int
)

insertintotbselect'a',1

unionallselect'a',2

unionallselect'b',1

unionallselect'b',2

unionallselect'b',3

selectcol1,col2=cast(col2asvarchar(100))into#t1fromtborderbycol1,col2
declare@col1varchar(20)
declare@col2varchar(100)

update#t1set@col2=

casewhen@col1=col1then@col2+','+col2

elsecol2end,
@col1=col1,
col2=@col2
select*from#t1
selectcol1,col2=max(col2)from#t1groupbycol1