34,838
社区成员




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
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
这样改试试,先分页再连接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
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分钟,求高手优化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 冲突了,重写一下