计算子查询中的行数

Question

如何将 SELECT 查询中的行计数为值？ 如

SELECT FUCNTIONIMLOOKINGFOR(SELECT * FROM anothertable) AS count FROM table;

因此，count 是子查询 SELECT * FROM anothertable 返回的行数的整数。

编辑

SELECT p.PostPID, p.PostUID, p.PostText, p.PostTime, u.UserUID, u.UserName, u.UserImage, u.UserRep,
    (
        SELECT COUNT(f.FlagTime)
            FROM Flags as f 
                JOIN Posts as p 
                ON p.PostPID = f.FlagPID
    ) as PostFlags
    FROM Posts AS p
        JOIN Users AS u
        ON p.PostUID = u.UserUID
    ORDER BY PostTime DESC
    LIMIT 0, 30

Answer 1

SELECT ( SELECT COUNT(id) FROM aTable ) as count FROM table

我假设您的示例是实际查询的截断版本，因此也许您应该发布您想要的内容以获得可能更优化的查询。

编辑

直接从我的大脑工作，这样的事情应该是更优化的。

SELECT p.PostPID, p.PostUID, p.PostText, p.PostTime, u.UserUID, u.UserName, u.UserImage, u.UserRep, COUNT(v.FlagTime) as postFlags
    FROM Flags as f 
    JOIN Posts as p ON p.PostPID = f.FlagPID
    JOIN Users AS u ON p.PostUID = u.UserUID
LIMIT 0, 30
GROUP BY p.PostPID
ORDER BY PostTime DESC

Answer 2

您可以指定

SELECT COUNT(*) FROM anothertable

哪个将返回一个数值，您可以在另一个查询中使用该数值，例如在另一个查询的选择列表中，或者作为另一个查询中的条件。

SELECT someVariable FROM table
WHERE (SELECT COUNT(*) FROM anotherTable) > 5

或者

SELECT someVariable, (SELECT COUNT(*) FROM anotherTable) as count FROM table