MySQl:如何在子查询上使用计数?

发布于 2024-09-14 09:44:38 字数 893 浏览 5 评论 0原文

我有一个 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

谢谢。

-拉克斯米迪

I've got a MySQL statement that selects a name and also makes a ranking.

  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

But, I also need to know out of how many the rank is calculated. So for example, ranked #4 out of X.

How do I count the total number of rows in the ranking sub-query? I need the count for this bit:

(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

Thank you.

-Laxmidi

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

暗恋未遂 2024-09-21 09:44:38

您可以再添加一个子查询 - 它将与现有子查询相同,但没有 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

You can add one more subquery - it will be the same as the existing, but without AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4) condition:

 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
别在捏我脸啦 2024-09-21 09:44:38

您可以使用相同的子选择而不进行分数比较:

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 列返回排名查询中考虑的候选者数量。

You can use the same subselect without the score comparison:

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'   

The rankOutOf column returns the number of candidates considered in the ranking query.

挽清梦 2024-09-21 09:44:38

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

(选择 COUNT(*), FOUND_ROWS() 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 排名,数量

您可以在此处找到更多信息:http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

I'm not sure if I understand, but I think you should include FOUND_ROWS() in the subquery.

(SELECT COUNT(*), FOUND_ROWS() 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, number

You can find more information here: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

冷情妓 2024-09-21 09:44:38

我有点难以理解你的问题,但我会尝试一下。

这部分为您提供特定的排名数字(例如 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

因此,为了从该子查询中查找总行数,您只需删除 WHERE 子句即可。我不确定您是否需要删除 WHERE 子句中的所有内容,也许只是类型,或者只是分数?

如果您想要将多行分组在一起,我会使用GROUP BY,然后根据需要使用COUNT

I'm having a little trouble understanding your question, but I'll take a stab at it.

This part gets you the specific rank number (such as 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

So in order to find the total number of rows from that subquery, you should just need to remove your WHERE clause. I'm not sure if you need to remove everything in the WHERE clause though, maybe just the types, or just the scores?

If you have multiple rows that you want to be grouped together, I would use GROUP BY and then use COUNT as necessary.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文