这个SQL该怎么写运行速度会比较快

[webnumen]

有这样一个例子

序号 数值
1 18
2 19
3 10
4 7
5 20


要显示出
序号 数值 统计
1 18 2
2 19 3
3 10 1
4 7 0
5 20 4


统计是是统计出当前数值 和其他数值相减，统计出大于0总共有多少

如下面所表述
18和其他数相减 相减之后得到2个正数
18-19 =-1
18-10 =8
18-7 11
18-20 =-2

19和其他数相减 相减之后得到3个正数
19-18=1
19-10=9
19-7=12
19-20=-1

10和其他数相减 相减之后得到1个正数
10-18=-8
10-19=-9
10-7=3
10-20=-10

7和其他数相减 相减之后得到0个正数
7-18=-11
7-19=-12
7-10=-3
7-20=-13

20和其他数相减 相减之后得到4个正数
20-18=2
20-19=1
20-10=10
20-7=13

请问SQL语句该怎么写?如果分不够，可以再加！

[汤姆克鲁斯]

你描述的太复杂了，实际上这就是个排序
你测一下面的两个方法，看哪个比较快，个人感觉是第一个

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([序号] INT,[数值] INT)
INSERT [tb]
SELECT 1,18 UNION ALL
SELECT 2,19 UNION ALL
SELECT 3,10 UNION ALL
SELECT 4,7 UNION ALL
SELECT 5,20
--------------开始查询--------------------------
--1
SELECT * ,ROW_NUMBER()OVER(ORDER BY [数值])-1 FROM [tb] ORDER BY [序号]
--2
SELECT * ,(SELECT COUNT(*) FROM tb WHERE [数值]<t.[数值]) FROM [tb] AS t 

----------------结果----------------------------
/* 
序号	数值	(无列名)
1	18	2
2	19	3
3	10	1
4	7	0
5	20	4
*/

[汤姆克鲁斯]

[Quote=引用 1 楼 的回复:]

SQL code
create table #ta(id int, value int)
insert into #ta select 1, 18
union all select 2, 19
union all select 3, 10
union all select 4, 7
union all select 5, 20


select a.value, count(case w……
[/Quote]
丫的换头像了啊，差点没认出来，O(∩_∩)O哈哈~

[kensouterry1]

2楼的解法是可以的

[duoxu1983]

[Quote=引用 2 楼 的回复:]

你描述的太复杂了，实际上这就是个排序
你测一下面的两个方法，看哪个比较快，个人感觉是第一个

SQL code
--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([序号] INT,[数值] INT)
INSERT [tb]
SELECT 1,18 UNION ALL
S……
[/Quote]

考虑到可能有重复值 应该用rank()

[-Tracy-McGrady-]

2楼正解

select *,统计=(select count(*) from @table where 数值<t.数值) from @table t

[gogodiy]

CREATE TABLE t1
(
	id INT,
	col INT
)
INSERT INTO t1
SELECT 1, 18 UNION ALL
SELECT 2, 19 UNION ALL
SELECT 3, 10 UNION ALL
SELECT 4, 7 UNION ALL
SELECT 5, 20
SELECT * FROM t1

;WITH aaa AS
(
	SELECT id,col,ROW_NUMBER() OVER(ORDER BY col)-1 AS rowindex FROM t1
)
SELECT * FROM aaa ORDER BY id

id	col	rowindex
1	18	2
2	19	3
3	10	1
4	7	0
5	20	4

[gw6328]

create table #ta(id int, value int)
insert into #ta select 1, 18
union all select 2, 19
union all select 3, 10
union all select 4, 7
union all select 5, 20

SELECT a.id,a.VALUE,SUM(CASE WHEN a.VALUE>b.value THEN 1 ELSE 0 end) AS x FROM #ta a JOIN #ta b ON 1=1
GROUP BY a.id,a.value
ORDER BY a.id

DROP TABLE #ta;

[DBA_磊仔]

[Quote=引用 3 楼 的回复:]
引用 1 楼 的回复:

SQL code
create table #ta(id int, value int)
insert into #ta select 1, 18
union all select 2, 19
union all select 3, 10
union all select 4, 7
union all select 5, 20


select a.……
[/Quote]真是排序，这回我傻了，要说答案也不错，要说速度我那个就烂死了
被楼主摆了一道啊

[DBA_磊仔]

抗日期间，剑八的头像就暂时打入冷宫吧

[webnumen]

什么排序？那个统计是根据他的数值减去其他记录，统计出几个正数，其实你们几个Sql语句我都懂但问题是这种方法估计比较慢，我的记录有30万条，同时计算，我试过了，都很慢！

[DBA_磊仔]

排序后，在他前面的相减就是整数，后面的就是负数。。
你这个模型可以转换为排序问题，你可以思考一下
全表row_number排序已经是最高效的了，只需要读取数据一遍，不用怀疑他的性能

[webnumen]

谢谢，我再试下

[DBA_磊仔]

create table #ta(id int, value int)
insert into #ta select 1, 18  
union all select 2, 19
union all select 3, 10
union all select 4, 7
union all select 5, 20


select  a.value, count(case when a.value - b.value > 0 then 1 else null end) counter 
from #ta a, #ta b 
where a.value <> b.value group by a.value
/*value       counter
----------- -----------
7           0
10          1
18          2
19          3
20          4

(5 行受影响)

*/

[driversky_ly]

create table #ta(id int, value int)
insert into #ta select 1, 18  
union all select 2, 19
union all select 3, 10
union all select 4, 7
union all select 5, 20


select id,value, dense_rank() over(order by value asc)-1 as rank_num from #ta

[driversky_ly]

[Quote=引用 15 楼 的回复:]

SQL code

create table #ta(id int, value int)
insert into #ta select 1, 18
union all select 2, 19
union all select 3, 10
union all select 4, 7
union all select 5, 20


select id,value, dense_rank(……
[/Quote]忘记 order by id 了