那你这怎么会是获取一条呢,应该是多条才对吧? select * from tb where score in( select top 1 score from tb group by score order by count(1) desc )
那就把子查询部分提出来就是了 select top 1 score,count(1) as num from tb group by score order by num desc
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本: 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 */
根据上面修改的,适合2000的: 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 */
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本: 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 */大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢?? 如果可以给出出现次数 那就再好不过了 求大牛指教
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本: 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 */大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢?? 如果可以给出出现次数 那就再好不过了 求大牛指教 哦,是这样吗: 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 */
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本: 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 */大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢?? 如果可以给出出现次数 那就再好不过了 求大牛指教 哦,是这样吗: 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 */ 谢谢大牛 我先试试
select top 1 score,count(1) from dbo.school group by score order by count(1) desc
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本: 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 */大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢?? 如果可以给出出现次数 那就再好不过了 求大牛指教 哦,是这样吗: 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 */ 谢谢大牛 我先试试 大神 不知道为何 我这边还是没有达到我的目的 可以给你QQ吗 想请教您下
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本: 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 */大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢?? 如果可以给出出现次数 那就再好不过了 求大牛指教 哦,是这样吗: 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 */ 谢谢大牛 我先试试 大神 不知道为何 我这边还是没有达到我的目的 可以给你QQ吗 想请教您下我加你关注了,也加我,可以发私信
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本: 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 */大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢?? 如果可以给出出现次数 那就再好不过了 求大牛指教 哦,是这样吗: 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 */ 谢谢大牛 我先试试 大神 不知道为何 我这边还是没有达到我的目的 可以给你QQ吗 想请教您下我加你关注了,也加我,可以发私信我给你发了私信
select * from tb where score in(
select top 1 score from tb group by score order by count(1) desc
)
select top 1 score,count(1) as num from tb group by score order by num desc
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本:
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
*/
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
*/
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本:
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
*/大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本:
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
*/大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
哦,是这样吗:
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
*/
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本:
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
*/大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
哦,是这样吗:
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
*/
谢谢大牛 我先试试
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本:
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
*/大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
哦,是这样吗:
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
*/
谢谢大牛 我先试试
大神 不知道为何 我这边还是没有达到我的目的 可以给你QQ吗 想请教您下
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本:
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
*/大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
哦,是这样吗:
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
*/
谢谢大牛 我先试试
大神 不知道为何 我这边还是没有达到我的目的 可以给你QQ吗 想请教您下我加你关注了,也加我,可以发私信
因为我只要那个分数是多少 其他的可以不要 最好可以让他显示出现的次数适合2005及以后的版本:
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
*/大牛 那可以把出现次数最多的 次多的 再次 这样的排名前十位的都列出来呢??
如果可以给出出现次数 那就再好不过了 求大牛指教
哦,是这样吗:
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
*/
谢谢大牛 我先试试
大神 不知道为何 我这边还是没有达到我的目的 可以给你QQ吗 想请教您下我加你关注了,也加我,可以发私信我给你发了私信