select Id,Title,Content,Date,UserId from (select row_number() over(partition by UserId order by id) no,* from tb) a order by no
declare @table table (id int,name varchar(1),col int) insert into @table select 1,'a',111 union all select 2,'a',112 union all select 3,'a',113 union all select 4,'b',114 union all select 5,'b',115 union all select 6,'b',116 union all select 7,'c',117 union all select 8,'c',118 union all select 9,'c',119select * from @table order by (row_number() over (partition by name order by id)),id /* id name col ----------- ---- ----------- 1 a 111 4 b 114 7 c 117 2 a 112 5 b 115 8 c 118 3 a 113 6 b 116 9 c 119 */
SELECT Id, Title, Content, Date, UserId FROM (SELECT PX=ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY ID),* FROM TB) T ORDER BY PX,UserId
declare @table table (id int,name varchar(1),col int) insert into @table select 1,'a',111 union all select 2,'a',112 union all select 3,'a',113 union all select 4,'b',114 union all select 5,'b',115 union all select 6,'b',116 union all select 7,'c',117 union all select 8,'c',118 union all select 9,'c',119select * from @table order by (row_number() over (partition by name order by newid())),id newid() 产生无序的
试验了,以上各位的代码,问题如下:left join 了2个表, 性能很低,10多万的测试数据,select top 20 * 局域网居然要10多秒
交叉排列是什麽意思?如果不想按USERID顺序显示,那就最后ORDER BY NEW()随机顺序显示即可。
select top 20 * from tablename order by (row_number() over(partition by UserId order by Date desc))
性能太差 的话我建议你建立索引 在这里你得在Date上面建立逆序索引 create index tab_index on tablename(Date desc)
比如强制走这个索引create index tab_index on tablename(Date desc)select top 20 * from tablename (index tab_index) order by (row_number() over(partition by UserId order by Date desc))
(select row_number() over(partition by UserId order by id) no,* from tb) a
order by no
declare @table table (id int,name varchar(1),col int)
insert into @table
select 1,'a',111 union all
select 2,'a',112 union all
select 3,'a',113 union all
select 4,'b',114 union all
select 5,'b',115 union all
select 6,'b',116 union all
select 7,'c',117 union all
select 8,'c',118 union all
select 9,'c',119select * from @table order by
(row_number() over (partition by name order by id)),id
/*
id name col
----------- ---- -----------
1 a 111
4 b 114
7 c 117
2 a 112
5 b 115
8 c 118
3 a 113
6 b 116
9 c 119
*/
SELECT Id, Title, Content, Date, UserId
FROM
(SELECT PX=ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY ID),* FROM TB) T
ORDER BY PX,UserId
insert into @table
select 1,'a',111 union all
select 2,'a',112 union all
select 3,'a',113 union all
select 4,'b',114 union all
select 5,'b',115 union all
select 6,'b',116 union all
select 7,'c',117 union all
select 8,'c',118 union all
select 9,'c',119select * from @table order by
(row_number() over (partition by name order by newid())),id newid() 产生无序的
select top 20 * from tablename
order by (row_number() over(partition by UserId order by Date desc))
create index tab_index on tablename(Date desc)
order by (row_number() over(partition by UserId order by Date desc))