mysql - GROUP BY 聚合和 INNER JOIN

Question

我试图尽可能地缩小问题的范围，它仍然是相当多的东西。这是无法按我想要的方式工作的查询:

SELECT *, MAX(tbl_stopover.dist)
FROM tbl_stopover
INNER JOIN
  (SELECT edges1.id id1, edges2.id id2, COUNT(edges1.id) numConn
  FROM tbl_edges edges1
  INNER JOIN tbl_edges edges2
  ON edges1.nodeB = edges2.nodeA
  GROUP BY edges1.id HAVING numConn = 1) AS tbl_conn
ON tbl_stopover.id_edge = tbl_conn.id1
GROUP BY id_edge

这是我得到的:

|id | edge | dist | id1 | id2 | numConn | MAX(tbl_stopover.dist) |
------------------------------------------------------------------
|2  | 23   | 2    | 23  | 35  | 1       | 9                      |
|4  | 24   | 5    | 24  | 46  | 1       | 9                      |
------------------------------------------------------------------

这就是我想要的:

|id | edge | dist | id1 | id2 | numConn | MAX(tbl_stopover.dist) |
------------------------------------------------------------------
|3  | 23   | 9    | 23  | 35  | 1       | 9                      |
|5  | 24   | 9    | 24  | 46  | 1       | 9                      |
------------------------------------------------------------------

但让我详细说明一下......

我有一个图表，让我们这样说:

    node1
      |
    node2
   /     \
node3    node4
  |       |
node5    node6

因此我有一个像这样调用 tbl_edges 的表:

| id  | nodeA | node B |
------------------------
| 12  |   1   |    2   |
| 23  |   2   |    3   |
| 24  |   2   |    4   |
| 35  |   3   |    5   |
| 46  |   4   |    6   |
------------------------

现在每个 edge 在一定距离(到 nodeA)都有“stop_over”。因此我有一个像这样的表 tbl_stopover:

| id  | edge  |  dist  |
------------------------
|  1  |  12   |    5   |
|  2  |  23   |    2   |
|  3  |  23   |    9   |
|  4  |  24   |    5   |
|  5  |  24   |    9   |
|  6  |  35   |    5   |
|  7  |  46   |    5   |
------------------------

为什么要查询？
假设我想计算 stop_over 之间的距离。 在一个边缘内是没有问题的。 跨越 边界变得更加困难。但是，如果我有两条连接的边并且没有其他连接，我也可以计算距离。这里有一个例子，假设所有边的 length 都是 10。:

edge23 在 dist=9 处有一个 stop_over(id=3)，edge35 有一个 stop_over(id=6) 在 dist=5。因此这两个 stop_over 之间的距离是:

dist = (length - dist_id3) + dist_id5 = (10-9) + 5

我不确定我是否表达清楚了。如果这不能理解，请随时提出问题，我会尽力使它更易于理解。

Answer 1

MySQL 允许您做一些愚蠢的事情 - 在聚合查询中显示不属于 GROUP BY 或聚合函数(如 MAX)一部分的字段。执行此操作时，您会得到其余字段的随机(如您所说)结果。

在您的查询中，您正在执行此操作两次 - 在您的内部查询中一次(id2 不是GROUP BY 或聚合的一部分)一次在外面。

为随机结果做好准备!

要修复它，请尝试这样的操作:

SELECT tbl_stopover.id,
       tbl_stopover.dist,
       tbl_conn.id1,
       tbl_conn.id2,
       tbl_conn.numConn,
       MAX(tbl_stopover.dist)
FROM tbl_stopover
INNER JOIN
  (SELECT edges1.id id1, edges2.id id2, COUNT(edges1.id) numConn
  FROM tbl_edges edges1
  INNER JOIN tbl_edges edges2
  ON edges1.nodeB = edges2.nodeA
  GROUP BY edges1.id, edges2.id
  HAVING numConn = 1) AS tbl_conn
ON tbl_stopover.id_edge = tbl_conn.id1
GROUP BY tbl_stopover.id,
         tbl_stopover.dist,
         tbl_conn.id1,
         tbl_conn.id2,
         tbl_conn.numConn

主要变化是显式字段列表(请注意，我删除了 id_edge，因为您在 id1 上加入并且已经有了该字段)，以及添加了其他字段内部和外部 GROUP BY 子句。

如果这为您提供了比您想要的更多的行，那么您可能需要更多地解释您想要的结果集。这样的事情是确保您获得适当分组的唯一方法。