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 |
|-----------+-------------|
|         7 |           1 |
|         6 |           2 |
|         5 |           1 |
|         4 |           2 |
|         3 |           2 |
|         2 |           1 |
|         1 |           1 |

即有 1 对有 7 个标记，2 对有 6 个标记，1 个有 5 个标记，等等。 有一种方便的方法可以有效地将每行的值与上面的值相加 它？我可以在应用程序级别做到这一点，但这似乎是这样的事情 它真正属于数据库。

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;

编辑：
您可以在 select、from、where 组中使用子查询 by 和having 查询的子句，如果您引用主/外部“查询”，那么它将评估每一行的子查询，那么它被称为 相关子查询。 （因此对性能的警告）

根据 Damien 的回答，您还可以使用 CTE - CTE 可以提高可读性，并且还可以使递归和自连接变得更容易（IMO）。

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

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 子句 - 上述排序只是一个快乐 事故）