分组查询最大最小记录的方法

来源：互联网发布：设置数据的有效范围编辑：程序博客网时间：2024/06/06 01:24

两种

-- 自连接的方式,如果最大条记录或者最小记录存在多条的话，则会查出所有的记录
SELECT 
  _max.sale_id,
  _max.visit_date,
  _max.sign_time,
  _max.id,
  _min.sign_time,
  _min.id 
FROM
  (SELECT 
    sign_max.id,
    sign_max.sale_id,
    sign_max.visit_date,
    sign_max.sign_time 
  FROM
    app_visit_agency_info sign_max 
  WHERE 
    (SELECT 
      COUNT(1) 
    FROM
      app_visit_agency_info a 
    WHERE a.`sale_id` = sign_max.`sale_id` 
      AND a.`visit_date` = sign_max.`visit_date` 
      AND a.`sign_time` > sign_max.`sign_time`) < 1) _max 
  LEFT JOIN 
    (SELECT 
      sign_min.id,
      sign_min.sale_id,
      sign_min.visit_date,
      sign_min.sign_time 
    FROM
      app_visit_agency_info sign_min 
    WHERE 
      (SELECT 
        COUNT(1) 
      FROM
        app_visit_agency_info a 
      WHERE a.`sale_id` = sign_min.`sale_id` 
        AND a.`visit_date` = sign_min.`visit_date` 
        AND a.`sign_time` < sign_min.`sign_time`) < 1) _min 
    ON _max.sale_id = _min.sale_id 
    AND _max.visit_date = _min.visit_date ORDER BY _max.visit_date DESC

-- -使用排序+group的方式
​
SELECT 
  _max.sale_id,
  _max.visit_date,
  _max.sign_time,
  _max.id,
  _min.sign_time,
  _min.id 
FROM
  (SELECT 
    sign_max.id,
    sign_max.sale_id,
    sign_max.visit_date,
    sign_max.sign_time 
  FROM
    (SELECT 
      * 
    FROM
      app_visit_agency_info a 
    ORDER BY sign_time DESC) sign_max 
  GROUP BY sale_id,
    visit_date 
  ORDER BY sign_time DESC) _max 
  LEFT JOIN 
    (SELECT 
      sign_min.id,
      sign_min.sale_id,
      sign_min.visit_date,
      sign_min.sign_time 
    FROM
      (SELECT 
        * 
      FROM
        app_visit_agency_info a 
      ORDER BY sign_time) sign_min 
    GROUP BY sale_id,
      visit_date 
    ORDER BY sign_time) _min 
    ON _max.sale_id = _min.sale_id 
    AND _max.visit_date = _min.visit_date 

阅读全文

0 0