帮忙看一下存储过程的问题？谢谢

godhuang 2005-07-19 03:34:18

1、下面用游标显示数据是一行一行显示的，有没有办法显示一个整体；
2、第一次写游标，帮忙优化一下

set nocount on
declare @sid varchar(20)
declare @sname varchar(100)
declare @lname varchar(100)
declare @sid2 varchar(20)
declare @sname2 varchar(100)
declare @lname2 varchar(100)

declare show cursor for
select a.supplyid,b.supplyname,a.licensename
from supply_license a left join supply b on a.supplyid = b.supplyid
order by a.supplyid

open show
FETCH NEXT FROM show into @sid,@sname,@lname
select @sid as sid,@sname as sname,@lname as lname
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM show into @sid2,@sname2,@lname2
if (@sid = @sid2 and @sname = @sname2 )
begin
select '' as sid,'' as sname,@lname2 as lname
set @sid = @sid2
set @sname = @sname2
set @lname = @lname2
end
else
begin
select @sid2 as sid,@sname2 as sname,@lname2 as lname
set @sid = @sid2
set @sname = @sname2
set @lname = @lname2
end
END

CLOSE show
DEALLOCATE show
set nocount off

本来写这个的目的如下面：
00001 沈阳统一企业有限公司生产许可证
00001 沈阳统一企业有限公司卫生许可证
00001 沈阳统一企业有限公司商标注册证
00001 沈阳统一企业有限公司营业执照
00001 沈阳统一企业有限公司企业标准
写语句变成
00001 沈阳统一企业有限公司生产许可证
卫生许可证
商标注册证
营业执照
企业标准
先谢谢各位

...全文

147 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

godhuang 2005-07-20

打赏
举报

licensename<a.licensename

这个是什么意思？

子陌红尘 2005-07-20

打赏
举报

try:
----------------------------------------------------------------------
select
supplyid_1 = (case
when exists(select 1 from supply_license where supplyid=a.supplyid and licensename<a.licensename)
then ''
else a.supplyid
end),
supplyname_1 = (case
when exists(select 1 from supply_license where supplyid=a.supplyid and licensename<a.licensename)
then ''
else b.supplyname
end),
a.licensename
from
supply_license a
left join
supply b
on
a.supplyid = b.supplyid
order by
a.supplyid,a.licensename

godhuang 2005-07-20