select * from product order by UserId ,CreatedAt 没任何变化。。
select * from product order by UserId ,CreatedAt 上面语句并没有交替显示产品,比如 UserId=5的用户发了20条产品,那么正页还是他的信息,能否每个用户只显示两条,按发布日期排序,这样交替显示。
试试这个,每个用户只显示2条: select * from ( select *, ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum from product )t where rownum <= 2
5 试试这个,每个用户只显示2条: select * from ( select *, ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum from product )t where rownum <= 2 这其实也有缺陷的 结果可能是这样 A1 A2 A3 B1 B2 B3 他要的结果应该是这样的 A1 B1 A2 B2 A3 B3
加个排序 select * from ( select *, ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum from product )t where rownum <= 2 order by rownum,CreatedAt DESC试试这个,每个用户只显示2条: select * from ( select *, ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum from product )t where rownum <= 2 这其实也有缺陷的 结果可能是这样 A1 A2 A3 B1 B2 B3 他要的结果应该是这样的 A1 B1 A2 B2 A3 B3
select * from( select *, ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum from product )t where rownum <= 2 可以实现交替显示,但出了新的问题,商品总计200多条,用户12个,目前商品只显示24个?不能循环的按日期把所有商品都显示吗?
试试这个: select * from ( select *, ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum from product )t order by rownum,CreatedAt desc
这段解决了问题,又有了新问题。 因为要left join几个表,我把这段语句做成了视图,但直接select * from view_name 排序无效了?? 视图是必须的,有个分页的存储过程只能调用视图和表,这部分代码我是没法修改的。怎么办?
试图中确实不能排序,只有一个例外,就是: select top 99.9 * from ( select *, ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum from product )t order by rownum,CreatedAt desc
再改一下:select top 99.9 percent * from ( select *, ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum from product )t order by rownum,CreatedAt desc
select * from product order by UserId ,CreatedAt
没任何变化。。
上面语句并没有交替显示产品,比如 UserId=5的用户发了20条产品,那么正页还是他的信息,能否每个用户只显示两条,按发布日期排序,这样交替显示。
试试这个,每个用户只显示2条:
select *
from
(
select *,
ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum
from product
)t
where rownum <= 2
试试这个,每个用户只显示2条:
select *
from
(
select *,
ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum
from product
)t
where rownum <= 2
这其实也有缺陷的
结果可能是这样
A1
A2
A3
B1
B2
B3
他要的结果应该是这样的
A1
B1
A2
B2
A3
B3
select *
from
(
select *,
ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum
from product
)t
where rownum <= 2
order by rownum,CreatedAt DESC试试这个,每个用户只显示2条:
select *
from
(
select *,
ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum
from product
)t
where rownum <= 2
这其实也有缺陷的
结果可能是这样
A1
A2
A3
B1
B2
B3
他要的结果应该是这样的
A1
B1
A2
B2
A3
B3
试试这个:
select *
from
(
select *,
ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum
from product
)t
order by rownum,CreatedAt desc
因为要left join几个表,我把这段语句做成了视图,但直接select * from view_name 排序无效了??
视图是必须的,有个分页的存储过程只能调用视图和表,这部分代码我是没法修改的。怎么办?
试图中确实不能排序,只有一个例外,就是:
select top 99.9 *
from
(
select *,
ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum
from product
)t
order by rownum,CreatedAt desc
from
(
select *,
ROW_NUMBER() over(partition by UserId order by CreatedAt DESC) rownum
from product
)t
order by rownum,CreatedAt desc