如何在不使用 UNION 的情况下在相关表之间执行此查询？

Question

假设我有两个单独的表，我要观察它们来查询。这两个表都与第三个表有关系。如何使用单个非基于 UNION 的查询来查询两个表？

这是一个理论上的例子。我有一个用户表。该用户可以同时拥有 CD 和书籍。我想通过与字符串匹配的单个查询来查找该用户的所有书籍和 CD（在本例中为“awesome”）。

基于 UNION 的查询可能如下所示：

SELECT "book" AS model, name, ranking 
 FROM book 
WHERE name LIKE 'Awesome%' 
UNION 
SELECT "cd" AS model, name, ranking 
  FROM cd 
 WHERE name LIKE 'Awesome%' 
ORDER BY ranking DESC

如何在没有 UNION 的情况下执行这样的查询？如果我从用户到书籍和 CD 进行简单的左连接，我们最终得到的结果总数等于匹配 CD 的数量乘以匹配书籍的数量。是否有 GROUP BY 或其他编写查询的方式来解决此问题？

（编辑：我想避免使用 Union 方法的原因是因为这实际上是一个 DQL 查询，而 Doctrine 不支持 UNION。如果没有 UNION 就无法做到这一点，我将采用本机 SQL 路线。此外，真正的查询包含一堆附加列，这些列在上面的示例中不能很好地相互映射。）

Answer 1

想想如何在面向对象的应用程序中对此进行建模。您将创建一个针对书籍和 CD 进行扩展的超类，然后您的用户将拥有一组收藏品。该集合中的任何给定对象要么是一本书，要么是一张 CD（或其他类型的收藏品），但它恰好具有这些子类型之一。

您可以使用 SQL 执行类似的操作，通过创建与超类型相对应的表：

CREATE TABLE Collectibles (
  collectible_id SERIAL PRIMARY KEY,
  user_id        INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

然后每个子类型都包含一个引用以使其可收集：

CREATE TABLE Books (
  book_id   BIGINT UNSIGNED PRIMARY KEY
  book_name VARCHAR(100) NOT NULL,
  FOREIGN KEY (book_id) REFERENCES Collectibles(collectible_id)
);

CREATE TABLE CDs (
  cd_id   BIGINT UNSIGNED PRIMARY KEY
  cd_name VARCHAR(100) NOT NULL,
  FOREIGN KEY (cd_id) REFERENCES Collectibles(collectible_id)
);

现在您可以执行查询并确保您不会得到笛卡尔积：

SELECT u.*, COALESCE(b.book_name, d.cd_name) AS media_name
FROM Users u
JOIN Collectibles c ON (u.user_id = c.user_id)
LEFT OUTER JOIN Books b ON (b.book_id = c.collectible_id)
LEFT OUTER JOIN CDs d ON (d.cd_id = c.collectible_id);

Answer 2

如果您试图避免联合，一种方法是创建视图。

编辑：要 创建视图，您有两个

选项如果您的查询仅用于选择记录，那么应该没有问题

CREATE VIEW media AS
SELECT "book" AS model, name, ranking 
 FROM book 
WHERE name LIKE 'Awesome%' 
UNION
SELECT "cd" AS model, name, ranking 
  FROM cd 
 WHERE name LIKE 'Awesome%' 
ORDER BY ranking DESC

如果您需要可以更新的视图，那么如果您重构，它可能会飞起来：

• 创建将保存所有数据和媒体类型的表
• 创建两个将在媒体上分割数据的视图类型（因为这些视图是对底层表的简单 1:1 查询，它们应该是可更新的，并且您应该能够在 ORM 映射或其他 SQL 查询中使用它们）

EDIT2：我忘记评论 UNION ALL 是必须结束的事实UNION 除非您希望 MySQL 在每次运行视图/查询时开始在磁盘上构建索引（感谢 HLGEM）。

Answer 3

除非有令人信服的理由不使用 UNION，否则就使用 UNION。

Answer 4

也许你可以尝试这样的事情......该示例假设第三个表称为 User：

$q = Doctrine_Query::create()
  ->select('c.cd, b.book')
  ->from('User u')
  ->LeftJoin('Cd c ON u.user_id = c.user_id AND c.name LIKE ?, 'Awesome%')
  ->LeftJoin('Book b ON u.user_id = b.user_id AND b.name LIKE ?, 'Awesome%');
$result = $q->execute();