用php计算投票数量,php – 计算MySQL中的投票频率

我正在创建一个网站,用户可以在该网站上对页面的类别进行投票.他们可以投票表明该页面属于a,b,c或d类.

我需要在所有投票中找到MySQL行中最常出现的类别.

每次用户提交投票时,它都会提交他们投票的“类别”和“page_id”.

到目前为止我有这个：

SELECT page_id, category

FROM categories

GROUP BY page_id

我不能使用COUNT(*)WHERE category =’a’然后对每个类别重复它,因为实际项目中还有更多类别.

最佳答案 如果你的表看起来像这样：

SELECT * from categories;

+---------+----------+

| page_id | category |

+---------+----------+

| 1 | a |

| 1 | b |

| 1 | a |

| 1 | c |

| 1 | a |

| 1 | b |

| 1 | a |

| 2 | d |

| 2 | d |

| 2 | c |

| 2 | d |

| 3 | a |

| 3 | b |

| 3 | c |

| 4 | c |

| 4 | d |

| 4 | c |

+---------+----------+

17 rows in set (0.00 sec)

然后您可能想尝试此查询：

SELECT c1.page_id, MAX(freq.total),

(

SELECT c2.category

FROM categories c2

WHERE c2.page_id = c1.page_id

GROUP BY c2.category

HAVING COUNT(*) = MAX(freq.total)

LIMIT 1

) AS category

FROM categories c1

JOIN (

SELECT page_id, category, count(*) total

FROM categories

GROUP BY page_id, category

) freq ON (freq.page_id = c1.page_id)

GROUP BY c1.page_id;

哪个返回：

+---------+-----------------+----------+

| page_id | MAX(freq.total) | category |

+---------+-----------------+----------+

| 1 | 4 | a |

| 2 | 3 | d |

| 3 | 1 | a |

| 4 | 2 | c |

+---------+-----------------+----------+

4 rows in set (0.00 sec)

将结果与实际频率分布进行比较：

SELECT page_id, category, COUNT(*) FROM categories GROUP BY page_id, category;

+---------+----------+----------+

| page_id | category | COUNT(*) |

+---------+----------+----------+

| 1 | a | 4 |

| 1 | b | 2 |

| 1 | c | 1 |

| 2 | c | 1 |

| 2 | d | 3 |

| 3 | a | 1 |

| 3 | b | 1 |

| 3 | c | 1 |

| 4 | c | 2 |

| 4 | d | 1 |

+---------+----------+----------+

10 rows in set (0.00 sec)

请注意,对于page_id = 3,没有前导频率,在这种情况下,此查询不保证在这种情况下将选择哪个类别.