‘壹’ sql语句 分组排序
你那图片根本看不清，再补充下吧 ，把问题也说清楚点。。
‘贰’ SQL 分组统计并排序
with tmp as
(select row_number() over(order by count(val)) as rn,val,count(val) as cnum from table1 group by val)
select * from tmp where rn<3
要想区别并列现象还要复杂一些，可能简单的语句解决不了
‘叁’ sql语句 在分组内排序
只能用存储过程
思路如下
将分组那个字段比如是A
Create temp table tmp_b
foreach
select distinct A into bianlianga from table
insert into tmp_b
select top 2 B from table where A=bianlianga order by B ;
delete from table where A=bianlianga and B in(select * from tmp_b）;
end foreach
‘肆’ SQL分组汇总排序
华子,我来看你.....
‘伍’ SQL如何对分组后的结果进行排序并且取前几名
SQL取分组中的前几名
[sql] www.2cto.com
create table #aa(class varchar(10),name varchar(10),lang int,math int)
go
insert into #aa
select '1','a1',60,70
union all
select '1','a2',80,70
union all
select '1','a3',90,70
union all
select '1','a4',50,70
go
insert into #aa
select '2','b1',60,70
union all
select '2','b2',90,90
union all
select '2','b3',90,70
union all
select '2','b4',50,70
go
select * from #aa
--取每年级前一名的成级
select * from
(select ROW_NUMBER() over(partition by class order by lang+math desc) rr, * from #aa ) a
where rr<2
--取每年级前二名的成级
select * from
(select ROW_NUMBER() over(partition by class order by lang+math desc) rr, * from #aa ) a
where rr<3
‘陆’ sql分组排序
group
by语句必须和聚合函数一起使用.
select
c,max(d)
from
a
group
by
c
order
by
max(d)
desc
这样子可以.
因为一条SELECT语句只可以返回一个结果集...
此句返回按c分组后并按每组中最大的d值进行排序.
‘柒’ sql 分组排序
没有计算机理解的顺序，必需构造一个表示顺序的函数。完整的实现语句如下：
select *,
case check_id
when 4 then 1
when 2 then 2
else check_id*10
end as def_order
from 表 order by def_order,idate
这里用CASE函数定义一个新的顺序，如果check_id=4，新的排序号为1；如果check_id=2,新的排序号为2；如果check_id为其它，新的排序号为check_id*10,这样能满足要求的顺序。
楼上的兄弟的解决方案，从理论上不能保证满足要求，因为查询输出记录的顺序不一定是记录输入表的顺序。
‘捌’ Sql分组排名
sybase 没有top 10吧。
用set rowcount 10
select * from table T where T.a in
(
set rowcount 10
select a from table where b=T.b order by a DESC
set rowcount 0
)
至于关键字是不是加[]记不清了。一直没用过这样的表名
--补充
你sql写的怪怪的，是不是下面这意思
set rowcount 10
select *
from table
order by a desc
set rowcount 0
‘玖’ SQL分组排序
create table #a (a char(10),b int)
insert #a select 'a',1
insert #a select 'a',5
insert #a select 'b',1
insert #a select 'b',4
insert #a select 'b',3
insert #a select 'b',5
create table #c (id int identity(1,1), a varchar(10))
insert into #c select distinct a from #A
create table #d (id int , a varchar(10),b int)
declare @a int
declare @b int
select @a=min(id) from #c
select @b=max(id) from #c
while (@a<=@b)
begin
select identity(int,1,1) as id , t1.a,t1.b into #b from #a t1, #c t2 where t1.a=t2.a and t2.[id]=@a order by t1.b
insert into #d
select * from #b
drop table #b
set @a=@a+1
end
select * from #d
‘拾’ sql 分组和排序
如果是SQLServer 2005以上可以这样写
select 学生ID、课程编号、成绩 from
(select 学生ID、课程编号、成绩 ,row_number(partition by 课程编号 order by 课程编号,成绩）as rn from student) as T where rn <=2 order by 课程编号、成绩