gpt4 book ai didi

mysql - 在查询中使用别名时出现 "#1054 - Unknown column"错误?

转载 作者:可可西里 更新时间:2023-11-01 06:43:12 25 4
gpt4 key购买 nike

在使用以下查询时,我得到错误

#1054 - Unknown column 'plus' in 'field list'

当使用 plus-minus 时。否则,查询运行正常。我猜有些东西与别名有关,但我不知道如何使用。请指导!

谢谢。

查询:

SELECT users.name,
count(*) as total,
SUM(sms.views)+ SUM(sms.downloads)+ (SELECT count(*) FROM `smsfb` WHERE (`feedback`=1 OR `feedback`=100) AND userid=users.uniqueID) AS plus,
SUM(sms.delreq)+(SELECT count(*) FROM `smsfb` WHERE (`feedback`=5 OR `feedback`=6) AND userid=users.uniqueID) AS minus,
plus-minus
FROM sms,users
WHERE sms.deviceID=users.uniqueID AND sms.catid!=23 AND sms.catid!=44 AND sms.catid!=45
AND date>="2011-10-03" AND date<"2011-10-09"
GROUP BY users.uniqueID HAVING total>10 ORDER BY total DESC LIMIT 0, 10

最佳答案

您不能在查询的选择部分中使用列的别名。

你可以这样做:

SELECT name
, total
, plus
, minus
, plus - minus
FROM (
SELECT users.name,
count(*) as total,
SUM(sms.views)+ SUM(sms.downloads)+ (SELECT count(*) FROM `smsfb` WHERE (`feedback`=1 OR `feedback`=100) AND userid=users.uniqueID) AS plus,
SUM(sms.delreq)+(SELECT count(*) FROM `smsfb` WHERE (`feedback`=5 OR `feedback`=6) AND userid=users.uniqueID) AS minus
FROM sms,users
WHERE sms.deviceID=users.uniqueID
AND sms.catid!=23 AND sms.catid!=44
AND sms.catid!=45
AND date>="2011-10-03" AND date<"2011-10-09"
GROUP BY users.uniqueID HAVING total>10 ORDER BY total DESC
LIMIT 0, 10
) plusAndMinus

来自 Problems with Column Aliases :

You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column

关于mysql - 在查询中使用别名时出现 "#1054 - Unknown column"错误?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7784815/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com