mysql根据条件取出分组后所要的那一条记录

图中出现了重复,统计记录条数时按一定条件过滤掉重复的记录,如重复的记录中只需要id最大的那条记录。

(1)先用这一句

SELECT MAX(id) AS maxid FROM taskparts GROUP BY part_id


(2)再用这一句把除了过滤掉后的详细记录查出来

SELECT * FROM taskparts WHERE id IN (SELECT MAX(id) AS maxid FROM taskparts GROUP BY part_id)

(3)然而,可是,但是,but,however, SQL语句中IN后面的值太多的话影响性能,甚至报错。改为如下方式

SELECT *  FROM taskparts  ,
  (SELECT MAX(id) AS maxid FROM taskparts AS linshitaskparts GROUP BY part_id)  linshitaskparts
 WHERE taskparts.id=linshitaskparts.maxid

(4)扩展一个三个表的

SELECT * FROM taskparts  ,
 (SELECT MAX(id) AS maxid FROM taskparts AS linshitaskparts GROUP BY part_id)  linshitaskparts,
(SELECT id FROM task WHERE user_code = 'admin') task
 WHERE  taskparts.id=linshitaskparts.maxid AND taskparts.task_id = task.id




没有更多推荐了,返回首页