对于我的投票系统,我有两个可能的投票值。1或-1,这个值将被保存在数据库中。
现在我选择/计数像field_up和field_down分开的值。我想改变我的查询只输出一个值,如:
如果我们有'+6'和-5,它应该返回+1 (+)如果我们有-3和+2 I,它应该返回-1(带-)
现在我有以下查询
SELECT
SUM(CASE thumb when 1 then 1 else 0 end) AS "thumbs_up",
SUM(CASE thumb when -1 then 1 else 0 end) AS "thumbs_down"
FROM ..etc
提前感谢!
Thea。SELECT SUM(thumb) AS votes FROM your_table
try this
SELECT
SUM( CASE WHEN thumb =1 THEN 1 ELSE 0 END) as "thumbs_up",
SUM( CASE WHEN thumb =-1 THEN 1 ELSE 0 END) as "thumbs_down",
FROM ..etc
几件事。您可以将查询大大简化为:
SELECT
SUM(thumb = 1) AS thumbs_up,
SUM(thumb = -1) AS thumbs_down
FROM ..etc
因为在mysql (only)中,true
是1
, false
是0
。
其次,您需要对该查询进行查询以调整输出:
SELECT concat(if(thumbs_up > thumbs_down, '+', ''), thumbs_up - thumbs_down)
FROM (
SELECT
SUM(thumb = 1) AS thumbs_up,
SUM(thumb = -1) AS thumbs_down
FROM ..etc
) x