關於select查詢的寫法
我有一張表:TEST(Menu_Name varchar(3), Action_Name varchar(4) )
里面的資料顯示為
Menu_Name Action_Name
A01 新增
A01 修改
A01 刪除
A01 審核
B01 新增
B01 修改
C01 刪除
C01 審核
現在我想用SELECT查詢顯示結果為:
Menu_Name Action_Name
A01 新增;修改;刪除;審核
B01 新增;修改
C01 刪除;審核
請大家指教一二,感激不盡!
[解决办法]
create table T(Menu_Name varchar(3), Action_Name varchar(4) )
insert T select 'A01 ', '新增 '
union all select 'A01 ', '修改 '
union all select 'A01 ', '刪除 '
union all select 'A01 ', '審核 '
union all select 'B01 ', '新增 '
union all select 'B01 ', '修改 '
union all select 'C01 ', '刪除 '
union all select 'C01 ', '審核 '
create function fun(@Menu_Name char(3))
returns varchar(100)
as
begin
declare @re varchar(1000)
set @re= ' '
select @re=@re+Action_Name+ '; ' from T where Menu_Name=@Menu_Name
select @re=left(@re, len(@re)-1)
return @re
end
select distinct Menu_Name, dbo.fun(Menu_Name) as Action_Name from T
--result
Menu_Name Action_Name
--------- ----------------------------------------------------------------
A01 新增;修改;刪除;審核
B01 新增;修改
C01 刪除;審核
(3 row(s) affected)
[解决办法]
create table test(Menu_Name varchar(3), Action_Name varchar(4))
insert into test(Menu_Name , Action_Name ) values ( 'A01 ', '新增 ')
insert into test(Menu_Name , Action_Name ) values ( 'A01 ', '修改 ' )
insert into test(Menu_Name , Action_Name ) values ( 'A01 ', '刪除 ')
insert into test(Menu_Name , Action_Name ) values ( 'A01 ', '審核 ')
insert into test(Menu_Name , Action_Name )values ( 'B01 ', '新增 ')
insert into test(Menu_Name , Action_Name )values ( 'B01 ', '修改 ')
insert into test(Menu_Name , Action_Name )values ( 'C01 ', '刪除 ')
insert into test(Menu_Name , Action_Name ) values ( 'C01 ', '審核 ')
go
create function f_hb(@a varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + '; ' + Action_Name from test where Menu_Name = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct Menu_Name,dbo.f_hb(Menu_Name) as Action_Name from test
drop table test
drop function f_hb
结果:
Menu_Name Action_Name
--------- -------------------
A01 新增;修改;刪除;審核
B01 新增;修改
C01 刪除;審核
（所影响的行数为 3 行）
[解决办法]
--臨時表法
select Menu_Name, Action_Name=cast(Action_Name as varchar(100)) into #T from T order by Menu_Name
declare @Menu_Name varchar(3), @Action_Name varchar(100)
update #T set
@Action_Name=case when Menu_Name=@Menu_Name then @Action_Name+ '; '+Action_Name else Action_Name end,
@Menu_Name=Menu_Name,
Action_Name=@Action_Name
select Menu_Name, max(Action_Name) as Action_Name from #T group by Menu_Name
--result
Menu_Name Action_Name
--------- ----------------------------------------------------------------
A01 新增;修改;刪除;審核
B01 新增;修改
C01 刪除;審核
(3 row(s) affected)
[解决办法]
create table T(Menu_Name varchar(3), Action_Name varchar(4) )
insert T select 'A01 ', '新增 '
union all select 'A01 ', '修改 '
union all select 'A01 ', '刪除 '
union all select 'A01 ', '審核 '
union all select 'B01 ', '新增 '
union all select 'B01 ', '修改 '
union all select 'C01 ', '刪除 '
union all select 'C01 ', '審核 '
alter table t add 显示列 varchar(20)--新增显示列
go
declare @ta table(id int identity(1,1),Menu_Name varchar(3),Action_Name varchar(4))
insert @ta
select Menu_Name,Action_Name from T
while exists(select 1 from @ta)
begin
update t
set 显示列=isnull(显示列+ ', ', ' ')+ta.Action_Name
from @ta ta,t
where t.Menu_Name=ta.Menu_Name and
not exists(select 1 from @ta where Menu_Name=ta.Menu_Name and id <ta.id)
delete ta
from @ta ta
where not exists(select 1 from @ta where Menu_Name=ta.Menu_Name and id <ta.id)
end
go
select distinct Menu_Name,显示列 from t--查询
go
alter table t drop column 显示列--删除
Menu_Name 显示列
--------- --------------------
A01 新增,修改,刪除,審核
B01 新增,修改
C01 刪除,審核
（所影响的行数为 3 行）
