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

两种

1

-- 自连接的方式,如果最大条记录或者最小记录存在多条的话，则会查出所有的记录

2

SELECT 

3

  _max.sale_id,

4

  _max.visit_date,

5

  _max.sign_time,

6

  _max.id,

7

  _min.sign_time,

8

  _min.id 

9

FROM

10

  (SELECT 

11

    sign_max.id,

12

    sign_max.sale_id,

13

    sign_max.visit_date,

14

    sign_max.sign_time 

15

  FROM

16

    app_visit_agency_info sign_max 

17

  WHERE 

18

    (SELECT 

19

      COUNT(1) 

20

    FROM

21

      app_visit_agency_info a 

22

    WHERE a.`sale_id` = sign_max.`sale_id` 

23

      AND a.`visit_date` = sign_max.`visit_date` 

24

      AND a.`sign_time` > sign_max.`sign_time`) < 1) _max 

25

  LEFT JOIN 

26

    (SELECT 

27

      sign_min.id,

28

      sign_min.sale_id,

29

      sign_min.visit_date,

30

      sign_min.sign_time 

31

    FROM

32

      app_visit_agency_info sign_min 

33

    WHERE 

34

      (SELECT 

35

        COUNT(1) 

36

      FROM

37

        app_visit_agency_info a 

38

      WHERE a.`sale_id` = sign_min.`sale_id` 

39

        AND a.`visit_date` = sign_min.`visit_date` 

40

        AND a.`sign_time` < sign_min.`sign_time`) < 1) _min 

41

    ON _max.sale_id = _min.sale_id 

42

    AND _max.visit_date = _min.visit_date ORDER BY _max.visit_date DESC

1

-- -使用排序+group的方式

2

​

3

SELECT 

4

  _max.sale_id,

5

  _max.visit_date,

6

  _max.sign_time,

7

  _max.id,

8

  _min.sign_time,

9

  _min.id 

10

FROM

11

  (SELECT 

12

    sign_max.id,

13

    sign_max.sale_id,

14

    sign_max.visit_date,

15

    sign_max.sign_time 

16

  FROM

17

    (SELECT 

18

      * 

19

    FROM

20

      app_visit_agency_info a 

21

    ORDER BY sign_time DESC) sign_max 

22

  GROUP BY sale_id,

23

    visit_date 

24

  ORDER BY sign_time DESC) _max 

25

  LEFT JOIN 

26

    (SELECT 

27

      sign_min.id,

28

      sign_min.sale_id,

29

      sign_min.visit_date,

30

      sign_min.sign_time 

31

    FROM

32

      (SELECT 

33

        * 

34

      FROM

35

        app_visit_agency_info a 

36

      ORDER BY sign_time) sign_min 

37

    GROUP BY sale_id,

38

      visit_date 

39

    ORDER BY sign_time) _min 

40

    ON _max.sale_id = _min.sale_id 

41

    AND _max.visit_date = _min.visit_date