tp5 mysql 查询表中每个类的前n条数据

本文主要是介绍tp5 mysql 查询表中每个类的前n条数据，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

 因为我的分类存的是id, 所以要join 关联查询

（把theme_id < 4 查的是前三个分类的数据，去掉则是全部分类）

SELECT 
    a.id,a.title,a.update_time,c.title as className
FROM
    ( 
     er_article AS a,
    (SELECT 
        GROUP_CONCAT(id) AS ids
    FROM
        er_article WHERE theme_id < 4 AND `status` = 1 And position > 0
    GROUP BY theme_id ) AS b 
    )
     LEFT JOIN er_theme c ON a.theme_id = c.id
WHERE
    FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 3 
ORDER BY a.theme_id ASC, a.update_time DESC

er_theme 是关联的分类表

GROUP_CONCAT(id) as ids 将所有id 存入一个数组ids

FIND_IN_SET(id,ids) 匹配对应数组的id

   BETWEEN 1 AND 每个分类查询3条数据

　　结果：

tp5

   $sql = "SELECT 
    a.id,a.title,subtitle,a.update_time,c.title as class
FROM
    ( er_article AS a,
    (SELECT 
        GROUP_CONCAT(id) AS ids
    FROM
        er_article WHERE theme_id < 4 AND `status` = 1 And position > 0
    GROUP BY theme_id ) AS b )
     LEFT JOIN er_theme c ON a.theme_id = c.id
WHERE
    FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 3 
ORDER BY a.theme_id ASC, a.update_time DESC";

        $list = Db::query($sql);
        array_walk($list,function ($v,$k) use (&$data){
            $data[$v['class']][] = $v;
        });

 结果：

这篇关于tp5 mysql 查询表中每个类的前n条数据的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！