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]
//你可以参考下 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; } }
use tempdbcreate 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
--> 测试数据:[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',1select 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 */
--> 测试数据:[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',1select 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 */
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 descleixing 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??
;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
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
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]
//你可以参考下
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;
}
}
(
[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
--> 测试数据:[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',1select 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
*/
--> 测试数据:[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',1select 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
*/
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 descleixing 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??
(
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
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