求优化 row_number 排序

[文盲老顾]

现在我有一个数据库，产品表大约有200万数据，对应的企业库大约有5万家

现在有这样一个需求

做出一个列表页，列出所有产品，但是要求每个公司的产品先出现一次，只要这个产品是上线状态且最新发布的，然后其他未列出的产品按照发布时间排序

我现在的 sql 指令是这么写的

select * from (
  select *,row_number() over(order by (case com_id when 1 then 0 else 1 end)) from (
    select *,row_number() over(partition by com_id order by p_date desc) as com_id from products where p_online=1
  )
) where rowid between 1 and 40 order by rowid

但是这样执行起来，效率非常低，甚至1分钟都无法查询成功

求高人优化该指令或提出其他解决办法

[文盲老顾]

select * from (
  select *,row_number() over(order by (case sort_id when 1 then 0 else 1 end)) from (
    select *,row_number() over(partition by com_id order by p_date desc) as sort_id from products where p_online=1
  )
) where rowid between 1 and 40 order by rowid

刚才的 com_id 冲突了，重写一下

[中国风]

select * from ( select *,row_number() over(order by (case sort_id when 1 then 0 else 1 end)) as RN from ( select *,row_number() over(partition by com_id order by p_date desc) as sort_id from products where p_online=1 ) ) where rowid between 1 and 40 order by rowid

[中国风]

少了别名，加上 select * from ( select *,row_number() over(order by (case sort_id when 1 then 0 else 1 end)) as RN from ( select *,row_number() over(partition by com_id order by p_date desc) as sort_id from products where p_online=1 ) as T ) as T2 where rowid between 1 and 40 order by rowid

[文盲老顾]

-_-b，不是无法执行，语句我没有错误，并且也能查出数据，我现在的问题是效率太低。。。

select * from (
	select *,row_number() over(order by (case comid when 1 then 0 else 1 end)) as rowid from (
		select ROW_NUMBER() over(partition by c_id order by p_id desc) as comid,* from pub_products)
	 a) 
 a where rowid between 1 and 40 order by rowid

200万数据，现在这条指令用时7分钟，求高手优化

[中国风]

SQL2012以上版本可用

select * from dbo.Tab1 order by ID  offset (@PageCount-1)*@PageSize rows fetch next @PageSize rows only

http://blog.csdn.net/roy_88/article/details/51318650

[文盲老顾]

。。。有别名，只不过开始的指令我顺手打的，不要在意细节，优化，求优化，明天再加分

[中国风]

没别名表和列不报错？什么数据库和版本？

[文盲老顾]

看右下角。。。7分钟实在不能接受

[中国风]

你的语句确定没问题，同一c_id取了最大的p_id,分页时又不过滤重复？

[文盲老顾]

sql 2008。。。连 r2 都不是

[中国风]

p_id--这是不是主健

[中国风]

select b.* 
from 
(
    select *
		,row_number() over(order by  SIGN(comid-1) asc) as rowid 
    from 
    (select ROW_NUMBER() over(partition by c_id order by p_id desc) as comid,p_id from pub_products) as a
   ) as a     

 inner join pub_products as b on a.p_id=b.p_id
where a.rowid between 1 and 40 
order by a.rowid
 

这样改试试，先分页再连接