高分求一条排序SQL
表结构
ID Class Content Content2
1 A aa bb
2 A cc dd
3 A ee ff
4 b gg hh
5 b ii jj
6 b kk ll
7 c mm nn
8 c oo pp
9 c qq rr
10 d ss tt
11 d uu vv
12 d ww xx
求一条sql 要求按Class组循环每取最新条一条记录重新排列,排序后结果以下,
数据较多，望高手写条高效的sql,高分酬谢。
12 d ww xx
9 c qq rr
6 b kk ll
3 A ee ff
11 d uu vv
8 c oo pp
5 b ii jj
2 A cc dd
10 d ss tt
7 c mm nn
4 b gg hh
1 A aa bb
[解决办法]
select ID Class Content Content2, 求余(ID，3) as new_id from table_a order by new_id ,id desc
[解决办法]
求余函数不记得SQL Server是否有，如果没有可以自己写一个
[解决办法]
select id,Class, Content ,Content2 from table order by ((id-1)%3) desc,id desc
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(ID int,Class varchar(10),Content varchar(10),Content2 varchar(10))
insert into tb(ID,Class,Content,Content2) values(1 , 'A ', 'aa ', 'bb ')
insert into tb(ID,Class,Content,Content2) values(2 , 'A ', 'cc ', 'dd ')
insert into tb(ID,Class,Content,Content2) values(3 , 'A ', 'ee ', 'ff ')
insert into tb(ID,Class,Content,Content2) values(4 , 'b ', 'gg ', 'hh ')
insert into tb(ID,Class,Content,Content2) values(5 , 'b ', 'ii ', 'jj ')
insert into tb(ID,Class,Content,Content2) values(6 , 'b ', 'kk ', 'll ')
insert into tb(ID,Class,Content,Content2) values(7 , 'c ', 'mm ', 'nn ')
insert into tb(ID,Class,Content,Content2) values(8 , 'c ', 'oo ', 'pp ')
insert into tb(ID,Class,Content,Content2) values(9 , 'c ', 'qq ', 'rr ')
insert into tb(ID,Class,Content,Content2) values(10, 'd ', 'ss ', 'tt ')
insert into tb(ID,Class,Content,Content2) values(11, 'd ', 'uu ', 'vv ')
insert into tb(ID,Class,Content,Content2) values(12, 'd ', 'ww ', 'xx ')
go
select px=(select count(1) from tb where class=a.class and id <a.id)+1 , * from tb a
order by px desc , class desc
drop table tb
/*
px ID Class Content Content2
----------- ----------- ---------- ---------- ----------
3 12 d ww xx
3 9 c qq rr
3 6 b kk ll
3 3 A ee ff
2 11 d uu vv
2 8 c oo pp
2 5 b ii jj
2 2 A cc dd
1 10 d ss tt
1 7 c mm nn
1 4 b gg hh
1 1 A aa bb
（所影响的行数为 12 行）
*/
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(ID int,Class varchar(10),Content varchar(10),Content2 varchar(10))
insert into tb(ID,Class,Content,Content2) values(1 , 'A ', 'aa ', 'bb ')
insert into tb(ID,Class,Content,Content2) values(2 , 'A ', 'cc ', 'dd ')
insert into tb(ID,Class,Content,Content2) values(3 , 'A ', 'ee ', 'ff ')
insert into tb(ID,Class,Content,Content2) values(4 , 'b ', 'gg ', 'hh ')
insert into tb(ID,Class,Content,Content2) values(5 , 'b ', 'ii ', 'jj ')
insert into tb(ID,Class,Content,Content2) values(6 , 'b ', 'kk ', 'll ')
insert into tb(ID,Class,Content,Content2) values(7 , 'c ', 'mm ', 'nn ')
insert into tb(ID,Class,Content,Content2) values(8 , 'c ', 'oo ', 'pp ')
insert into tb(ID,Class,Content,Content2) values(9 , 'c ', 'qq ', 'rr ')
insert into tb(ID,Class,Content,Content2) values(10, 'd ', 'ss ', 'tt ')
insert into tb(ID,Class,Content,Content2) values(11, 'd ', 'uu ', 'vv ')
insert into tb(ID,Class,Content,Content2) values(12, 'd ', 'ww ', 'xx ')
go
select ID,Class,Content,Content2 from
(
select px=(select count(1) from tb where class=a.class and id <a.id)+1 , * from tb a
) t
order by px desc , class desc
drop table tb
/*
ID Class Content Content2
----------- ---------- ---------- ----------
12 d ww xx
9 c qq rr
6 b kk ll
3 A ee ff
11 d uu vv
8 c oo pp
5 b ii jj
2 A cc dd
10 d ss tt
7 c mm nn
4 b gg hh
1 A aa bb
（所影响的行数为 12 行）
*/
[解决办法]
不知道，谁的运行的效率高，测试一下．｀｀
[解决办法]
2005可以这样写
create table tb(ID int,Class varchar(10),Content varchar(10),Content2 varchar(10))
insert into tb(ID,Class,Content,Content2) values(1 , 'A ', 'aa ', 'bb ')
insert into tb(ID,Class,Content,Content2) values(2 , 'A ', 'cc ', 'dd ')
insert into tb(ID,Class,Content,Content2) values(3 , 'A ', 'ee ', 'ff ')
insert into tb(ID,Class,Content,Content2) values(4 , 'b ', 'gg ', 'hh ')
insert into tb(ID,Class,Content,Content2) values(5 , 'b ', 'ii ', 'jj ')
insert into tb(ID,Class,Content,Content2) values(6 , 'b ', 'kk ', 'll ')
insert into tb(ID,Class,Content,Content2) values(7 , 'c ', 'mm ', 'nn ')
insert into tb(ID,Class,Content,Content2) values(8 , 'c ', 'oo ', 'pp ')
insert into tb(ID,Class,Content,Content2) values(9 , 'c ', 'qq ', 'rr ')
insert into tb(ID,Class,Content,Content2) values(10, 'd ', 'ss ', 'tt ')
insert into tb(ID,Class,Content,Content2) values(11, 'd ', 'uu ', 'vv ')
insert into tb(ID,Class,Content,Content2) values(12, 'd ', 'ww ', 'xx ')
go
select row_number() over(partition by class order by id desc) as row,id,class,content,content2
from tb
order by row,class desc
drop table tb
[解决办法]
declare @t table(ID int,Class varchar(6),Content varchar(6),Content2 varchar(6))
insert into @t values(1 , 'A ', 'aa ', 'bb ')
insert into @t values(2 , 'A ', 'cc ', 'dd ')
insert into @t values(3 , 'A ', 'ee ', 'ff ')
insert into @t values(4 , 'b ', 'gg ', 'hh ')
insert into @t values(5 , 'b ', 'ii ', 'jj ')
insert into @t values(6 , 'b ', 'kk ', 'll ')
insert into @t values(7 , 'c ', 'mm ', 'nn ')
insert into @t values(8 , 'c ', 'oo ', 'pp ')
insert into @t values(9 , 'c ', 'qq ', 'rr ')
insert into @t values(10, 'd ', 'ss ', 'tt ')
insert into @t values(11, 'd ', 'uu ', 'vv ')
insert into @t values(12, 'd ', 'ww ', 'xx ')
select
t.*
from
@t t
order by
(select count(*) from @t where Class=t.Class and ID <=t.ID) desc,Class desc
/*
ID Class Content Content2
----------- ------ ------- --------
12 d ww xx
9 c qq rr
6 b kk ll
3 A ee ff
11 d uu vv
8 c oo pp
5 b ii jj
2 A cc dd
10 d ss tt
7 c mm nn
4 b gg hh
1 A aa bb
*/
[解决办法]
--借用子陌兄的例子，楼上各位好像理解错了楼主的意思，排序规则是以加3递增的
楼主看不是这个意思看一下效果:
declare @t table(ID int,Class varchar(6),Content varchar(6),Content2 varchar(6))
insert into @t values(1 , 'A ', 'aa ', 'bb ')
insert into @t values(2 , 'A ', 'cc ', 'dd ')
insert into @t values(3 , 'A ', 'ee ', 'ff ')
insert into @t values(4 , 'b ', 'gg ', 'hh ')
insert into @t values(5 , 'b ', 'ii ', 'jj ')
insert into @t values(6 , 'b ', 'kk ', 'll ')
insert into @t values(7 , 'c ', 'mm ', 'nn ')
insert into @t values(8 , 'c ', 'oo ', 'pp ')
insert into @t values(9 , 'c ', 'qq ', 'rr ')
insert into @t values(10, 'd ', 'ss ', 'tt ')
insert into @t values(11, 'd ', 'uu ', 'vv ')
insert into @t values(12, 'd ', 'ww ', 'xx ')
select * from @t order by id%3,class desc
ID Class Content Content2
----------- ------ ------- --------
12 d ww xx
9 c qq rr
6 b kk ll
3 A ee ff
10 d ss tt
7 c mm nn
4 b gg hh
1 A aa bb
11 d uu vv
8 c oo pp
5 b ii jj
2 A cc dd
(12 行受影响)
[解决办法]
--把11放在第二位:
declare @t table(ID int,Class varchar(6),Content varchar(6),Content2 varchar(6))
insert into @t values(1 , 'A ', 'aa ', 'bb ')
insert into @t values(2 , 'A ', 'cc ', 'dd ')
insert into @t values(3 , 'A ', 'ee ', 'ff ')
insert into @t values(4 , 'b ', 'gg ', 'hh ')
insert into @t values(5 , 'b ', 'ii ', 'jj ')
insert into @t values(6 , 'b ', 'kk ', 'll ')
insert into @t values(7 , 'c ', 'mm ', 'nn ')
insert into @t values(8 , 'c ', 'oo ', 'pp ')
insert into @t values(9 , 'c ', 'qq ', 'rr ')
insert into @t values(10, 'd ', 'ss ', 'tt ')
insert into @t values(11, 'd ', 'uu ', 'vv ')
insert into @t values(12, 'd ', 'ww ', 'xx ')
select *
from @t
order by case when id%3 =0 then 3 else id%3 end desc,id desc
ID Class Content Content2
----------- ------ ------- --------
12 d ww xx
9 c qq rr
6 b kk ll
3 A ee ff
11 d uu vv
8 c oo pp
5 b ii jj
2 A cc dd
10 d ss tt
7 c mm nn
4 b gg hh
1 A aa bb
(12 行受影响)
[解决办法]
ankor(~~打破面皮问到底~~) ( ) 信誉：100 Blog 2007-3-31 22:24:47 得分: 0
不知道，谁的运行的效率高，测试一下．｀｀
看了一下楼主各位的，都用到子查询；
表面上看偶的好像要快一下。。。
[解决办法]
你的快，如果不是3个呢，，
如果是aa,bb,cc,dd,ee呢
那不会出错吗
[解决办法]
如果是aa,bb,cc,dd,ee呢?
不会出错，以上排序是根据ID列，Class的变化不会影响
只是楼主要怎样的效果，
给出一个数据看看,
[解决办法]
declare @t table(ID int,Class varchar(6),Content varchar(6),Content2 varchar(6))
insert into @t values(1 , 'A ', 'aa ', 'bb ')
insert into @t values(2 , 'A ', 'cc ', 'dd ')
insert into @t values(3 , 'A ', 'ee ', 'ff ')
insert into @t values(4 , 'b ', 'gg ', 'hh ')
insert into @t values(5 , 'b ', 'ii ', 'jj ')
insert into @t values(6 , 'b ', 'kk ', 'll ')
insert into @t values(7 , 'c ', 'mm ', 'nn ')
insert into @t values(8 , 'c ', 'oo ', 'pp ')
insert into @t values(9 , 'c ', 'qq ', 'rr ')
insert into @t values(10, 'd ', 'ss ', 'tt ')
insert into @t values(11, 'd ', 'uu ', 'vv ')
insert into @t values(12, 'd ', 'ww ', 'xx ')
insert into @t values(13, 'e ', 'ss ', 'tt ')
insert into @t values(14, 'e ', 'uu ', 'vv ')
insert into @t values(15, 'e ', 'ww ', 'xx ')
select *
from @t
order by case when id%3 =0 then 3 else id%3 end desc,id desc
ID Class Content Content2
----------- ------ ------- --------
15 e ww xx
12 d ww xx
9 c qq rr
6 b kk ll
3 A ee ff
14 e uu vv
11 d uu vv
8 c oo pp
5 b ii jj
2 A cc dd
13 e ss tt
10 d ss tt
7 c mm nn
4 b gg hh
1 A aa bb
(15 行受影响)
