MySQL - 每日前 10 名

Question

我需要从表格中按 PRESENCE 和 LECTURE DURATION 生成前 10 名。

我尝试了一些方法，但没有成功。这是现在的查询。

我可以得到一些指导来解决这个问题吗？

select 
        t1.EVE_DATE, 
        t1.CUSTID,
        SUM(t1.ATTENDENCE) as PRESENCE,
        TRUNCATE((AVG(t1.LECTURE_DUR))/(1000),2) as LECTURE_DUR
from 
        MY_TABLE t1
            left join
        (
            select
                CUSTID
            from
                MY_TABLE t2
            where
                t1.EVE_DATE = t2.EVE_DATE
            order by
                t2.CUSTID
            limit 10
        ) t3
            on
        t1.CUSTID = t3.CUSTID
where
        t1.SUBJECT= 'PHYSICS' 
        and t1.EVE_DATE >= '2015-01-01'
        and t1.EVE_DATE <= '2016-01-01'
        and t1.CUSTID <> ''
group by
        t1.EVE_DATE,
        t1.CUSTID
order by
        t1.EVE_DATE

Answer 1

假设您想要每个客户的 10 个最近的 eve_Dates。

未经测试我认为相关子查询将为每个客户生成一个结果集，但我对此并不乐观。

我不认为您限制为 10 条记录，因为您在日期没有加入。

select 
        t1.EVE_DATE, 
        t1.CUSTID,
        SUM(t1.ATTENDENCE) as PRESENCE,
        TRUNCATE((AVG(t1.LECTURE_DUR))/(1000),2) as LECTURE_DUR
from 
        MY_TABLE t1
            left join
        (
            select
                CUSTID, eve_date
            from
                MY_TABLE t2
            where
                t1.EVE_DATE = t2.EVE_DATE
            order by
                t2.CUSTID, t2.eve_Date desc
            limit 10
        ) t3
            on
        t1.CUSTID = t3.CUSTID
        t1.Eve_Date = T3.Eve_Date
where
        t1.SUBJECT= 'PHYSICS' 
        and t1.EVE_DATE >= '2015-01-01'
        and t1.EVE_DATE <= '2016-01-01'
        and t1.CUSTID <> ''
group by
        t1.EVE_DATE,
        t1.CUSTID
order by
        t1.EVE_DATE