mysql查询获取每个类别的最新记录

Question

这个问题已经有答案了:

Using LIMIT within GROUP BY to get N results per group? (14 个回答)

已关闭 7 年前。

我有 2 个表设备和位置。我需要类似于 deviceid 的最新 5 条记录(其中管理员 id)的结果。作为 json 格式表格如下:设备表:

    +-----------------------+
    | id |device_id|adminid |
    +-----------------------+
    |  1 |eefdcdfs | admin1 |
    |  2 |ffsdxwe2 | admin1 |
    |  3 |aaqw35ds | admin2 |
    |  4 |grfdr23f | admin2 |
    |  5 |etdhy79e | admin3 |
    +-----------------------+

位置表:

    +-----------------------------------------------------------------+
    | lid|lat       |long      |time               |deviceid |adminid |
    +----+----------+----------+-------------------+---------+--------+
    |  1 |17.4425358|78.3922061|2016-06-08 12:23:24|eefdcdfs | admin1 |
    |  2 |17.4425358|78.3922061|2016-06-08 12:45:24|eefdcdfs | admin1 |
    |  3 |17.4425358|78.3922061|2016-06-08 11:56:24|eefdcdfs | admin1 |
    |  4 |17.4425358|78.3922061|2016-06-08 12:53:24|eefdcdfs | admin1 |
    |  5 |17.4425500|78.3922342|2016-06-08 12:53:34|ffsdxwe2 | admin1 |
    |  6 |17.4425342|78.3922546|2016-06-08 11:55:34|ffsdxwe2 | admin1 |
    |  7 |17.4425562|78.3922657|2016-06-08 12:23:34|ffsdxwe2 | admin1 |
    |  8 |17.4425223|78.3922675|2016-06-08 12:12:34|ffsdxwe2 | admin1 |
    |  9 |17.4424856|78.3922307|2016-06-08 12:56:48|aaqw35ds | admin2 |
    | 10 |17.4425453|78.3922087|2016-06-08 13:08:30|grfdr23f | admin2 |
    | 11 |17.4425472|78.3922294|2016-06-08 13:15:54|etdhy79e | admin3 |
    +----+----------+----------+-------------------+---------+--------+

预期结果:

    +-----------------------------------------------------------------+
    | lid|lat       |long      |time               |deviceid |adminid |
    +----+----------+----------+-------------------+---------+--------+
    |  4 |17.4425358|78.3922061|2016-06-08 12:53:24|eefdcdfs | admin1 |
    |  2 |17.4425358|78.3922061|2016-06-08 12:45:24|eefdcdfs | admin1 |
    |  1 |17.4425358|78.3922061|2016-06-08 12:23:24|eefdcdfs | admin1 |
    |  3 |17.4425358|78.3922061|2016-06-08 11:56:24|eefdcdfs | admin1 |
    |  5 |17.4425500|78.3922342|2016-06-08 12:53:34|ffsdxwe2 | admin1 |
    |  7 |17.4425562|78.3922657|2016-06-08 12:23:34|ffsdxwe2 | admin1 |
    |  8 |17.4425223|78.3922675|2016-06-08 12:12:34|ffsdxwe2 | admin1 |
    |  6 |17.4425342|78.3922546|2016-06-08 11:55:34|ffsdxwe2 | admin1 |
    +----+----------+----------+-------------------+---------+--------+

我尝试过:

    select deviceid,CONCAT('[',CAST(lat AS CHAR ),',',CAST(long AS
    CHAR ),'],') json from locations WHERE 
    admin_id='admin1' 
    AND `time` > DATE_SUB(NOW(), 
    INTERVAL 3 HOUR) ORDER BY time DESC limit 10;

Answer 1

这是我的程序中使用的mysql查询。使用join连接两个表Distinct 将避免复制。

qry=Select distinct * from  tbl_1 join tbl_2 on tbl_1 .Som_Id =tbl_2 .S_Id  order by DATE1"; 

这只是一个例子，仅供引用。通过进行一些更改，您就可以实现您的结果。