22,210
社区成员
发帖
与我相关
我的任务
分享
declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
select top 1 score
from @school
order by (COUNT(*) over(PARTITION by score)) desc
/*
score
85
*/
[/quote]
大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
[/quote]
哦,是这样吗:
declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
--score出现次数一样的,也会排下去
select score,
rownum
from
(
select score,
row_number() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
where rownum <=10
/*
score rownum
85 1
70 2
75 3
95 4
*/
--score出现次数一样的,都排成第2名
select top 10 score,
rownum
from
(
select score,
rank() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
--where rownum <=10
/*
score rownum
85 1
70 2
75 2
95 2
*/
[/quote]
谢谢大牛 我先试试[/quote]
大神 不知道为何 我这边还是没有达到我的目的 可以给你QQ吗 想请教您下[/quote]
我加你关注了,也加我,可以发私信[/quote]
我给你发了私信declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
select top 1 score
from @school
order by (COUNT(*) over(PARTITION by score)) desc
/*
score
85
*/
[/quote]
大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
[/quote]
哦,是这样吗:
declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
--score出现次数一样的,也会排下去
select score,
rownum
from
(
select score,
row_number() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
where rownum <=10
/*
score rownum
85 1
70 2
75 3
95 4
*/
--score出现次数一样的,都排成第2名
select top 10 score,
rownum
from
(
select score,
rank() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
--where rownum <=10
/*
score rownum
85 1
70 2
75 2
95 2
*/
[/quote]
谢谢大牛 我先试试[/quote]
大神 不知道为何 我这边还是没有达到我的目的 可以给你QQ吗 想请教您下[/quote]
我加你关注了,也加我,可以发私信declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
select top 1 score
from @school
order by (COUNT(*) over(PARTITION by score)) desc
/*
score
85
*/
[/quote]
大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
[/quote]
哦,是这样吗:
declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
--score出现次数一样的,也会排下去
select score,
rownum
from
(
select score,
row_number() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
where rownum <=10
/*
score rownum
85 1
70 2
75 3
95 4
*/
--score出现次数一样的,都排成第2名
select top 10 score,
rownum
from
(
select score,
rank() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
--where rownum <=10
/*
score rownum
85 1
70 2
75 2
95 2
*/
declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
select top 1 score
from @school
order by (COUNT(*) over(PARTITION by score)) desc
/*
score
85
*/
[/quote]
大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
[/quote]
哦,是这样吗:
declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
--score出现次数一样的,也会排下去
select score,
rownum
from
(
select score,
row_number() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
where rownum <=10
/*
score rownum
85 1
70 2
75 3
95 4
*/
--score出现次数一样的,都排成第2名
select top 10 score,
rownum
from
(
select score,
rank() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
--where rownum <=10
/*
score rownum
85 1
70 2
75 2
95 2
*/
[/quote]
谢谢大牛 我先试试[/quote]
大神 不知道为何 我这边还是没有达到我的目的 可以给你QQ吗 想请教您下declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
select top 1 score
from @school
order by (COUNT(*) over(PARTITION by score)) desc
/*
score
85
*/
[/quote]
大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
[/quote]
哦,是这样吗:
declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
--score出现次数一样的,也会排下去
select score,
rownum
from
(
select score,
row_number() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
where rownum <=10
/*
score rownum
85 1
70 2
75 3
95 4
*/
--score出现次数一样的,都排成第2名
select top 10 score,
rownum
from
(
select score,
rank() over(order by c desc) as rownum
from
(
select distinct
score,
COUNT(*) over(PARTITION by score) c
from @school
)t
)tt
--where rownum <=10
/*
score rownum
85 1
70 2
75 2
95 2
*/
[/quote]
谢谢大牛 我先试试declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
select top 1 score
from @school
order by (COUNT(*) over(PARTITION by score)) desc
/*
score
85
*/
[/quote]
大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
select top 1 score
from @school
group by score
order by COUNT(*) desc
/*
score
85
*/
declare @school table(id int, name varchar(20), sex varchar(10), score int)
insert into @school
select 1,'张三','男',85 union all
select 2,'李四','男',95 union all
select 3,'王五','男',75 union all
select 4,'李六','男',70 union all
select 5,'王二','男',85
select top 1 score
from @school
order by (COUNT(*) over(PARTITION by score)) desc
/*
score
85
*/