select b.* from (select col2,sum(num) as anum from tb group by col2) a join tb b on a.col2=b.col2 order by anum desc,b.num desc --这下对了
select a.* from tb a left join (select col2,sum(c) as col2_sum from tb group by col2 ) b on a.col2=b.col2 order by b.col2_sum,a.col2,a.num
select * from tb a order by (select sum(num) from tb where col2=a.col2) desc ,num desc
--测试--测试数据 create table tb(col1 varchar(10),col2 varchar(10),num int) insert tb select 'a','类1',30 union all select 'b','类2',20 union all select 'c','类1',10 union all select 'd','类2',5 union all select 'e','类2',60 go--查询 select * from tb a order by (select sum(num) from tb where col2=a.col2) desc ,num desc go--删除测试 drop table tb/*--测试结果col1 col2 num ---------- ---------- ----------- e 类2 60 b 类2 20 d 类2 5 a 类1 30 c 类1 10(所影响的行数为 5 行) --*/
select a.* from tb a left join (select col2,sum(c) as col2_sum from tb group by col2 ) b on a.col2=b.col2 order by b.col2_sum desc ,a.col2,a.num desc
order by b.col2_sum,a.col2,a.num
select * from tb a
order by (select sum(num) from tb where col2=a.col2) desc
,num desc
create table tb(col1 varchar(10),col2 varchar(10),num int)
insert tb select 'a','类1',30
union all select 'b','类2',20
union all select 'c','类1',10
union all select 'd','类2',5
union all select 'e','类2',60
go--查询
select * from tb a
order by (select sum(num) from tb where col2=a.col2) desc
,num desc
go--删除测试
drop table tb/*--测试结果col1 col2 num
---------- ---------- -----------
e 类2 60
b 类2 20
d 类2 5
a 类1 30
c 类1 10(所影响的行数为 5 行)
--*/
select a.* from tb a left join (select col2,sum(c) as col2_sum from tb group by col2 ) b on a.col2=b.col2
order by b.col2_sum desc ,a.col2,a.num desc