sql - SQL计数表联接表

Question

我有以下sql命令:

SELECT "USERNAME"."TOPICS".VALUE,
       "USERNAME"."TOPICS".QID,
       "USERNAME"."QUESTION".QRATING
FROM "USERNAME"."TOPICS" JOIN "USERNAME"."QUESTION"
ON "USERNAME"."TOPICS".QID = "USERNAME"."QUESTION".QID 
AND "USERNAME"."TOPICS".VALUE = 'kia'
ORDER BY QRATING DESC

它确实运行良好，但我想计算返回的元素数量。所以我尝试使用:

SELECT COUNT("USERNAME"."TOPICS".QID)
FROM "USERNAME"."TOPICS" JOIN "USERNAME"."QUESTION"
ON "USERNAME"."TOPICS".QID = "USERNAME"."QUESTION".QID 
AND "USERNAME"."TOPICS".VALUE = 'kia'
ORDER BY QRATING DESC

但是我得到了错误:

Column reference 'USERNAME.TOPICS.VALUE' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.

问题是什么？

Answer 1

嗯ORDER BY应该收到错误，而不是SELECT。但是，使用表别名可以使查询更容易理解:

SELECT COUNT(t.QID)
FROM "USERNAME"."TOPICS" t JOIN
     "USERNAME"."QUESTION" q
     ON t.QID = q.QID AND t.VALUE = 'kia';

如果第一个查询有效，我认为没有理由不这样做(并且没有 ORDER BY的原始内容也应有效)。