在 mysql 中写一个硬查询给我? [英] a hard query to write in mysql to me?

问题描述

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

1,output the content_id field and descending it, the rule is according to the value(the count of each content_id 's value=1 minus the count of each content_id 's value=0 ,each content_id has many value=0 or value=1). where the value_type=option

有没有办法做到这一点?

is there a way to get that?

即:

$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的降序规则，

i want to use the count value = 1 minus the count value = 0. this is the content_id's descending rule,

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

For each content_id, the number of rows with value=1 minus the number of rows with value=0

推荐答案

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

这篇关于在 mysql 中写一个硬查询给我?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！