mysql - 从多个表中缺少字段的SQL查询

希望有人可以帮助我。我有5个表格，下面是简化的示例：

catalog_items：将有关项目的信息存储在目录中。

CREATE TABLE catalog_items (
  item_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  create_date DATETIME NOT NULL,
  valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,

  PRIMARY KEY (item_id)
);

用户：存储用户信息。

CREATE TABLE users (
  user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,

  PRIMARY KEY (user_id)
);

user_contribution_types：用户可以向目录中贡献商品来赚取积分，此表描述了它是什么类型的贡献以及他们赚了多少。

CREATE TABLE user_contribution_types (
  type_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  description TEXT NOT NULL,
  credits DECIMAL(5,2) UNSIGNED NOT NULL,
  valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,

  PRIMARY KEY (type_id)
);

user_contributions：用于关联以上所有内容的表（哪个用户贡献了x个项目）。

CREATE TABLE user_contributions (
  user_id INTEGER UNSIGNED NOT NULL,
  item_id INTEGER UNSIGNED NOT NULL,
  type_id INTEGER UNSIGNED NOT NULL,
  create_date DATETIME NOT NULL,
  valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,

  FOREIGN KEY (user_id)
    REFERENCES users(user_id),
  FOREIGN KEY (item_id)
    REFERENCES catalog_items(item_id),
  FOREIGN KEY (type_id)
    REFERENCES user_contribution_types(type_id)
);

ending_items：必须批准所有贡献，因此将它们存储在临时表中。这些条目可能来自没有帐户的新用户，这就是为什么只有电子邮件而不是user_id的原因。

CREATE TABLE pending_items (
  pending_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  title VARCHAR(255) NOT NULL,
  create_date DATETIME NOT NULL,
  valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,

  PRIMARY KEY (pending_id)
);

题

我正在尝试从目录中选择已批准和待审核的项目；但是，到目前为止，我想出的最好办法是将它们拆分：

仅选择批准的捐款：

SELECT a.item_id, c.title, a.create_date, b.description, b.credits
FROM user_contributions a, user_contribution_types b, catalog_items c
WHERE a.type_id = b.type_id AND a.item_id = c.item_id
AND a.valid = TRUE AND b.valid = TRUE AND c.valid = TRUE
ORDER BY c.create_date DESC LIMIT 0, 10

仅选择待处理的捐款：

SELECT pending_id, title, create_date FROM pending_items WHERE email = '[email protected]' AND valid = TRUE
ORDER BY create_date DESC LIMIT 0, 10

有没有一种方法可以将上述两个查询组合在一起，以得到如下所示的结果集，其中缺少的字段仅显示为NULL？

| item_or_pending_id | title | create_date         | description | credits | approved |
| ------------------ | ----- | ------------------- | ----------- | ------- | -------- |
| 14                 | test4 | 2017-04-08 04:41:21 | bar         | 1.00    | true     |
| 14                 | test3 | 2017-04-06 01:23:45 | NULL        | NULL    | false    |
| 11                 | test2 | 2017-02-03 12:30:00 | NULL        | NULL    | false    |
| 9                  | test1 | 2017-01-25 05:16:18 | foo         | 1.00    | true     |

最佳答案

是的:-)

SELECT a.item_id AS item_or_pending_id,
       c.title,
       a.create_date,
       b.description,
       b.credits,
       true      AS approved
FROM   user_contributions a,
       user_contribution_types b,
       catalog_items c
WHERE  a.type_id = b.type_id
       AND a.item_id = c.item_id
       AND a.valid = true
       AND b.valid = true
       AND c.valid = true
UNION
SELECT pending_id,
       title,
       create_date,
       NULL,
       NULL,
       false
FROM   pending_items
WHERE  email = '[email protected]'
       AND valid = true
ORDER  BY create_date DESC
LIMIT  0, 10

关于mysql - 从多个表中缺少字段的SQL查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43365695/