php - 按两个字段的计数之和排序

Question

好的，我这里有一个有趣的问题。我有两张 table ，提示和投票。提示看起来像这样:

tip_id, challenge_id, user_id, ip_address, tip_date, tip_text

投票看起来像这样:

tip_id, ip_address, vote_date, vote

用户可以发布关于挑战的提示，然后其他用户可以通过赞成或反对的方式对提示进行投票。 votes中的vote字段是一个bool，0表示不赞成，1表示赞成。挑战可以有任意数量的提示。

我绞尽脑汁，就是无法让它发挥作用。我需要一个查询，可以获取特定提示的赞成票和反对票的数量，这两个数字的差值(因此，如果有 2 个赞成票和 1 个反对票，则差值为 1)作为评级，以及从 tips 表中获取 tip 数据，然后将其按最高评级排在第一位。假设我们有两个提示，a 和 b:

a:向上 3 个，向下 2 个(差异:1)b: 4 up, 1 down (diff: 3)

在这种情况下，b 将排在第一位，因为它的评级高于 a，赞成票和反对票之间的差异更大。

例如，假设我有一个 id 为 1 的小费，它有 4 个赞成票和 2 个反对票，这意味着相差 2，并且这个小费链接到挑战 id 5，我的返回可能看起来像这样:

up | down | rating | tip_id | tip_date | user_id | tip_text
------------------------------------------------------------
3  | 2    | 1      | 1      | 12345678 | 1       | "this is a tip"

现在我实际上已经能够做到这一点，但是如果有多个挑战提示，我遇到的问题是获取多行。假设挑战 ID 5 有 2 个提示，那么我应该得到这样的结果:

up | down | rating | tip_id | tip_date | user_id | tip_text
------------------------------------------------------------
3  | 2    | 1      | 1      | 12345678 | 1       | "this is a tip"
4  | 1    | 3      | 1      | 87654321 | 2       | "this is also a tip"

每个提示在结果中都有自己的行。现在发生的事情是我得到一行，上下值是该挑战的赞成票和反对票的总数，而不是小费。因此，使用上面的第二个示例，我将得到一行，而不是两个单独的行，其中 up 的值为 7，down 为 3，即针对该挑战的所有提示的赞成票和反对票的总数。

我确定这将涉及加入、排序和分组，但我不关心查询有多复杂，我只是不想在 PHP 中执行它。最终，我想为查询提供一个挑战 ID 并让它获得 X 行，其中 X 是该挑战 ID 的提示数，每行将仅包含该提示 ID 的计数。

任何帮助将不胜感激，因为我的头发快要拔掉了。不幸的是，这是我项目的定义点，评分较高的提示需要在列表中显示在较高位置。

编辑:这是我当前几乎可以使用的查询。在睡了一夜好觉后，我对它做了一些调整，它基本上可以正常工作，但仍然有一个我无法解释的问题:

SELECT
  COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
  COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
  (COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END)-COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END)) AS rating,
  T.*
  FROM votes V, tips T
  WHERE T.challenge_id = 10
  GROUP BY V.tip_id
  ORDER BY rating DESC

这得到了我需要的一切，所有提示和针对特定挑战的投票，每个提示都有一个新行(而不是像我之前得到的那样的总数)。问题是，我的赞成票和反对票，还有评级，是它应该的两倍。因此，如果我有一个有 3 票赞成票和 1 票反对票的小费，因此评级为 2，则此查询将显示我有 6 票赞成票和 2 票反对票，评级为 4。我想我可以简单地划分用 PHP 增加了两个，但这并不能解决问题，它只是一个创可贴。谁能明白为什么我得到的数字是我应该得到的两倍？我会继续弄乱它，也许会尝试 LEFT JOIN，希望我能弄清楚，但多几双眼睛会很好。

Answer 1

这样的事情怎么样:

SELECT v.up, v.down, (v.up - v.down) AS rating, t.tip_id, t.tip_date, t.user_id, t.tip_text
FROM tips AS t
INNER JOIN votes AS v
ON t.tip_id = v.tip_id
WHERE challenge_id=1
ORDER BY rating DESC;

然后你可以在你的 PHP 中设置 WHERE 子句的 challenge_id 值，然后再将它传递给 mysql。我认为这应该给你你想要的东西，虽然因为我使用了 INNER JOIN 你需要确保“tips”中的每一行在“votes”中都有一行或者它'将被忽略。

编辑:

根据新信息(抱歉我之前没有正确阅读问题，希望这次我没看错!):

 SELECT
  COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
  COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
  (COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END)-COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END)) AS rating,
  T.*
  FROM votes V
  INNER JOIN tips AS T
  ON T.tip_id = V.tip_id
  WHERE T.challenge_id = 1
  GROUP BY V.tip_id
  ORDER BY rating DESC;

我刚刚在 SQLite3 中(在简化表上)对此进行了测试，它似乎工作正常。我使用的数据是:

sqlite> select * from votes
   ...> ;
tip_id      vote      
----------  ----------
2           1         
2           1         
2           1         
2           0         
2           0         
3           0         
3           0         
3           0         
3           1         
4           0         
4           0         
4           1         
4           1         
4           1         
4           1    

结果是:

up          down        rating      tip_id      challenge_id  user_id   
----------  ----------  ----------  ----------  ------------  ----------
4           2           2           4           1             4         
3           2           1           2           1             2         
1           3           -2          3           1             3