两种
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