sql如何按每个类型排序并取前几条数据

[qiuqingpo]

原始数据如下：
table
name leixing
A 1
A 1
A 1
B 1
B 1
B 1
A 2
C 2
C 2
D 2
E 1
F 1
leixing name 数量
1 A 3
1 B 3
1 E 2
2 A 1
2 C 2
2 D 1

数量就是name在表格中出现的行数
想取每个分类的前三条记录，按序排列

[trunjun]

SLEECT leixing,[name],count(*) as num FROM TABLE GROUP BY leixing acs,[name] asc

[开着拖拉机泡妞]

select leixing,,[name],count(*) as num FROM TABLE GROUP BY leixing ,[name] 

[liuleiyu121520]

//你可以参考下
public List<CYW.UserCenter.Model.TopTenList> GetConference(int TopNum, String TypeID1)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance(DataAccessInstance.Academe_CYDB))
            {
                List<CYW.UserCenter.Model.TopTenList> list = new List<TopTenList>();
                String SqlStr = @"SELECT DISTINCT
                                  *
                                FROM
                                (
                                   SELECT rid=ROW_NUMBER() OVER(PARTITION BY TypeID1,TypeID2 order by [Year] desc,UpdateTime desc),*
                                   FROM TopTen_List";
                if (TypeID1 != "" && TypeID1 != null)
                             SqlStr+=" where TypeID1=@TypeID1";
                SqlStr += @") AS T
                                WHERE rid<=@TopNum";
                
                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@TopNum", TopNum);
                if (TypeID1 != "" && TypeID1 != null) dpc.AddWithValue("TypeID1", TypeID1);
                using (IDataReader dr = broker.ExecuteSQLReader(SqlStr, dpc))
                {
                    while (dr.Read())
                    {
                        CYW.UserCenter.Model.TopTenList obj = new CYW.UserCenter.Model.TopTenList();
                        obj.Load(dr, null);
                        list.Add(obj);
                    }
                }
                return list;
            }
        }

[trunjun]

use tempdb

create table mytest
(
	[name] nvarchar(10),
	leixing int
)

insert into mytest([name],leixing) values('A',1)
insert into mytest([name],leixing) values('A',1)
insert into mytest([name],leixing) values('A',1)
insert into mytest([name],leixing) values('B',1)
insert into mytest([name],leixing) values('B',1)
insert into mytest([name],leixing) values('B',1)
insert into mytest([name],leixing) values('A',2)
insert into mytest([name],leixing) values('C',2)
insert into mytest([name],leixing) values('C',2)
insert into mytest([name],leixing) values('D',2)
insert into mytest([name],leixing) values('E',1)
insert into mytest([name],leixing) values('F',1)

SELECT leixing,[name],count(*) as num 
	FROM mytest GROUP BY leixing ,[name]
	ORDER BY leixing

[qiuqingpo]

[Quote=引用 4 楼 trunjun 的回复:]

SQL code
use tempdb

create table mytest
(
[name] nvarchar(10),
leixing int
)

insert into mytest([name],leixing) values('A',1)
insert into mytest([name],leixing) values('A',1)
insert into m……
[/Quote]

想取每个分类的前三条记录，按序排列是的取每个分类的前三条记录。不是全部的呢

[开着拖拉机泡妞]

--> 测试数据：[table]
go
if object_id('[table]') is not null 
drop table [table]
create table [table](
[name] varchar(1),
[leixing] int
)
go
insert [table]
select 'A',1 union all
select 'A',1 union all
select 'A',1 union all
select 'B',1 union all
select 'B',1 union all
select 'B',1 union all
select 'A',2 union all
select 'C',2 union all
select 'C',2 union all
select 'D',2 union all
select 'E',1 union all
select 'F',1

select leixing,name,COUNT(*)as 数量 from [table] group by leixing,name order by leixing,name
/*
leixing	name 数量
1	A	3
1	B	3
1	E	1
1	F	1
2	A	1
2	C	2
2	D	1
*/

