MySQl :如何对子查询使用Count？

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中排名第四。

如何统计排名子查询中的总行数？我需要这个比特的计数：

(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

谢谢。

-Laxmidi

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

Answer 2

您可以使用相同的子选择，但不进行分数比较：

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,                 
         (SELECT COUNT(*)
            FROM my_table1 z
           WHERE z.type LIKE '%Blue%' 
             AND t.type  LIKE '%Blue%') AS rankOutOf
    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'   

rankOutOf列返回排名查询中考虑的候选项的数量。

Answer 3

我不确定我是否理解，但我认为您应该在子查询中包含FOUND_ROWS()。

(SELECT COUNT(*)，FOUND_ROWS() FROM my_table1 z WHERE z.type LIKE '%Blue%‘和t.type LIKE '%Blue%’AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4))作为排名，数字

你可以在这里找到更多信息：http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows