存储过程里怎么用order by 不同的条件。

2008-10-09 03:17:10 261 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

MySQL中order by的原理目录示例表示例表以下面这张表为例，看一下 order by 的工作流程 CREATE TABLE `user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `city` varchar(16) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, `addr` varchar(128) NOT NULL, PRIMARY KEY (`id`), KEY `ci 阅读详情

前面跟一个参数过来，用户选择的@orderby int
假设orderby的值为1，执行order by price1 desc
orderby值为2时，执行order by price1 asc
orderby 值为空时，即用户不选择条件排序，那么order by id desc

CREATE procedure ProList
@myname varchar(100),
@orderby int
as
select id,myname,price from [pro]
where online=1
and myname like '%'+@myname+'%'

这儿排序怎么写

GO

...全文

面试:hive的优化, 4个by, 存储过程和自定义函数答：有用过 Oracle 的自定义函数，其实就是将某些查询或者计算方法封装成一个函数，类似于常用的 substr、replace 这样，可以避免重复开发，简化代码，提高工作的效率。，即对查询结果的所有数据进行排序。答：在plsql中使用exception，在exception中把过程数据记录到错误日志表，然后对主过程的事务做回滚，并且针对异常我们会记录异常的日志，方便后续问题的分析。对数据的ETL过程，对SQL语句进行优化，一些需要重复调用的过程，对数据库进行复杂操作，比如增删改，这些都需要用到存储过程。阅读详情

luancode

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

水族杰纶 2008-10-09

打赏
举报

[Quote=引用 7 楼 wufeng4552 的回复:]
SQL codeCREATE procedure ProList
@myname varchar(100),
@orderby int
as
declare @s varchar(5000)
set @s='select id,myname,price from [pro] where online=1 and myname like '''+ '%'+@myname+'%'''
if @orderby=1
exec(@s+' order by price1 desc')
else if @orderby=2
exec(@s+ ' order by price1 asc')
else
exec (@s+'order by id desc')…
[/Quote]
把你的做成動態不可以嗎
Print @S
看看有沒有語法錯誤

luancode 2008-10-09

打赏
举报

CREATE procedure ProList
@bid varchar(20),
@sid varchar(20),
@myname varchar(100),
@myitem varchar(100),
@price_1 int,
@price_2 int,
@commend int,
@orderby int
as
select id,myname,img,price1,commend,online,hit,addtime,kucun from [pro]
where online=1
and (',')+bid like '%,'+@bid+'%'
and (',')+sid like '%,'+@sid+'%'
and myname like '%'+@myname+'%'
and myitem like '%'+@myitem+'%'
and price1>=(case @price_1 when 0 then price1 else @price_1 end)
and price2<=(case @price_2 when 0 then price2 else @price_2 end)
and commend=@commend
order by (case @orderby when 1 then price1 when 2 then id else addtime end) desc
GO

这样执行正确的，可是达不到我想要的效果，price asc.哎

水族杰纶 2008-10-09

打赏
举报

[Quote=引用 9 楼 luancode 的回复:]
CREATE procedure ProList
@bid varchar(20),
@sid varchar(20),
@myname varchar(100),
@myitem varchar(100),
@price_1 int,
@price_2 int,
@commend int,
@orderby int
as
declare @s varchar(5000)
set @s='select id,myname,img,price1,commend,online,hit,addtime,kucun from [pro]
where online=1
and ('','')+bid like '+'%,'+@bid+'%''
and ('','')+sid like '+'%,'+@sid+'%''
and mynam…
[/Quote]
這個問題應該不是存儲過程的
你先在查詢分析器調試看看~~

luancode 2008-10-09

打赏
举报

CREATE procedure ProList
@bid varchar(20),
@sid varchar(20),
@myname varchar(100),
@myitem varchar(100),
@price_1 int,
@price_2 int,
@commend int,
@orderby int
as
declare @s varchar(5000)
set @s='select id,myname,img,price1,commend,online,hit,addtime,kucun from [pro]
where online=1
and ('','')+bid like '+'%,'+@bid+'%''
and ('','')+sid like '+'%,'+@sid+'%''
and myname like '+'%'+@myname+'%''
and myitem like '+'%'+@myitem+'%''
and price1>=(case @price_1 when 0 then price1 else @price_1 end)
and price2<=(case @price_2 when 0 then price2 else @price_2 end)
and commend=@commend'
if @orderby=1
exec(@s+' order by price1 desc')
else if @orderby=2
exec(@s+ ' order by price1 asc')
else
exec (@s+'order by id desc')

这是我的全部代码。
这样写后，提示：不允许对具有不止一条 SELECT 语句的远程存储过程或存储过程使用服务器游标。请使用默认结果集或客户端游标。我在分页那儿调用：set rs=cmd.execute 这行提示错误。
dobear_0922，你那样写，我的代码是不是会很多？

dobear_0922 2008-10-09

打赏
举报

CREATE procedure ProList 

      @myname varchar(100), 

      @orderby int 

as 

begin

    if @orderby=1

		select id,myname,price from [pro] 

		where online=1 

			and myname like '%'+@myname+'%' 

		order by price1 desc        

    else if @orderby=2

        select id,myname,price from [pro] 

		where online=1 

			and myname like '%'+@myname+'%' 

		order by price1 

    else

		select id,myname,price from [pro] 

		where online=1 

			and myname like '%'+@myname+'%' 

		order by id desc  

end

go

水族杰纶 2008-10-09

打赏
举报

CREATE procedure ProList 

      @myname varchar(100), 

      @orderby int 

as 

    declare @s varchar(5000)

    set @s='select id,myname,price from [pro] where online=1 and myname like '''+ '%'+@myname+'%''' 

    if @orderby=1

        exec(@s+'  order by price1 desc')

    else if @orderby=2

        exec(@s+ ' order by price1 asc')

    else 

        exec (@s+'order by id desc')

luancode 2008-10-09

打赏
举报

[Quote=引用 5 楼 nalnait 的回复:]
CREATE procedure ProList
@myname varchar(100),
@orderby int
as
select id,myname,price from [pro]
where online=1
and myname like '%'+@myname+'%'

--这儿排序怎么写
order by case when @orderby is null then id else price1 end desc

GO
[/Quote]
还有一种情况，price1升序怎么排呢？一共有三种.
orderby的值为1，执行order by price1 desc
orderby值为2时，执行order by price1 asc
orderby 值为空时，即用户不选择条件排序，那么order by id desc

nalnait 2008-10-09

打赏
举报

CREATE procedure ProList
@myname varchar(100),
@orderby int
as
select id,myname,price from [pro]
where online=1
and myname like '%'+@myname+'%'

--这儿排序怎么写
order by case when @orderby is null then id else price1 end desc

GO

mugua604 2008-10-09

打赏
举报



CREATE procedure ProList 

@orderby int 

as 

begin

select top 10 id,type,subject from [table1]   order by   case @orderby when 1 then  id  when  2  then  type   end desc

end

luancode 2008-10-09

打赏
举报

我不是根据不同的字段排序，是两种情况都有，当orderby不为空时，根据同一字段price升序或降序，当orderby为空时，根据另一字段id desc

Lori268 2008-10-09

打赏
举报

自己动手改自己的吧

Lori268 2008-10-09

打赏
举报



CREATE procedure ProList 

@orderby int 

as 

begin

select top 10 id,type,subject from [table1]   order by   case @orderby when 1 then  id  when  2  then  type   end desc

end

存储过程里怎么用order by 不同的条件。

相关推荐