mysql - "Every derived table must have its own alias"Mysql错误

Question

我的查询有什么问题:

SELECT minDate, deviceType, COUNT(*)
FROM (SELECT visitorId, deviceType, 
               MIN(sessionDate) as minDate
      FROM sessions
      GROUP BY visitorId)
WHERE minDate > DATE_SUB(NOW(), INTERVAL 14 DAY)
GROUP BY minDate, deviceId

我收到此消息:

Query Error: Error: ER_DERIVED_MUST_HAVE_ALIAS: Every derived table must have its own alias

Answer 1

错误信息已经很清楚了。您必须为子选择生成的派生表添加别名。因此，给它一个别名。

另一个问题是，在子查询中，非聚合列 deviceType 应包含在 GROUP BY 子句中。此更改可能会也可能不会产生您期望的结果:如果没有，那么您需要提供示例数据、预期结果以及对您要实现的目标的解释，以便帮助修复查询。

SELECT minDate, deviceType, COUNT(*)
FROM (
    SELECT visitorId, deviceType, MIN(sessionDate) as minDate
      FROM sessions
      GROUP BY visitorId, deviceType -- all non-aggregated columns in the GROUP BY clause
) t  -- alias here
WHERE minDate > DATE_SUB(NOW(), INTERVAL 14 DAY)
GROUP BY minDate, deviceId