关于order by的处理

[bartholomew4]

select top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
		select orderId,num,price,Amount,proId from
		(select top 30 orderId,num,price,Amount,proId from orderDetails ) a order by a.orderid desc)o
		inner join Product p on o.proId=p.Pro_ID order by o.orderid asc

消息 1033，级别 15，状态 1，第 3 行
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。



求解决办法

[快溜]

order by 只能在外层查询

[yubofighting]

select top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
        select orderId,num,price,Amount,proId from
        (select top 30 orderId,num,price,Amount,proId from orderDetails ) a )o
        inner join Product p on o.proId=p.Pro_ID order by orderId

[叶子]

你这个是视图吧？

--把order by 去掉
--再调用视图的时候加上

select * from yourview order by orderid asc

[AcHerat]

select top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
select orderId,num,price,Amount,proId from
(select top 30 orderId,num,price,Amount,proId from orderDetails ) a order by a.orderid desc)o
inner join Product p on o.proId=p.Pro_ID order by o.orderid asc

红色部分去掉！或者

select top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
        select top 30 orderId,num,price,Amount,proId from orderDetails order by orderid desc)o
        inner join Product p on o.proId=p.Pro_ID order by o.orderid asc

[bartholomew4]

[Quote=引用 2 楼 yubofighting 的回复:]

SQL code


select top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
select orderId,num,price,Amount,proId from
(select top 30 orderId,num,price,Amount,proId from……
[/Quote]
实现不了功能，我要的是TOP30里的最后10条

[AcHerat]

[Quote=引用 5 楼 bartholomew4 的回复:]

引用 2 楼 yubofighting 的回复:

SQL code


select top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
select orderId,num,price,Amount,proId from
(select top 30 orderId,num,price,A……
[/Quote]

看4楼SQL CODE里的！

[bartholomew4]

[Quote=引用 4 楼 acherat 的回复:]

select top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
select orderId,num,price,Amount,proId from
(select top 30 orderId,num,price,Amount,proId from orderDeta……
[/Quote]
后面的那不行，是把整个表先排序了在TOP 30，我要的是在不排序的情况下TOP30，再取21-30条记录

[bartholomew4]

[Quote=引用 2 楼 yubofighting 的回复:]

SQL code


select top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
select orderId,num,price,Amount,proId from
(select top 30 orderId,num,price,Amount,proId from……
[/Quote]不是视图，是表

[AcHerat]

select convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
        select top 10  orderId,num,price,Amount,proId from
        (select top 30 orderId,num,price,Amount,proId from orderDetails ) a order by a.orderid desc)o
        inner join Product p on o.proId=p.Pro_ID

[bartholomew4]

排序不正确，应该是按照ASC排列的，而且你的这个语句只能查到5条（我也不知道为什么）

[bluesmiler]

select  top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
        select top 30 orderId,num,price,Amount,proId from
        (select top 30 orderId,num,price,Amount,proId from orderDetails ) a order by a.orderid desc)o
        inner join Product p on o.proId=p.Pro_ID 

[bluesmiler]

试试这个
select top 10 convert(varchar(10),orderId),p.Pro_Name,num,price,Amount from (
        select top 30 orderId,num,price,Amount,proId from
        (select top 30 orderId,num,price,Amount,proId from orderDetails ) a order by a.orderid desc)o
        inner join Product p on o.proId=p.Pro_ID order by o.orderid asc

[bluesmiler]

你加order by 前面要指定top的，所以再加个top 30 上去，
不要直接select orderId,num,price,Amount,proId from (select top 30 orderId,num,price,Amount,proId from orderDetails ) a order by a.orderid desc