MySQL -每天前10名

Question

我需要从一个表中生成PRESENCE & LECTURE DURATION的前十名。

我试过一些东西，但没有运气。下面是现在的查询。

我能得到一些指导才能把这事做好吗？

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