当连接条件在其他表中时 MySQL 排除连接

Question

我正在尝试订购分组结果集。问题是其中一个连接条件引用了另一个表。具体来说，我需要从 table_a 中选择组内具有最高值的记录，但组 id 是另一个表中的字段。

以下是我尝试过的一些不同的变化。每当我添加 GROUP BY pr.id 时，一些已知应该位于前 3 名的结果被排除。当我不添加它时，每组会得到超过 1 条记录。

非常感谢任何帮助。

SELECT * FROM ivalues AS iv

INNER JOIN mprod AS p ON (p.id = iv.p_id)
INNER JOIN contact AS pr ON (pr.id = p.pr_id)


LEFT OUTER JOIN ivalues i2
    ON i2.p_id = p2.id

    AND i2.period = iv.period
    AND i2.current = iv.current
    AND i2.cert = iv.cert
    AND i2.exists = iv.exists
    AND iv.value > i2.value
WHERE 
iv.period = 0
AND iv.current = 1
AND iv.cert = 1
AND p.type_id = 15747
AND iv.exists = 1
AND i2.id IS NULL
GROUP BY pr.id  
ORDER BY iv.value ASC
LIMIT 10;

SELECT * FROM ivalues AS iv

INNER JOIN mprod AS p ON (p.id = iv.p_id)
INNER JOIN contact AS pr ON (pr.id = p.pr_id)

LEFT OUTER JOIN contact pr2 
    ON pr2.id = p.pr_id

LEFT OUTER JOIN mprod p2
    ON p2.type_id = p.type_id           

LEFT OUTER JOIN ivalues i2
    ON i2.p_id = p2.id

    AND i2.period = iv.period
    AND i2.current = iv.current
    AND i2.cert = iv.cert
    AND i2.exists = iv.exists
    AND iv.value > i2.value
WHERE 
iv.period = 0
AND iv.current = 1
AND iv.cert = 1
AND p.type_id = 15747
AND iv.exists = 1
AND i2.id IS NULL

ORDER BY iv.value ASC
LIMIT 10;

SELECT * FROM ivalues AS iv

INNER JOIN mprod AS p ON (p.id = iv.p_id)
INNER JOIN contact AS pr ON (pr.id = p.pr_id)

LEFT OUTER JOIN contact p2 
    ON p2.pr_id = p.pr_id   
    AND p2.type_id = p.type_id

INNER JOIN mprod p2
    ON p2.type_id = p.type_id           

INNER JOIN ivalues i2
    ON i2.p_id = p2.id

    AND i2.period = iv.period
    AND i2.current = iv.current
    AND i2.cert = iv.cert
    AND i2.exists = iv.exists
    AND iv.value > i2.value

WHERE 
iv.period = 0
AND iv.current = 1
AND iv.cert = 1
AND p.type_id = 15747
AND iv.exists = 1

ORDER BY iv.value ASC
LIMIT 3;

Answer 1

我认为您需要将 ivalues 和 mprod 作为子选择连接，或者作为外部连接来排除非最大值，或者作为内部组来查找最大值。希望它看起来像这样：

SELECT * FROM contact
  INNER JOIN mprod ON mprod.contact_id = contact.id
  INNER JOIN ivalues ON ivalues.mprod_id = mprod.id
  LEFT OUTER JOIN (
    SELECT mprod.contact_id, ivalues.*
      FROM mprod
      INNER JOIN ivalues ON mprod.id = ivalues.mprod_id
      WHERE ivalues.period = 0
        AND ivalues.current = 1
        AND ivalues.cert = 1
        AND mprod.type_id = 15747
        AND ivalues.exists = 1
  ) AS pv ON pv.contact_id = contact.id AND pv.value > ivalues.value
  WHERE ivalues.period = 0
    AND ivalues.current = 1
    AND ivalues.cert = 1
    AND mprod.type_id = 15747
    AND ivalues.exists = 1
    AND pv.id IS NULL
  GROUP BY pr.id  
  ORDER BY iv.value ASC
  LIMIT 10;