php - MySQL 计算复杂的查询结果？

Question

我有以下查询:

$count = (SELECT COUNT(*) FROM post GROUP BY ID
HAVING ID NOT IN (SELECT taxiID FROM taxi WHERE userID = '.$userID.' AND value IS NOT NULL)
ORDER BY postID), OBJECT);

计数包含这个:

count = Array ( [0] => stdClass Object ( [COUNT(*)] => 1 ) [1] => stdClass Object ( [COUNT(*)] => 1 ) [2] => stdClass Object ( [COUNT(*)] => 1 ) [3] => stdClass Object ( [COUNT(*)] => 1 ) [4] => stdClass Object ( [COUNT(*)] => 1 ) [5] => stdClass Object ( [COUNT(*)] => 1 ) [6] => stdClass Object ( [COUNT(*)] => 1 ) [7] => stdClass Object ( [COUNT(*)] => 1 ) [8] => stdClass Object ( [COUNT(*)] => 1 ) [9] => stdClass Object ( [COUNT(*)] => 1 ) [10] => stdClass Object ( [COUNT(*)] => 1 ) [11] => stdClass Object ( [COUNT(*)] => 1 ) [12] => stdClass Object ( [COUNT(*)] => 1 ) [13] => stdClass Object ( [COUNT(*)] => 1 ) [14] => stdClass Object ( [COUNT(*)] => 1 ) [15] => stdClass Object ( [COUNT(*)] => 1 ) [16] => stdClass Object ( [COUNT(*)] => 1 ) [17] => stdClass Object ( [COUNT(*)] => 1 ) [18] => stdClass Object ( [COUNT(*)] => 1 ) [19] => stdClass Object ( [COUNT(*)] => 1 )

我需要计算上面传递的结果数。问题是，我不知道如何使用结果!

我有这段代码，但现在它不起作用:

<?php if($count[0]->{'COUNT(*)'} > 10){ ?
    echo "Load More";
}else { 
    echo "Nothing to load";
} ?>

$count 应该大于 10，我的 php 应该回显 Load More 但它没有回显 Nothing to load。

出租车表是这样的:

ID    taxiID    userID    value
1     1         1         1
2     1         6         1
3     1         4         0
4     2         1         0
5     2         6         1
6     2         4         0
7     3         6         1
8     3         4         0

帖子表如下所示:

ID    postID    randomNum
1     1         564
2     2         789
3     3         234
4     4         845
5     5         089

假设 $userID 为 1，则查询返回 postID 1,3,4,5(1 被点赞，3 不被用户 1 点赞且未点赞，4 和 5 未被任何用户点赞且未点赞)。因此 $count 应该包含 4(找到 4 个结果)。

如果我的查询效率低下，我该如何调整它以提高效率？

最终，问题是我该怎么做:

if ($count > 10) {}

Answer 1

您的问题是，您的查询没有返回您认为返回的内容(它总是有助于独立运行查询，以查看结果集是否符合您的预期)。

好吧，让我们分解一下。

It is counting all posts that the user has not liked or disliked. likes and dislikes are stored in the taxi table. taxi.taxiID matches post.ID. Hence if the userID with any value that isn't null is found, ignore that post.ID. I am counting those post.ID which are not ignored

您正在尝试计算所有在出租车表中没有与该用户 ID 匹配的记录的帖子。您在这里想要的是 JOIN 表并在 post 中获取那些通常会被连接排除的行。这是通过左外连接实现的

(已编辑)

SELECT p.ID, t.taxiID
FROM post p LEFT OUTER JOIN taxi t ON p.ID = t.taxiID AND t.userID = '.$user.'
HAVING t.taxiID IS NULL

HAVING 子句表示:只有结果集中没有相应 t.taxiID 的那些行。

如果您运行此查询并获得预期的行集(该用户没有喜欢或不喜欢的帖子)，那么您可以添加一个外部查询来计算返回的行数:

SELECT COUNT(*) as count FROM (
    SELECT p.ID, t.taxiID
    FROM post p LEFT OUTER JOIN taxi t ON p.ID = t.taxiID AND t.userID = '.$user.'
    HAVING t.taxiID IS NULL
) a

这应该返回一个名为 count 的标量。在这种情况下，您可以说:

if ($count[0]->count > 10) { blah blah blah }

(第二次编辑)此内部查询将为您提供那些在出租车表中具有 value = 1 或根本没有值的帖子，这会导致您的示例返回 4:

SELECT p.ID, t.taxiID, t.value
FROM post p LEFT OUTER JOIN taxi t ON p.ID = t.taxiID AND t.userID = '.$user.'
HAVING t.taxiID IS NULL OR t.value = 1