网站首页 > 文章专栏 > mysql获取某字段值相同,主键值最大(或最小)的数据集
mysql获取某字段值相同,主键值最大(或最小)的数据集
发布时间:2018-12-06 15:21:39 作者:飞星 浏览量:71

1、如下图,根据排序字段相同值获取ID最小的那一条数据查询结果集,获取红框中的数据

108.png

查询sql语句

SELECT * FROM `user` `m` INNER JOIN ( SELECT max(id) id FROM `user` GROUP BY sort ) u ON `u`.`id`=`m`.`id`

获得结果

array(7) {
  [0] => array(4) {
    ["id"] => int(1)
    ["name"] => string(6) "小三"
    ["reg_datetime"] => string(19) "2018-12-06 11:25:36"
    ["sort"] => int(1)
  }
  [1] => array(4) {
    ["id"] => int(2)
    ["name"] => string(9) "戴老大"
    ["reg_datetime"] => string(19) "2018-12-06 11:26:38"
    ["sort"] => int(2)
  }
  [2] => array(4) {
    ["id"] => int(3)
    ["name"] => string(12) "香肠叔叔"
    ["reg_datetime"] => string(19) "2018-12-06 11:27:29"
    ["sort"] => int(3)
  }
  [3] => array(4) {
    ["id"] => int(4)
    ["name"] => string(9) "胸大大"
    ["reg_datetime"] => string(19) "2018-12-06 11:28:26"
    ["sort"] => int(4)
  }
  [4] => array(4) {
    ["id"] => int(8)
    ["name"] => string(7) "小舞2"
    ["reg_datetime"] => string(19) "2018-12-06 14:43:36"
    ["sort"] => int(5)
  }
  [5] => array(4) {
    ["id"] => int(9)
    ["name"] => string(7) "胖子2"
    ["reg_datetime"] => string(19) "2018-12-06 14:43:36"
    ["sort"] => int(6)
  }
  [6] => array(4) {
    ["id"] => int(10)
    ["name"] => string(7) "荣荣2"
    ["reg_datetime"] => string(19) "2018-12-06 14:43:36"
    ["sort"] => int(7)
  }
}

2、如下图,根据排序字段相同值获取ID最大的那一条数据查询结果集,获取红框中的数据

109.png

查询sql语句

SELECT * FROM `user` `m` INNER JOIN ( SELECT min(id) id FROM `user` GROUP BY sort ) u ON `u`.`id`=`m`.`id`

获取查询结果

array(7) {
  [0] => array(4) {
    ["id"] => int(1)
    ["name"] => string(6) "小三"
    ["reg_datetime"] => string(19) "2018-12-06 11:25:36"
    ["sort"] => int(1)
  }
  [1] => array(4) {
    ["id"] => int(2)
    ["name"] => string(9) "戴老大"
    ["reg_datetime"] => string(19) "2018-12-06 11:26:38"
    ["sort"] => int(2)
  }
  [2] => array(4) {
    ["id"] => int(3)
    ["name"] => string(12) "香肠叔叔"
    ["reg_datetime"] => string(19) "2018-12-06 11:27:29"
    ["sort"] => int(3)
  }
  [3] => array(4) {
    ["id"] => int(4)
    ["name"] => string(9) "胸大大"
    ["reg_datetime"] => string(19) "2018-12-06 11:28:26"
    ["sort"] => int(4)
  }
  [4] => array(4) {
    ["id"] => int(5)
    ["name"] => string(6) "小舞"
    ["reg_datetime"] => string(19) "2018-12-06 14:43:31"
    ["sort"] => int(5)
  }
  [5] => array(4) {
    ["id"] => int(6)
    ["name"] => string(6) "胖子"
    ["reg_datetime"] => string(19) "2018-12-06 14:43:31"
    ["sort"] => int(6)
  }
  [6] => array(4) {
    ["id"] => int(7)
    ["name"] => string(6) "荣荣"
    ["reg_datetime"] => string(19) "2018-12-06 14:43:31"
    ["sort"] => int(7)
  }
}

有人说为什么不能在第一次直接把字段加好一次性查出来呢,比如

SELECT max(id) id,`name`,`reg_datetime`,`sort` FROM `user` GROUP BY sort

这样子如果把max改成min查出来跟上面查询最小哪一行还真行,但是对于最大一条的话,会导致除了id其他都是id最小的那个值,达不到想要的效果

附tp5查询方式

$sql = db('user')->field('max(id) id')->group('sort')->buildSql();
$list = db('user')->alias('m')->join($sql.' u','u.id=m.id')->select();


来说两句吧
最新评论