WHERE 子句中可能不会出现聚合(SQL Server 错误)
An aggregate may not appear in the WHERE clause( SQL Server Error )
当我尝试这个查询时，
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT
(A.StudentId), MAX(A.StudentFirstName), MAX(A.StudentLastName), MAX(A.StudentAddress), 'Batch ' + MAX(C.BatchName), CAST(MAX(CAST(A.StudentStatus AS INT)) AS BIT), MAX(B.StudentBatchId) FROM tblStudentDetails A INNER JOIN tblStudentBatchDetails B ON A.StudentId = B.studentid INNER JOIN tblBatch C ON C.BatchId = B.batchid WHERE MAX(A.StudentFirstName) LIKE 'A%' GROUP BY A.StudentId |
我收到了这个错误：
An aggregate may not appear in the WHERE clause unless it is in a
subquery contained in a HAVING clause or a select list, and the column
being aggregated is an outer reference.
有人可以帮忙解决这个问题吗？
相关讨论
- 您对错误有什么不明白的地方？使用
having 子句。也就是说，我不知道为什么在这种情况下你需要一个max() 。 - 你到底想达到什么目的？您能否分享您的表结构、一些示例数据以及您试图为此示例获得的结果？
-
您究竟希望通过
max(A.StudentFirstName) like 'A%' 获得什么？？？这甚至没有意义。在我看来，你在试图解决一个你只是抓住你能想到的任何东西的问题上已经偏离了Rails。使用一些示例数据和您尝试从该数据中获取的输出来解释您要解决的实际问题可能会更好，并询问如何编写查询来做到这一点。
正确的语法应该是...
|
1
2 3 4 5 6 7 8 9 |
SELECT (A.StudentId),MAX(A.StudentFirstName),
MAX(A.StudentLastName),MAX(A.StudentAddress), 'Batch ' + MAX(C.BatchName),CAST(MAX(CAST(A.StudentStatus AS INT)) AS BIT), MAX(B.StudentBatchId) FROM tblStudentDetails A INNER JOIN tblStudentBatchDetails B ON A.StudentId=B.studentid INNER JOIN tblBatch C ON C.BatchId=B.batchid GROUP BY A.StudentId HAVING MAX(A.StudentFirstName) LIKE 'A%' |
相关讨论
- 非常有用，你能建议我应用有子句条件的方式吗？