我正在尝试做的是在选择中做一些计算并将其分配为虚拟变量,就像 TimeDiff 一样,但是当我尝试在 where 条件下使用它时,它说未知列。
我尝试了两种方式都失败了:
1 :
SELECT `AL`.`UserID`, ROUND(IFNULL(TIMESTAMPDIFF(SECOND, MIN(AL.CreatedDate), MAX(AL.CreatedDate)) / NULLIF(COUNT(*) - 1, 0), 0)) as TimeDiff FROM (`ActiveLogins` AL) LEFT JOIN `Users` U ON `U`.`UserID`=`AL`.`UserID` WHERE `U`.`StatusID` IN (1, 2) AND TimeDiff>0 AND TimeDiff<86401 GROUP BY `AL`.`UserID`
阿拉伯数字:
SELECT `AL`.`UserID`, @TimeDiff := ROUND(IFNULL(TIMESTAMPDIFF(SECOND, MIN(AL.CreatedDate), MAX(AL.CreatedDate)) / NULLIF(COUNT(*) - 1, 0), 0)) as TimeDiff FROM (`ActiveLogins` AL) LEFT JOIN `Users` U ON `U`.`UserID`=`AL`.`UserID` WHERE `U`.`StatusID` IN (1, 2) AND @TimeDiff>0 AND @TimeDiff<86401 GROUP BY `AL`.`UserID`
在第二个查询中没有错误,也没有记录返回,但表中确实存在记录,表中只有 2 列 用户 ID 和 ActiveLogin 表中的创建日期
不能
在同一select
查询where
子句中使用Alias name
。在外部查询中添加筛选器
SELECT *
FROM (SELECT `al`.`userid`,
Round(Ifnull(Timestampdiff(second, Min(al.createddate),
Max(al.createddate)) /
Nullif(Count(*) - 1, 0), 0)) AS timediff
FROM `activelogins` al
LEFT JOIN `users` u
ON `u`.`userid` = `al`.`userid`
AND `u`.`statusid` IN ( 1, 2 )) a
WHERE timediff > 0
AND timediff < 86401
GROUP BY `userid`
这就是查询的逻辑处理方式
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
如您所见SELECT
WHERE
子句之后,您不能在 select
中生成的where
中使用别名
您需要
对聚合列使用 HAVING 关键字
SELECT
`AL`.`UserID`,
ROUND(IFNULL(TIMESTAMPDIFF(SECOND, MIN(AL.CreatedDate), MAX(AL.CreatedDate)) / NULLIF(COUNT(*) - 1, 0), 0)) as TimeDiff
FROM
(`ActiveLogins` AL) LEFT JOIN `Users` U ON `U`.`UserID`=`AL`.`UserID`
WHERE
`U`.`StatusID` IN (1, 2)
HAVING
TimeDiff>0 AND TimeDiff<86401
GROUP BY
`AL`.`UserID`