*************求一SQL语句**************

jilm168 2004-12-29 08:36:01
有如下表:
errcode location count
---------------------------------------------
15:23 U3 168
15:23 C5910 117
15:23 C1617 103

15:20 J4001 715
15:20 J4000 558
15:20 J7600 293

.......

有没有办法选择出这样的结果:
errcode desc
--------------------------------------------
15:23 U3*168;C5910*117;C1617*103
15:20 J4001*715;J4000*558;J7600*293
.....
...全文
187 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
zlp321002 2004-12-29
  • 打赏
  • 举报
回复
函数中不支持动态表,
把邹建的函数改写成存储过程就可以了!

Create proc p_str
(@errcode varchar(5),
@TableName varchar(20),
@outValue varchar(8000) output
)
as
begin
set @outValue=''
declare @sqls as nvarchar(4000)
set @sqls=''
set @sqls='select @r=@r+'';''+rtrim(location)+''*''+rtrim([count])
from '+@TableName+' where errcode='+@errcode+' '
exec sp_executesql @sqls,N'@r as varchar(8000) out', @outValue output
select @outValue
end
go
--测试
declare @output as varchar(8000)
exec p_str '参数','表名',@output output


jilm168 2004-12-29
  • 打赏
  • 举报
回复
为了普遍性,我希望把表的名字也作为参数传进去,怎么写呀?
631799 2004-12-29
  • 打赏
  • 举报
回复
select aa.errcode,aa.a+';'+bb.b+';'+cc.c from
(
select errcode,a=max(cast(location as varchar)+'*'+cast(count as varchar)) from tb group by errcode
) aa
left join
(
select errcode,b=min(cast(location as varchar)+'*'+cast(count as varchar)) from tb group by errcode
) bb
on aa.errcode=bb.errcode
left join
(
select errcode,c=


(select t.* from
(select cast(location as varchar)+'*'+cast(count as varchar) from tb) t where cast(location as varchar)+'*'+cast(count as varchar)
not in (select max(cast(location as varchar)+'*'+cast(count as varchar)) from tb group by errcode union all
select min(cast(location as varchar)+'*'+cast(count as varchar)) from tb group by errcode )

)

from tb group by errcode
) cc
on bb.errcode =cc.errcode
zjcxc 元老 2004-12-29
  • 打赏
  • 举报
回复

--写个自定义函数处理
create function f_str(@errcode char(5))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+';'+rtrim(location)+'*'+rtrim([count])
from 表 where errcode=@errcode
return(stuff(@r,1,1,''))
end
go

--调用实现查询
select errcode,[desc]=dbo.f_str(errcode) from 表 group by errcode

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