Mysql查看多个条目

Question

我有一个名为 POINTS 的 mysql 表，如下所示:

userid1 | userId2 | points
---------------------------
    1   |    1    |   3
    1   |    1    |   2
    1   |    2    |   5
    2   |    1    |   4
    1   |    3    |   5

我正在尝试像这样创建此表的 View :

userId | gained | received | spent | current
--------------------------------------------
   1   |    5   |     4    |  10   |   -1
   2   |    0   |     5    |   4   |    1
   3   |    0   |     5    |   0   |    5

列的算法在哪里:

• userId，在 POINTS 表中的 userId1 或 userId2 列中找到的 userId 的唯一值
• 得到，POINTS表中userId1 = userId AND userId2 = userId的点数之和
• 收到，POINTS表中userId1 != userId AND userId2 = userId的点数总和
• 花费，POINTS表中userId1 = userId AND userId2 != userId的点数总和
• 当前的，获得的 + 收到的 - 花费的

我对 mysql 有点陌生，我的问题是如果我进入多个子查询，我不知道如何为不同的用户保存总和值。任何帮助将不胜感激

更新:示例中的数字是准确的

Answer 1

这是结构化查询语言的结构化部分的直接应用。

您需要两个关键组件子查询才能将其放在一起。一个是每个用户获得的点数列表。就像这样。 ( http://sqlfiddle.com/#!2/1b1884/1/0 )

                      SELECT userid1 AS userid,
                             SUM(points) AS gained
                        FROM points
                       WHERE userid1 = userid2
                       GROUP BY userid1

同样，您可以生成花费和收到的积分 ( http://sqlfiddle.com/#!2/1b1884/5/0 )

                      SELECT userid1 AS userid,
                             SUM(points) AS spent
                        FROM points
                       WHERE userid1 != userid2
                       GROUP BY userid1

和(http://sqlfiddle.com/#!2/1b1884/7/0)

                      SELECT userid2 AS userid,
                             SUM(points) AS received
                        FROM points
                       WHERE userid1 != userid2
                       GROUP BY userid2

您还碰巧需要一个子查询来为您提供所有不同用户的列表。如果你有一个单独的 user 表，你没有在这里提到，那会比这更好。 ( http://sqlfiddle.com/#!2/1b1884/9/0 )

                      SELECT DISTINCT userid1 userid FROM points
                       UNION
                      SELECT DISTINCT userid2 userid FROM points

现在，您需要将这四个子查询连接在一起，这就是您的结果。这是大纲。

SELECT a.userid, 
       IFNULL(g.gained,0) gained, 
       IFNULL(s.spent,0) spent,
       IFNULL(r.received,0) received,
       IFNULL(g.gained,0)-IFNULL(s.spent,0)+IFNULL(r.received,0) total
  FROM (   /* all users subquery */
       ) a
  LEFT JOIN (   /* gained subquery */
       ) g ON a.userid = g.userid
  LEFT JOIN (   /* received subquery */
       ) r ON a.userid = r.userid
  LEFT JOIN (   /* spent subquery */
       ) s ON a.userid = s.userid
 ORDER BY a.userid

最后，像这样把它们放在一起(http://sqlfiddle.com/#!2/1b1884/13/0)

SELECT a.userid, 
       IFNULL(g.gained,0) gained, 
       IFNULL(s.spent,0) spent,
       IFNULL(r.received,0) received,
       IFNULL(g.gained,0)-IFNULL(s.spent,0)+IFNULL(r.received,0) total
  FROM (   /* all users subquery */
                      SELECT DISTINCT userid1 userid FROM points
                       UNION
                      SELECT DISTINCT userid2 userid FROM points
       ) a
  LEFT JOIN (   /* gained subquery */
                      SELECT userid1 AS userid,
                             SUM(points) AS gained
                        FROM points
                       WHERE userid1 = userid2
                       GROUP BY userid1
       ) g ON a.userid = g.userid
  LEFT JOIN (   /* received subquery */
                      SELECT userid2 AS userid,
                             SUM(points) AS received
                        FROM points
                       WHERE userid1 != userid2
                       GROUP BY userid2
       ) r ON a.userid = r.userid
  LEFT JOIN (   /* spent subquery */
                      SELECT userid1 AS userid,
                             SUM(points) AS spent
                        FROM points
                       WHERE userid1 != userid2
                       GROUP BY userid1
       ) s ON a.userid = s.userid
 ORDER BY a.userid

如果您将其视为四层俱乐部三明治，其中子查询是奶酪片，而 JOIN 子句是面包片，这可能会有所帮助。我承认这是一个满口!