mysql - 计算以逗号分隔的值并获得另一个值的总和

Question

我有 2 个表；用户和意见。下面的示例架构和数据

users table
---------------------------------
userid
  1
  2
  3
  4
  5

opinions table
---------------------------------
opinionid   |   points   |   yes   |   no
    1             5         1,2,3      4,5
    2             5         1,3,5      2,4
    3            10         1,2,4      3,5
    4            10         1,4,5      2,3
    5            15           1      2,3,4,5

* the yes and no columns contains comma-delimited userids

期望的结果:按用户id分组，按积分排序，是，否

userid   |   points   |   yes   |   no
  1            45          5         0
  4            20          2         3
  2            15          2         3
  5            15          2         3
  3            10          2         3

Summary: for each userid, sum(points), count(yes), count(no)

谢谢!

Answer 1

你应该 normalize您的数据，而不是将值存储为逗号分隔，这将更容易在查询中处理。只需添加一个新列 user_id 作为外键，并添加一个列作为标志 YesOrNo，然后为每个意见输入用户 ID，并为每个设置一个标志值 0 或1.

---

现在，您可以使用 FIND_IN_SET功能来做你正在寻找的东西，就像这样:

SELECT
  userid,
  sum(CASE WHEN type = 'yes' THEN points ELSE 0 END), 
  SUM(CASE WHEN type = 'yes' THEN 1 ELSE 0 END) AS Yes,
  SUM(CASE WHEN type = 'no' THEN 1 ELSE 0 END) AS No
FROM
(
  SELECT 
    o.opinionid,
    o.points,
    'yes' AS type,
    u.userid
  FROM options AS o
  INNER JOIN users AS u ON FIND_IN_SET(u.userid, o.yes) <> 0
  UNION
  SELECT
    o.opinionid,
    o.points,
    'no' AS type,
    u.userid
  FROM options AS o
  INNER JOIN users AS u ON FIND_IN_SET(u.userid, o.no) <> 0
) AS t
GROUP BY userid;

• SQL Fiddle Demo