MySQL，分组依据和最新结果

Question

SELECT
o.semaine AS week,
c.id AS c_id,
sr.id AS sr_id,
mr.id AS mr_id,
o.`%_RACH_distance_sup_5075m` AS rach,
o.DCR_Total_AMR AS dcr_amr
FROM
rdi.ref_cells AS c
INNER JOIN nortel.OM_Cell_week AS o ON o.cellule = c.libelle
INNER JOIN rdi.ref_sites AS sr ON sr.id = c.siteRadio_id
INNER JOIN rdi.ref_mres AS mr ON o.rnc = mr.libelle AND sr.milieuReseau_id = mr.id
INNER JOIN rdi.ref_pl AS p ON c.plaque_id = p.id
WHERE
o.date > ADDDATE(NOW(), INTERVAL - 3 WEEK) AND
o.`%_RACH_distance_sup_5075m` > 50 AND
o.DCR_Total_AMR > 1.5

结果：

+------+--------+-------+-------+---------+---------+
| week | c_id   | sr_id | mr_id | rach    | dcr_amr |
+------+--------+-------+-------+---------+---------+
|   16 | 117114 | 37312 |    79 | 64,1198 |  1,5018 |
|   17 | 117114 | 37312 |    79 | 67,6647 | 1,79469 |
|   18 | 117114 | 37312 |    79 | 66,6645 | 1,51302 | <- this
|   16 | 117116 | 37312 |    50 | 69,1325 |  2,3014 |
|   17 | 117116 | 37312 |    50 | 67,6647 |   1,568 | <- this
+------+--------+-------+-------+---------+---------+

我想选择本周最高的最新结果（第 3 行和第 5 行）。 我尝试添加 GROUP BY c.id 但默认情况下它返回每个组的第一个。 我还尝试了 ORDER BY o.semaine

Answer 1

SELECT  o.semaine, c.id, sr.id, mr.id
FROM    rdi.ref_cells c
JOIN    nortel.OM_Cell_week o
ON      o.id = 
        (
        SELECT  o.id
        FROM    nortel.OM_Cell_week oi
        WHERE   oi.cellule = c.libelle
                AND oi.date > ADDDATE(NOW(), INTERVAL - 3 WEEK)
                AND oi.`%_RACH_distance_sup_5075m` > 50
                AND oi.DCR_Total_AMR > 1.5
        ORDER BY
                oi.week DESC, oi.id DESC
        LIMIT 1
        )
JOIN    rdi.ref_sites AS sr
ON      sr.id = c.siteRadio_id
JOIN    rdi.ref_mres AS mr
ON      mr.libelle = o.rnc
        AND mr.id = sr.milieuReseau_id
JOIN    rdi.ref_pl AS p
ON      p.id = c.plaque_id

Answer 2

您指出的两条线不是本周最高的两条线，所以我不确定您的意思。

无论如何，您始终可以使用子查询来执行此类操作。以下是选择周数最高的行的示例：

select * from table where week = (select max(week) from table)