MySQL 有没有办法将多个查询合并到同一个表中以在自己的行中获得不同的结果? [英] Is there a way with MySQL to merge multiple queries to the same table to get the different results in there own rows?

问题描述

这是我的查询:

SELECT COUNT(a.rating_id), COUNT(b.rating_id), COUNT(c.rating_id)
FROM wp_ratings a
LEFT JOIN wp_ratings b
LEFT JOIN wp_ratings c
WHERE a.rating_rating <= '5' AND a.rating_rating >= '4'
AND b.rating_rating <= '4' AND b.rating_rating >= '3'
AND c.rating_rating <= '3' AND c.rating_rating >= '0'

我收到一个错误.我认为我的查询是不言自明的.我只是不想这样做:

I am getting an error. I think my query is very self explanatory. I just don't want to do this:

SELECT COUNT(*) FROM wp_ratings WHERE rating_rating <= ‘5' AND rating_rating >= ‘4'
SELECT COUNT(*) FROM wp_ratings WHERE rating_rating <= ‘4' AND rating_rating >= ‘3'
SELECT COUNT(*) FROM wp_ratings WHERE rating_rating <= ‘3' AND rating_rating >= ‘0’

我正在尝试获得尽可能快的查询.

I am trying to get a query that will be as fast as possible.

那么有没有办法使用 MySQL 将多个查询合并到同一个表中，从而在各自的行中获得不同的结果?

So is there a way, with MySQL, to merge multiple queries to the same table to get the different results in there own rows?

更新

当我做 EXPLAIN 时，我看到 MySQL 对表进行了 3 次扫描，并且该表有 15 000 行，因此乘以 15 000 可以获得 45 000 行扫描.如果可能的话，我想把它降到 15 000.

When I do EXPLAIN I see that MySQL scans the table 3 times and that table has 15 000 rows so multiply by 15 000 you get 45 000 row scans. I want to bring it down to only 15 000 if possible.

推荐答案

SELECT
SUM(IF(rating_rating <= 5 AND rating_rating >= 4, 1, 0)),
SUM(IF(rating_rating <= 4 AND rating_rating >= 3, 1, 0)),
SUM(IF(rating_rating <= 3 AND rating_rating >= 0, 1, 0))
FROM wp_ratings

只需使用 SUM 而不是 COUNT 并且多次使用.然后，您可以只计算"您想要计算的内容.

Just use SUM instead of COUNT and this multiple times. You can then "count" only what you want to count.

这篇关于MySQL 有没有办法将多个查询合并到同一个表中以在自己的行中获得不同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！