php - MySQL Join返回的 yield 超出预期

这个问题让我睡了两天。
我有两张桌子

views

id | postid |   date     | count
=================================
13 |   8    | 2016-07-16 |  38
16 |   8    | 2016-07-17 |  35
15 |   9    | 2016-07-16 |  7
17 |   9    | 2016-07-17 |  32
14 |   12   | 2016-07-16 |  17
18 |   12   | 2016-07-17 |  13


visitors

id | postid |   date     | ip
=================================
13 |   8    | 2016-07-16 |  127.0.0.1
17 |   8    | 2016-07-17 |  127.0.0.1
18 |   8    | 2016-07-17 |  127.0.0.1
16 |   9    | 2016-07-16 |  127.0.0.1
19 |   9    | 2016-07-17 |  127.0.0.1
14 |   12   | 2016-07-16 |  127.0.0.1
15 |   12   | 2016-07-16 |  127.0.0.1
20 |   12   | 2016-07-17 |  127.0.0.1
21 |   12   | 2016-07-17 |  127.0.0.1

以及下面的查询

$query = $wpdb->get_results("
    SELECT
        SUM(a.count) AS countviews,
        COUNT(b.ip) AS countvisitors,
        a.postid
    FROM views a
    RIGHT JOIN visitors b
        ON a.postid=b.postid
        AND a.date=b.date
    WHERE
        a.date
    BETWEEN
        DATE_SUB('2016-07-17', INTERVAL 3 DAY)
    AND
        '2016-07-17'
    GROUP BY
        a.postid
    ORDER BY
        countviews DESC
");

当我打印输出时，我将看到以下结果

Array
(
    [0] => stdClass Object
        (
            [countviews] => 108
            [countvisitors] => 3
            [postid] => 8
        )

    [1] => stdClass Object
        (
            [countviews] => 60
            [countvisitors] => 4
            [postid] => 12
        )

    [2] => stdClass Object
        (
            [countviews] => 39
            [countvisitors] => 2
            [postid] => 9
        )

 )

只有[countviews]结果高于expacted。我要数一数，看看postid 8的countviews不是'108'而是'73'。奇怪的是，8号的最后一个数字是“35”108'减去'35'='73'。那么视图表是双倍计数的吗？
右连接、左连接和内部连接都给出相同的结果。

最佳答案

如果你想数数，就不能在这里加入。您建立的关系是创建视图表的倍数，以防同一个帖子的搜索参数中有多个日期。
通过使用子查询可以避免这种情况：

SELECT
    SUM(a.count) AS countviews,
    (SELECT COUNT(b.ip) FROM visitors i WHERE b.date BETWEEN DATE_SUB("2016-07-17", INTERVAL 3 DAY) AND "2016-07-17" AND i.postid = a.postid) AS countvisitors,
    a.postid
FROM views a
WHERE
    a.date
BETWEEN
    DATE_SUB('2016-07-17', INTERVAL 3 DAY)
AND
    '2016-07-17'
GROUP BY
    a.postid
ORDER BY
    countviews DESC

希望我做对了。如果有帮助，请告诉我：）

关于php - MySQL Join返回的 yield 超出预期，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38433451/