sql - MySQL : How do I use Count on a Sub-Query?

Question

我有一个 MySQL 语句，它选择一个名称并进行排名。

  SELECT t.name,                        
         (SELECT COUNT(*)
            FROM my_table1 z
           WHERE z.type LIKE '%Blue%' 
             AND t.type  LIKE '%Blue%'
             AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4)) AS  rank                     
    FROM my_table1 t, my_table2 d
   WHERE d.name = t.name
     AND t.status != 'unknown'
     AND t.type = 'Blue'
     AND d.area_served = '$area_id'                 
ORDER BY rank ASC

但是，我还需要知道排名是多少。因此，例如，在 X 中排名第 4。

如何统计排名子查询的总行数？我需要这个位的计数:

(SELECT COUNT(*)
    FROM my_table1 z
    WHERE z.type LIKE '%Blue%' AND  t.type  LIKE '%Blue%'
    AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4)) AS  rank

谢谢。

-拉克斯米迪

Answer 1

您可以再添加一个子查询 - 它与现有子查询相同，但没有 AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4) 条件:

 SELECT t.name,                        
     (SELECT COUNT(*)
        FROM my_table1 z
       WHERE z.type LIKE '%Blue%' 
         AND t.type  LIKE '%Blue%'
         AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4)) AS  rank,
      // new subquery
    (SELECT COUNT(*)
        FROM my_table1 z
       WHERE z.type LIKE '%Blue%' 
         AND t.type  LIKE '%Blue%') as max_rank
FROM my_table1 t, my_table2 d   
WHERE d.name = t.name
 AND t.status != 'unknown'
 AND t.type = 'Blue'
 AND d.area_served = '$area_id'                 
ORDER BY rank ASC