我试图使用子选择列出表的内容,并只选择与我的WHERE子句匹配的内容:
SELECT DISTINCT `torrentItem` as ti,
(SELECT COUNT(*) FROM votes WHERE `torrentItem` = ti AND `voteType` = 0) AS `badVotes`,
(SELECT COUNT(*) FROM votes WHERE `torrentItem` = ti AND `voteType` = 1) AS `goodVotes`,
(SELECT TIMESTAMPDIFF(WEEK,(SELECT MAX(`date`) FROM votes WHERE `torrentItem` = ti),CURRENT_TIMESTAMP)) AS `weeksSinceLastVote`
FROM `votes`
WHERE (`badVotes` > 1 AND `goodVotes` = 0 AND `weeksSinceLastVote` > 1);
它不起作用:MySQL告诉我不能只在WHERE
子句中使用badVotes
或goodVotes
:Error Code: 1054. Unknown column 'badVotes' in 'where clause'
你能解释一下为什么吗?如何做到这一点?
对于表中的每一行,执行每个子查询。性能噩梦:)
您可以将查询重写为
SELECT `torrentItem` as ti,
SUM(`voteType` = 0) AS `badVotes`,
SUM(`voteType` = 1) AS `goodVotes`,
TIMESTAMPDIFF(WEEK, MAX(`date`), CURRENT_TIMESTAMP) AS `weeksSinceLastVote`
FROM `votes`
GROUP BY ti
HAVING (`badVotes` > 1 AND `goodVotes` = 0 AND `weeksSinceLastVote` > 1);
这将比您的子查询运行得更好。
不能在where
子句中使用别名的原因是解释查询的顺序。
- 来自
- 其中
- 分组依据
- 具有
- 订购人
- 选择
不能在where子句中使用列别名。
但是,您也可以使用having
子句。
点击此处查看更多详细信息http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html