排序的问题

是算好了的,用下面的语句SELECT COUNT(*) FROM MB WHERE (A+B)%10=0 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=1 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=2 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=3 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=4 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=5 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=6 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=7 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=8 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=9

select (A+B) % 10 , COUNT(1) , ROW_NUMBER() OVER(ORDER BY COUNT(1) DESC) FROM MB GROUP BY (A+B)%10
试试这样可以不？

[code=sqlif OBJECT_ID('chong') is not null
drop table chong
go
create table chong(
col_num int primary key not null,
col_repnum int not null
)insert into chong values(0,28);insert into chong values(1,12);
insert into chong values(2,5);
insert into chong values(3,30);
insert into chong values(4,999);
insert into chong values(5,32);
insert into chong values(6,654);
insert into chong values(7,876);
insert into chong values(8,505);
insert into chong values(9,775);
select * from chong;with tt
as(
select a.col_repnum ,RANK()over(Order by a.col_repnum desc) rn
from chong a

)
select a.col_num ,a.col_repnum ,b.rn
from chong a left outer join tt b on a.col_repnum =b.col_repnum ][/code]

if OBJECT_ID('chong') is not null
drop table chong
go
create table chong(
col_num int primary key not null,
col_repnum int not null
)insert into chong values(0,28);insert into chong values(1,12);
insert into chong values(2,5);
insert into chong values(3,30);
insert into chong values(4,999);
insert into chong values(5,32);
insert into chong values(6,654);
insert into chong values(7,876);
insert into chong values(8,505);
insert into chong values(9,775);
select * from chong;with tt
as(
select a.col_repnum ,RANK()over(Order by a.col_repnum desc) rn
from chong a

)
select a.col_num ,a.col_repnum ,b.rn
from chong a left outer join tt b on a.col_repnum =b.col_repnum

这个排序倒是不算太复杂，就是不知道有没有更优化的方法，下面是测试数据;with cte as
(
select abs(cast(cast(newid() as varbinary)/10000 as int)) as A,
abs(cast(cast(newid() as varbinary)/10000 as int)) as B
from sysobjects a cross join sysobjects b
)
select (a+b)%10 as C,
count(1),
row_number() over (order by count(1))
from cte
group by (a+b)%10
order by c

不是已经计算好了吗？
直接
ROW_number()over(order by 重复次数 desc) 不就OK了？

-------------------------
是想在查询结束时，同时输出C、C的重复次数、重复次数的名次，C列得按照从0-9排列。
即看看C的个位数是0的记录有多少条、排第几，C的个位数是1的记录有多少条、排第几......C的个位数是9的记录有多少条、排第几。

-------------------------
是想在查询结束时，同时输出C、C的重复次数、重复次数的名次，C列得按照从0-9排列。
即看看C的个位数是0的记录有多少条、排第几，C的个位数是1的记录有多少条、排第几......C的个位数是9的记录有多少条、排第几。
你把你原始的数据和你所有用的查询的语句贴上来看看，这样只看你的结果，要去改你得到这个结果的语句有点困难

数据如下(A+B通过查询得到）
A B A+B
0 0 0
10 10 20
3 7 10

1 10 11

4 8 12
9 13 22

33 10 43
44 19 63
11 12 23
15 18 33
14 19 33
66 17 83

41 13 54
33 11 44
22 82 104
48 96 144
4 10 14
77 17 94
165 89 254
33 11 44
34 20 54
29 45 74
65 19 84

7 28 35
46 39 85
55 10 65
11 34 45
69 36 105

16 50 66
13 13 26
24 12 36
42 14 56

22 105 127
23 34 57
95 22 117
3 74 77
67 10 77
34 43 77
33 44 77
90 7 97

45 13 58
10 28 38
98 10 108
43 25 68
78 10 88
25 53 78
64 24 88

9 10 19
21 58 79
64 45 109
47 52 99
88 11 99
77 22 99
66 33 99
12 27 39
19 10 29
28 11 39-------------
A+B的结果取个位数，个位数的重复次数如下C 重复次数
0 3
1 1
2 2
3 6
4 11
5 5
6 4
7 8
8 7
9 10
就是想把重复次数按大小标识出来，如下，个位为4的记录有11条，重复次数最多，排第一，个位为1的记录只有一条，排第十。
C 重复次数排序
0 3 8
1 1 10
2 2 9
3 6 5
4 11 1
5 5 6
6 4 7
7 8 3
8 7 4
9 10 2
--------------------------------------用下面的语句只能查出A+B的个位数的重复次数，不能得到排序。
SELECT COUNT(*) FROM MB WHERE (A+B)%10=0 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=1 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=2 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=3 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=4 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=5 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=6 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=7 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=8 UNION ALL
SELECT COUNT(*) FROM MB WHERE (A+B)%10=9

IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 'test')
DROP TABLE test
GO
CREATE TABLE test(a INT , b int)
GO
INSERT INTO test
SELECT 0 , 0 UNION ALL
SELECT 10 , 10 UNION ALL
SELECT 3 ,7 UNION ALL
SELECT 1 , 10 UNION ALL
SELECT 4 , 8 UNION ALL
SELECT 9 , 13 UNION ALL
SELECT 33, 10 UNION ALL
SELECT 44 , 19 UNION ALL
SELECT 11 , 12 UNION ALL
SELECT 15 , 18 GO
--执行查询
SELECT (a + b) % 10                    AS 个位数,
       COUNT(1)                        AS 次数,
       RANK() OVER(ORDER BY COUNT(1) DESC)  AS 排名
FROM   test
GROUP BY
       (a + b) % 10/*结果
个位数         次数          排名
----------- ----------- --------------------
3           4           1
0           3           2
2           2           3
1           1           4(4 row(s) affected) */测试资料没有建那么多，基本就是这样了

IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 'test')
DROP TABLE test
GO
CREATE TABLE test(a INT , b int)
GO
INSERT INTO test
SELECT 0 , 0 UNION ALL
SELECT 10 , 10 UNION ALL
SELECT 3 ,7 UNION ALL
SELECT 1 , 10 UNION ALL
SELECT 4 , 8 UNION ALL
SELECT 9 , 13 UNION ALL
SELECT 33, 10 UNION ALL
SELECT 44 , 19 UNION ALL
SELECT 11 , 12 UNION ALL
SELECT 15 , 18 GO
--执行查询
SELECT (a + b) % 10                    AS 个位数,
       COUNT(1)                        AS 次数,
       RANK() OVER(ORDER BY COUNT(1) DESC)  AS 排名
FROM   test
GROUP BY
       (a + b) % 10
ORDER BY (a+b) % 10/*结果
个位数         次数          排名
----------- ----------- --------------------
0           3           2
1           1           4
2           2           3
3           4           1(4 row(s) affected) */
在結果中加上order by 就好了

非常感谢大虾yoan2014的帮助，非常好用！也感谢小F版主、of785409885、wanyingsong！问题解决！谢谢大家！！

解决方案 »