（sqlserver）表中有个a是随机1到10的数字，现在要查询出以下东西

发布网友发布时间：2022-04-09 07:41

共2个回答

热心网友时间：2022-04-09 09:10

select a,count(a) 次数
from 表
group by a
order by count(a) desc

--如果你这是sql2005就用
row_num() over() 来处理名次。但我这没环境也没试

--下面这是另种方法
declare @t table(a int,b int,c int identity(1,1))
insert into @t(a,b)
select a,count(a) 次数
from 表
group by a
order by count(a) desc
select * from @t

热心网友时间：2022-04-09 10:28

表TestTable中有字段RandomNumber,以下sql语句即可：
select RandomNumber, count(RandomNumber) as NumberCount, dbo.uf_getRanking(RandomNumber) as Ranking
from TestTable
group by RandomNumber
order by count(RandomNumber) desc

排名写一个用户自定义函数dbo.uf_getRanking(),在sql中调用即可
CREATE FUNCTION dbo.uf_getRanking (@RandomNumber int)
RETURNS int
AS
BEGIN
declare @Ranking int //@Ranking代表名次
select @Ranking=count(RandomNumber) + 1
from TestTable
where RandomNumber>@RandomNumber
return @Ranking
END