带有 Group By 的Where子句
我有一个这样的查询：
SELECT TPCombined.Title,
GROUP_CONCAT(DISTINCT CONCAT(a.LastName, ', ', a.FirstName) SEPARATOR '<br />') as Author
FROM ((SELECT b.Title FROM BookList b) UNION (SELECT s.Title FROM Studios s))
AS TPCombined
LEFT OUTER JOIN (AuthorLinks al JOIN Authors a ON al.AuthorStem = a.AuthorStem)
ON TPCombined.BookStem = al.BookStem
WHERE Author LIKE '%Smith%' GROUP BY BookStem;
此查询不起作用：
“where 子句”中存在未知列“作者”
这适用于 HAVING 而不是 WHERE。但我确实需要使用Where。请您确认这是否真的不可能？或者也许我可以以某种方式修复查询以使其与Where? 一起使用？
I have a query like this:
SELECT TPCombined.Title,
GROUP_CONCAT(DISTINCT CONCAT(a.LastName, ', ', a.FirstName) SEPARATOR '<br />') as Author
FROM ((SELECT b.Title FROM BookList b) UNION (SELECT s.Title FROM Studios s))
AS TPCombined
LEFT OUTER JOIN (AuthorLinks al JOIN Authors a ON al.AuthorStem = a.AuthorStem)
ON TPCombined.BookStem = al.BookStem
WHERE Author LIKE '%Smith%' GROUP BY BookStem;
This query doesn't work:
Unknown column 'Author' in 'where clause'
This would work with HAVING instead of WHERE. But I really need to use Where. Could you please confirm if this is really impossible? Or maybe I can fix the query somehow to make it work with Where?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
WHERE 子句应用于行级别。您已将聚合函数别名为 Author，这意味着它的结果在行级别不可用 - 仅当查询完成并准备好发送给客户端时。您需要使用 HAVING 过滤器，该过滤器在发送到客户端之前应用。
WHERE clauses are applied at the row level. You've aliased an aggregate function as Author, which means its results won't be available at the row level - only when the query is complete and ready to go to the client. You'd need to use a HAVING filter instead, which is applied just before sending to client.
您需要使用前缀 TPCombined ... TPCombined.Author LIKE ... 来限定作者
you need to qualify Author with prefix TPCombined ... TPCombined.Author LIKE ...