mysql - 合并 3 个表的结果

Question

这是我的数据的简化模式:

CREATE TABLE IF NOT EXISTS `posts` (
  `id`         VARCHAR(64)    NOT NULL,
  `name`       VARCHAR(64)    NOT NULL,
  `url`        VARCHAR(255)   NOT NULL,
  `data_date`  DATE                NOT NULL,
               PRIMARY KEY(`id`, `data_date`)
);

CREATE TABLE IF NOT EXISTS `post_views_by_id` (
  `id`         VARCHAR(64)    NOT NULL,
  `views`      INT(16)        NOT NULL DEFAULT 0,
  `data_date`  DATE           NOT NULL,
               PRIMARY KEY(`id`, `data_date`)
);

CREATE TABLE IF NOT EXISTS `post_views_by_url` (
  `url`        VARCHAR(255)   NOT NULL,
  `views`      INT(16)        NOT NULL DEFAULT 0,
  `data_date`  DATE           NOT NULL,
               PRIMARY KEY(`url`, `data_date`)
); 

示例数据:

INSERT INTO `posts`
  (`id`, `name`, `url`, `data_date`)
VALUES
  ("1a", "name1", "url1", "2016-08-08"),
  ("1b", "name2", "url2", "2016-08-08"),
  ("1c", "name3", "url3", "2016-08-08"),
  ("1a", "name1", "url1", "2016-08-09"),
  ("1b", "name2", "url2", "2016-08-09"),
  ("1c", "name3", "url3", "2016-08-09");


INSERT INTO `post_views_by_id`
  (`id`, `views`, `data_date`)
VALUES
  ("1a", 10, "2016-08-08"),
  ("1b", 15, "2016-08-08"),
  ("1a", 12, "2016-08-09"),
  ("1b", 17, "2016-08-09");


INSERT INTO `post_views_by_url`
  (`url`, `views`, `data_date`)
VALUES
  ("url3", 22, "2016-08-08"),
  ("url3", 12, "2016-08-09");

我正在尝试创建一个查询，该查询将返回一个帖子列表，其中包含来自 3 个表格的 View 编号。

当我只做一个连接时......

SELECT 
  p.id        AS 'post_id',
  p.name      AS 'post_name',
  p.url       AS 'post_url',
  pbi.views   AS 'post_views',
  p.data_date AS 'date'
FROM posts p
LEFT JOIN post_views_by_id pbi
  ON p.id = pbi.id
WHERE p.data_date   = "2016-08-08"
  AND pbi.data_date = "2016-08-08"

我得到一个结果:

id  name    url     views   data_date1a  name1   url1    10  August, 08 2016 00:00:001b  name2   url2    15  August, 08 2016 00:00:00

Now i want to combine the third table, where views are stored with url.

SELECT 
   p.id        AS 'post_id',
   p.name      AS 'post_name',
   p.url       AS 'post_url',
   pbi.views   AS 'post_views_id',
   pbu.views   AS 'post_views_url',
   p.data_date AS 'date'
FROM posts p
INNER JOIN post_views_by_id pbi
   ON p.id = pbi.id
INNER JOIN post_views_by_url pbu
   ON p.url = pbu.url
WHERE p.data_date   = "2016-08-08"
  AND pbi.data_date = "2016-08-08"
  AND pbu.data_date = "2016-08-08"

然后我根本没有得到任何结果..

我不太确定如何构建正确的查询。我在这里阅读了很多关于多重连接的帖子，但没有一篇真正帮助我解决问题。

预期的结果应该是:

id  name    url     views   data_date1a  name1   url1    10      August, 08 2016 00:00:001b  name2   url2    15      August, 08 2016 00:00:001c  name3   url3    22      August, 08 2016 00:00:00

SQL Fiddle for the question

Answer 1

如果没有错，您需要使用 LEFT OUTER JOIN 而不是 INNER JOIN

SELECT p.id        AS 'post_id', 
       p.NAME      AS 'post_name', 
       p.url       AS 'post_url', 
       pbi.views   AS 'post_views_id', 
       pbu.views   AS 'post_views_url', 
       p.data_date AS 'date' 
FROM   posts p 
       LEFT JOIN post_views_by_id pbi 
              ON p.id = pbi.id 
                 AND pbi.data_date = p.data_date 
       LEFT JOIN post_views_by_url pbu 
              ON p.url = pbu.url 
                 AND pbu.data_date = p.data_date 
WHERE  p.data_date = '2016-08-08'

• SQLFIDDLE DEMO