mysql - 每个派生表都有自己的别名

Question

我有以下问题:

select userid, name, checktime, campaign,

CASE WHEN Hoursworked - 9 > 0 THEN Hoursworked - 9 ELSE 0 END Overtime
from
(

    select  
    a.userid,a.name,a.campaign,

    date(a.CHECKTIME),
    timediff(max(b.CHECKTIME), min(a.CHECKTIME)) as Hoursworked
    from CHECKINOUT a

    join CHECKINOUT b

    on date(a.CHECKTIME) = date(b.CHECKTIME) and a.userid = b.userid
    where
    a.CHECKTYPE = 'I'

    AND b.CHECKTYPE = 'O'

    group by date(a.CHECKTIME), userid
)

它给出了这个错误

1248 - 每个派生表都必须有自己的别名

你能帮我看看哪里出了问题吗？

Answer 1

您需要add a name for the subquery你用过的。

Subqueries are legal in a SELECT statement's FROM clause. The actual syntax is:

SELECT ... FROM (subquery) [AS] name ...

The [AS] name clause is mandatory, because every table in a FROM clause must have a name. Any columns in the subquery select list must have unique names.

所以给你的子查询一个别名:

select userid, name, checktime, campaign,

CASE WHEN Hoursworked - 9 > 0 THEN Hoursworked - 9 ELSE 0 END Overtime
from
(

    select  
    a.userid,a.name,a.campaign,

    date(a.CHECKTIME),
    timediff(max(b.CHECKTIME), min(a.CHECKTIME)) as Hoursworked
    from ...
) AS T  --here is the alias