[--小F--]

楼上你娃。

[开着拖拉机泡妞]

--> 测试数据：[table]
go
if object_id('[table]') is not null 
drop table [table]
create table [table](
[name] varchar(1),
[leixing] int
)
go
insert [table]
select 'A',1 union all
select 'A',1 union all
select 'A',1 union all
select 'B',1 union all
select 'B',1 union all
select 'B',1 union all
select 'A',2 union all
select 'C',2 union all
select 'C',2 union all
select 'D',2 union all
select 'E',1 union all
select 'F',1

select leixing,name from
(select row_number()over(partition by leixing,name order by getdate()) as num,*
from [table])a where num<=3
/*
leixing	name
1	A
1	A
1	A
1	B
1	B
1	B
1	E
1	F
2	A
2	C
2	C
2	D
*/
取前三条

[开着拖拉机泡妞]

--不考虑name的时候取每个分类前三行
select leixing,name from
(select row_number()over(partition by leixing order by getdate()) as num,*
from [table])a where num<=3
/*
leixing	name
1	A
1	A
1	A
2	A
2	C
2	C
*/

[--小F--]

select
  a.*,b.数量
from
  (select leixing,name from
(select row_number()over(partition by leixing order by getdate()) as num,*
from [table])a where num<=3)a
left join
   (select  name,count(1) as 数量 group by name)b
on 
   a.name=b.name

[唐诗三百首]

create table qiu
(name char(1), leixing int)

insert into qiu
select 'A', 1 union all
select 'A', 1 union all  
select 'A', 1 union all  
select 'B', 1 union all  
select 'B', 1 union all  
select 'B', 1 union all  
select 'A', 2 union all  
select 'C', 2 union all  
select 'C', 2 union all  
select 'D', 2 union all  
select 'E', 1 union all
select 'F', 1


;with e as
(select row_number() over(partition by t.leixing order by t.ct desc) rn,
 t.leixing,t.name,t.ct
 from (select leixing,name,count(*) ct
       from qiu group by leixing,name) t
)
select leixing,name,ct '数量'
from e where rn<=3
order by leixing,name,ct desc

leixing     name 数量
----------- ---- -----------
1           A    3
1           B    3
1           E    1
2           A    1
2           C    2
2           D    1

(6 row(s) affected)

[开着拖拉机泡妞]

go
if object_id('[table]') is not null 
drop table [table]
create table [table](
[name] varchar(1),
[leixing] int
)
go
insert [table]
select 'A',1 union all
select 'A',1 union all
select 'A',1 union all
select 'B',1 union all
select 'B',1 union all
select 'B',1 union all
select 'A',2 union all
select 'C',2 union all
select 'C',2 union all
select 'D',2 union all
select 'E',1 union all
select 'F',1
select leixing,name,数量 from(
select row_number()over(partition by leixing order by getdate()) as num,
leixing,name,COUNT(*)as 数量 from [table] 
group by leixing,name)a
where num<=3


/*
leixing	name	数量
1	A	3
1	B	3
1	E	1
2	A	1
2	C	2
2	D	1
*/



楼主E这个后面的数量是不是应该是1，而不是你写的2？？

[--小F--]

;with f as
(
select leixing,name,count(1) as 数量 from tb group by name,leixing
)
select 
  distinct b.*
from
  f a
cross apply
  (select top 3 * from f where leixing=a.leixing and name=a.name  order by 数量 desc)b
order by 
  leixing,name 
  
  
    
select
 leixing,name,数量 
from
(select row_number()over(partition by leixing order by getdate()) as num,leixing,name,COUNT(*)as 数量 from [tb] group by leixing,name )a
where
 num<=3

[wylsjz]

select leixing,name,num from (
select RANK() over(partition by leixing order by gid) ra,leixing,name,num
from (select leixing,name,COUNT(*) num from tablename group by leixing,name) tb) tbn
where ra<=3

[qiuqingpo]

感谢大家。分还是不够分