MySQL计数复杂查询结果？ [英] MySQL count complex query results?

问题描述

我有以下查询：

$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 )

结果由上述交付。事情是，我不知道如何使用的结果！

I need to count the number of results delivered by the above. Thing is, I have no idea how to use the result!

我有此代码，但现在无法使用：

I had this code but now it won't work:

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

$ count应该大于10，我的php应该回显Load更多，但它回显无负载

$count should be more than 10 and my php should echo Load More but it is echoing 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个结果）。

Assuming $userID is 1, the query returns postID 1,3,4,5 (1 is liked, 3 is not liked and not disliked by user 1, 4 and 5 are not liked and not disliked by any user). Therefore $count should contain 4 (4 results are found).

如果我的查询是低效的，我该如何调整它是高效的？

If my query is inefficient, how do I adapt it to be efficient?

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

Ultimately, the question is how do I do something like:

if ($count > 10) {}

推荐答案

您的问题是， 't返回你认为它返回（它总是有助于运行你查询单独，看看结果集是否是你期望的）。

Your problem is, your query isn't returning what you think it returns (it always helps to run you query standalone, to see if the result set is what you expect).

右键，让我们打破这个

它会计算用户没有赞成或不喜欢的所有帖子。喜欢和不喜欢被存储在出租车的表。 taxi.taxiID与post.ID匹配。因此，如果找到任何值不为null的userID，请忽略该post.ID。

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

您正在计算所有帖子不要

You're trying count all posts that don't have a matching record in the taxi table, for that userID. What you want here is to JOIN the tables and get those rows in post that would normally be excluded by the join. This is achieved by an left outer join

（编辑）实现的。

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的行。

That HAVING clause is saying: only those rows in the resultset that didn't have a corresponding t.taxiID.

如果运行此查询并获取预期的行没有该用户的喜欢或不喜欢）那么您可以添加一个外部查询来计算返回的行数：

If you run this query and get the expected set of rows (posts that do not have likes or dislikes by that user) THEN you can add an outer query to count the number of returned rows:

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

单个标量命名计数。在这种情况下，您可以说：

This should return a single scalar named count. In this case you'll be able to say:

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

（第二次编辑您的那些帖子在出租车表中有值= 1，或没有值，这导致4为您的示例返回：

(2nd edit) This inner query will get you those posts that have either value = 1 in the taxi table, or no value at all, which results in 4 being returned for your example:

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

这篇关于MySQL计数复杂查询结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！