如何在MySql 5.5.34中取得每週每天的前兩個條目-PHP中文網路問答

我有一個MySQL 表，其中包含TENANT_NAME、MAX_CALLSTENANT_NAME、MAX_CALLS 和TIME_STAMP 列，並且< 和TIME_STAMP 列，且< ，根據要求，我們每小時插入數據，並且相同的名稱可以重複。

現在我想取得需要新增群組名稱和求和呼叫並取得一週內每天的前 2 個條目。

例如：22:49 插入資料

TENANT_NAME,MAX_CALLS,TIME_STAMP RS1, 20, 2022-12-07 22:49:17 RS2, 10, 2022-12-07 22:49:17 RS3, 2, 2022-12-07 22:49:17

下一小時 23:49

RS1, 15, 2022-12-07 23:49:17 RS2, 0, 2022-12-07 23:49:17 RS3, 20, 2022-12-07 23:49:17

這樣，我就有了1年的數據

現在我想要每天總結一週的 2 筆記錄的名稱群組

像這樣

RS1, 35, MON RS3, 22, MON... so on RS4, 40, SUN RS2, 35, SUN

我嘗試了這個查詢，我能夠對姓名和總呼叫進行分組，並顯示 DAYNAME，但我想要一周內每天的前 2 條記錄。

select a.TENANT_NAME,SUM(a.MAX_CALLS),DAYNAME(a.TIME_STAMP) from TENANT_LIC_DISTRIBUTION AS a group by a.TENANT_NN,day(a.TIME_AMP ) order by a.MAX_CALLS,a.TIME_STAMP; RS1, 35, MON RS3, 22, MON RS2, 10, MON RS3, 30, TUE RS2, 20, TUE RS1, 10, TUE.... so on RS1, 20, SUN RS2, 10, SUN RS3, 1, SUN

我想像這樣取得

RS1, 35, MON RS3, 22, MON RS3, 30, TUE RS2, 20, TUE.... so on RS1, 20, SUN RS2, 10, SUN

請幫我

謝謝

WITH rank_tenant AS ( SELECT TENANT_NAME, DAY, CALLS, row_number() OVER ( PARTITION BY TENANT_NAME ORDER BY CALLS DESC ) AS row_num FROM (select TENANT_NAME, DAYNAME(TIME_STAMP) as DAY, SUM(MAX_CALLS) as CALLS from TENANT_LIC_DISTRIBUTION group by TENANT_NAME, DAY) as t1 ) SELECT TENANT_NAME, DAY, CALLS FROM rank_tenant WHERE row_num <= 2;