基于各个COUNT（*）值的查询

Question

I have two tables ('posts' and 'votes') which allows users to vote thumbs-up (where rating = 1 in 'votes') or thumbs-down (where rating = 0 in 'votes') on posts.

I'm joining the two tables in a query and am trying to filter the results so that a row only shows if it has 2+ positive (rating = 1) ratings AND 2+ negative (rating = 0) ratings from the 'votes' table ('post_id' and 'rating' columns).

This is what I got, but it doesn't work as intended, since it brings back results which also have only 1 positive and 1 negative vote -- although you can't see this due to the conglomeration of votes for each post -- which isn't what I want (the 'HAVING' line isn't working as intended):

SELECT *, COUNT(*)
FROM posts p
JOIN votes v ON p.id = v.post_id
WHERE rating = 1 OR rating = 0
GROUP BY p.id 
HAVING COUNT(rating = 1) > 1 AND COUNT(rating = 0) > 1

+----+---------+----------+----------+
| id | post_id | rating   | COUNT(*) |
+----+---------+----------+----------+
|  4 |       4 |        0 |        2 |
|  7 |       7 |        0 |        2 |
|  9 |       9 |        0 |        2 |
| 83 |      83 |        1 |        2 |
+----+---------+----------+----------+

Answer 1

I think you want this having clause:

SELECT p.*, COUNT(*)
FROM posts p JOIN
     votes v
     ON p.id = v.post_id
WHERE rating IN (1, 0)
GROUP BY p.id 
HAVING ABS(SUM(rating = 1) - SUM(rating = 0)) > 1;

EDIT:

The above is doing the right thing. Here is a SQL Fiddle showing the results.

EDIT II:

I might have misinterpreted the question. I understood the question to be a net positive of two votes or a net negative of two votes. You seem to want just at least two votes either way. That having clause is:

HAVING SUM(rating = 1) > 1 or SUM(rating = 0) > 1