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