mysql - 在 select 子句中分配和使用 mysql 用户变量

Question

我有一个以 EID 作为主键的表(它是一个 varchar 并包含一些详细信息)和上传时间作为日期时间列。我希望此表的结果为:

substr(eid,5,5) as machine | count(eid) in group(grouped by machine) as packets | AvgDelay (as shown in SQL code) | packets * AvgDelay as Penalty

但是最后一列总是空的。我使用的 SQL 是:

SELECT substr(eid, 5, 5) AS machine,
@packetcount: = count(*) AS packets,
@avg_delay: = round(avg(DATEDIFF(IF (STR_TO_DATE(uploadtime, '%Y-%m-%d') IS NULL, CURDATE(), STR_TO_DATE(uploadtime, '%Y-%m-%d')), STR_TO_DATE(SUBSTR(eid, 15, 8), '%Y%m%d') ) ) ) AS AvgDelay, 
@packetcount * @avg_delay AS Penalty 
FROM eidreport
WHERE uploadtime IS NOT NULL
OR DATEDIFF(IF (STR_TO_DATE(uploadtime, '%Y-%m-%d') IS NULL, CURDATE(), STR_TO_DATE(uploadtime, '%Y-%m-%d')), STR_TO_DATE(SUBSTR(eid, 15, 8), '%Y%m%d') ) >= 100 
GROUP BY machine

Here is the link for SQL Fiddle

我哪里错了？请帮忙。提前致谢。

Answer 1

我认为这里的问题在 GROUP BY 语句中。我认为 @packetcount: = count(*) 是在分组之后计算的，但是 @packetcount * @avg_delay 之前是这样的，所以此时它们是未定义的。

尝试像这样使用子查询:

SELECT *,
packets * AvgDelay AS Penalty

FROM
(
select substr(eid,5,5) as machine,count(*) as packets,
round(avg(DATEDIFF(IF(STR_TO_DATE(uploadtime, '%Y-%m-%d') IS NULL, CURDATE(), STR_TO_DATE(uploadtime, '%Y-%m-%d')),
                      STR_TO_DATE(SUBSTR(eid, 15, 8),'%Y%m%d')))) AS AvgDelay
from eidreport
where uploadtime is not null or
DATEDIFF(
  IF(STR_TO_DATE(uploadtime, '%Y-%m-%d') IS NULL, CURDATE(), STR_TO_DATE(uploadtime, '%Y-%m-%d')),
  STR_TO_DATE(SUBSTR(eid, 15, 8),'%Y%m%d')) >= 100
group by machine
) as T

SQLFiddle example