php - 在 mysql 中向我写一个困难的查询？

Question

field:   content_id, value=1 or  value=0, value_type=option
table:  votingapi_vote

1、输出content_id字段，并降序输出，规则是根据值(每个content_id的value个数=1减去每个content_id的value个数=0，每个content_id有多个value=0或值=1)。其中value_type=option

有办法得到它吗？

即:

$query1=mysql_query(SELECT content_id, COUNT(*) FROM votingapi_vote WHERE value_type = option  AND value = 1 GROUP BY content_id)

$query2=mysql_query(SELECT content_id, COUNT(*) FROM votingapi_vote WHERE value_type = option  AND value = 0 GROUP BY content_id)

我想用计数值=1减去计数值=0。这是content_id的降序规则，

对于每个 content_id，value=1 的行数减去 value=0 的行数

Answer 1

SELECT content_id,
       SUM(CASE WHEN value=1 THEN 1 
                WHEN value=0 THEN -1 
                ELSE 0 
           END) AS ContentSum
    FROM votingapi_vote
    WHERE value_type = 'option'
    GROUP BY content_id
    ORDER BY ContentSum DESC