这是一个让我睡了2天的问题 .
我有2张 table
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
");
当我print_r输出时,我会看到以下结果
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]结果高于已经过期 . 我要算一下,看看来自postid 8的countviews不能是'108'而是'73' . 关于它的陌生事情是postid 8的最后一次计数是'35' . '108'减去'35'='73' . 所以视图表计数加倍?
RIGHT JOIN,LEFT JOIN和INNER JOIN给出了相同的结果 .
1 回答
如果你想数数,你不能在这里加入 . 您所 Build 的关系是创建视图表的倍数,以防您的搜索参数中存在多天相同的postid .
您可以使用子查询来避免这种情况:
希望我做对了 . 如果这有帮助,请告诉我:)