php - MySQL查询交叉引用

Question

我正在寻找一个 mysql 查询(使用 php)来一次遍历每个表，并且只显示与 cross_check.lastmod 中的结果不同的结果

t_one

guid | name | lastmod
 1   |  Joe | 2012-01-01 01:00:00
 2   |  Tom | 2012-01-02 01:00:00
 3   |  Sue | 2012-03-01 02:00:00

t_two

guid | pet  | lastmod
 4   | cat  | 2012-01-01 01:00:00
 5   | dog  | 2012-01-02 01:00:00
 6   | fish | 2012-03-01 02:00:00

t_three

guid | fruit   | lastmod
 7   |  orange | 2012-01-01 01:00:00
 8   |  pear   | 2012-01-02 01:00:00
 9   |  grape  | 2012-03-01 02:00:00

交叉检查

guid | lastmod
   1 |  2012-01-01 01:00:00
   2 |  2012-01-02 01:00:00
   3 |  2012-01-01 02:00:00
   4 |  2012-01-01 01:00:00
   5 |  2012-01-02 01:00:00
   6 |  2012-01-01 02:00:00
   7 |  2012-01-01 01:00:00
   8 |  2012-01-02 01:00:00
   9 |  2012-01-01 02:00:00

查询结果为:

t_one => 3 | Sue   | 2012-03-01 02:00:00
t_two => 6 | fish  | 2012-03-01 02:00:00
t_three => 9 | grape | 2012-03-01 02:00:00

到目前为止我的代码(需要表格交叉引用)

$tables = array('t_one', 't_two', 't_three');

  foreach ($tables AS $table) {

    $query = sprintf("SELECT * FROM '%s'",
    mysql_real_escape_string($table));

    $result = mysql_query($query);

  }

Answer 1

只需在cross_check 表中JOIN。

SELECT t_one.*
FROM t_one
JOIN cross_check ON t_one.guid=cross_check.guid
WHERE t_one.lastmod != cross_check.lastmod

虽然每个表仍然有一个查询(您可以使用联合放屁，但这不会减少查询的核心数量)。

如果你想将它们合二为一，你可以使用 UNIONs:

[query for t_one]
 UNION
[query for t_two]
 UNION
[query for t_three]

我觉得这并不比执行三个不同的查询更有效(尽管是一种直觉，未经证实的说法)。

您也可以这样做(尽管这仅在 t_* guid 之间没有交叉时才有效；如果有 t_one 将是喜欢，然后 t_two):

SELECT COALESCE(t_one.guid,t_two.guid,t_three.guid) AS guid
FROM cross_check
LEFT JOIN t_one ON t_one.guid=cross_check.guid 
LEFT JOIN t_two ON t_two.guid=cross_check.guid
LEFT JOIN t_three ON t_three.guid=cross_check.guid
WHERE (t_one.lastmod IS NOT NULL AND t_one.lastmod != cross_check.lastmod)
   OR (t_two.lastmod IS NOT NULL AND t_two.lastmod != cross_check.lastmod)
   OR (t_three.lastmod IS NOT NULL AND t_three.lastmod != cross_check.lastmod)

如果您想获取标签而不是 ID，您也可以放置 COALESCE(t_one.name, t_two.pet, t_three.fruit) AS label。