• 主页
  • 基础类
  • 应用实例
  • 新技术前沿

*************求一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
.....
...全文
148 点赞 收藏 4
写回复
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
回复 点赞

--写个自定义函数处理
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
回复 点赞
发动态
发帖子
MS-SQL Server
创建于2007-09-28

1.4w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区
社区公告
暂无公告