在SQL中，计算具有大于或等于另一列的值的行数

Question

我有一个带有两列的表格：一对夫妻的ID和该夫妻的“分数”数量。 我想要一个结果，该结果列出了每个值为x时具有x或更多分数的夫妻数量。 因此，我的输入如下： | couple_id | num_marks | |-----------+-----------| | 9 | 7 | | 6 | 6 | | 8 | 6 | | 2 | 5 | | 3 | 4 | | 5 | 4 | | 1 | 3 | | 4 | 3 | | 10 | 2 | | 7 | 1 |

我想得到以下结果： | num_marks | num_couples | |-----------+-------------| | 7 | 1 | | 6 | 3 | | 5 | 4 | | 4 | 6 | | 3 | 8 | | 2 | 9 | | 1 | 10 |

也就是说，有1对夫妇拥有7个或更多分数，有3对夫妇拥有6个或更多分数，有4对夫妇拥有5个或更多分数等等。我已经能够提出一个查询，以返回恰好n个分数的夫妇数量：

SELECT num_marks,
       count(couple_id) AS num_couples
  FROM table_name
  GROUP BY num_marks
  ORDER BY num_marks DESC;

这段文字描述了一个包含两列数据的表格，第一列是num_marks（标记数），第二列是num_couples（配对数）。每行数据表示相应标记数的配对数。例如，有1个标记数为7的配对，2个标记数为6的配对，1个标记数为5的配对等等。作者询问是否有一种方便的方法来有效地将每行的值与其上面的值相加？作者可以在应用程序级别上完成此操作，但似乎这种操作更适合在数据库中进行。

- haxney

Answer 1

这可能不是特别高效，但应该能完成工作：

SELECT t1.num_marks,       
  (SELECT count(t2.couple_id)
   FROM table_name t2  
   WHERE t2.num_marks >= t1.num_marks
   ) AS num_couples 
FROM table_name t1  
GROUP BY t1.num_marks   
ORDER BY t1.num_marks DESC;

编辑：您可以在查询的选择、来源、条件约束、分组和聚合子句中使用子查询，如果您引用了主/外部查询，那么它将对每一行评估子查询，这就是相关子查询。 (因此性能有所限制)。

根据Damien的回答，您也可以使用CTE - CTE可以提高可读性，并且在我看来还可以使递归和自连接变得更加容易。

据我所知，大多数SQL都支持子查询。

- StuartLC

---

真是太棒了！我不知道你可以将SELECT作为列。这是标准的SQL，适用于SQLite和Postgres吗？此外，我的table_name实际上并不是一个表，而是一个大而复杂的SELECT语句；这会破坏它吗？ - haxney

@haxney - 我已经编辑过了 - 是的，你可以用另一个查询替换table_name，但你需要给它取别名才能在外部select语句中使用。 - StuartLC

Answer 2

您可以使用 RANK() 函数来确定每个结果的排名，然后将并列结果的数量加到该排名上即可。

create table #T (couple_id int,num_marks int)
insert into #T (couple_id,num_marks)
select    9 ,         7 union all
select    6 ,         6 union all
select    8 ,         6 union all
select    2 ,         5 union all
select    3 ,         4 union all
select    5 ,         4 union all
select    1 ,         3 union all
select    4 ,         3 union all
select   10 ,         2 union all
select    7 ,         1

;with Ranked as (
    select num_marks,RANK() OVER (ORDER BY num_marks desc) as rk from #T
)
select num_marks,rk + COUNT(*) -1 as Result from Ranked
group by num_marks,rk

给予:

num_marks   Result
----------- --------------------
7           1
6           3
5           4
4           6
3           8
2           9
1           10

(7 row(s) affected)

（当然，如果您需要按特定顺序获取结果，请不要忘记添加ORDER BY子句——上述排序只是一个偶然的结果。）

- Damien_The_Unbeliever