1.4w+
社区成员
25.3w+
社区内容
- 主页
- 基础类
- 应用实例
- 新技术前沿
*************求一SQL语句**************
有如下表:
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
.....
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
.....
...全文
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
把邹建的函数改写成存储过程就可以了！
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
(
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