代码之家 › 专栏 › 技术社区 › Shantaram Tupe

MySQL检索JOIN with group by中的最后一条记录

group-by join mysql

1

Shantaram Tupe · 技术社区 · 9 年前

我有以下数据表:

follow_up

mysql> select * from follow_up;
+--------------+----------------+--------------------------------------------------+-------------------+---------+---------------+-----------+---------------+----------+
| follow_up_id | feedback_close | feedback_open                                    | is_email_required | is_Open | reminder_date | client_id | conclusion_id | stage_id |
+--------------+----------------+--------------------------------------------------+-------------------+---------+---------------+-----------+---------------+----------+
|            1 | NULL           | dsffsdfsdfsd                                     |                 1 |       1 | 2017-09-20    |       101 |            96 |       72 |
|            2 | NULL           | FSGDFHFGHFG                                      |                 1 |       1 | 2017-09-28    |       101 |           251 |       72 |
|            3 | NULL           | Tender stage fb                                  |                 0 |       1 | NULL          |       101 |            98 |      163 |
|            4 | NULL           | Call back tender stage update date from 28 to 30 |                 1 |       1 | 2017-09-28    |       101 |            96 |      163 |
|            5 | NULL           | Metting follow up for next meeting               |                 1 |       1 | 2017-10-02    |       101 |            96 |       73 |
+--------------+----------------+--------------------------------------------------+-------------------+---------+---------------+-----------+---------------+----------+

2、表格 logs 作为:

mysql> SELECT *  from logs where transaction = 'FLWUP';
+---------+---------+---------------------+---------+-------------+
| user_id | menu_id | logs_time           | tran_id | transaction |
+---------+---------+---------------------+---------+-------------+
|      84 |      69 | 2017-09-19 19:31:04 |       1 | FLWUP       |
|      84 |      69 | 2017-09-19 19:31:25 |       2 | FLWUP       |
|      84 |      69 | 2017-09-20 19:10:41 |       2 | FLWUP       |
|      84 |      69 | 2017-09-21 12:35:01 |       3 | FLWUP       |
|      84 |      69 | 2017-09-21 12:35:26 |       4 | FLWUP       |
|      84 |      69 | 2017-09-21 12:36:16 |       4 | FLWUP       |
|      84 |      69 | 2017-09-21 12:38:30 |       5 | FLWUP       |
+---------+---------+---------------------+---------+-------------+
7 rows in set (0.00 sec)

allcode 作为:

mysql> select * from allcode where code_type like 'MARK%';
+------------------+---------+------+----------------------+
| code_type        | code_id | srno | code_name            |
+------------------+---------+------+----------------------+
| MARKETING_STAGES |      72 |    1 | Enquiry              |
| MARKETING_STAGES |      73 |    3 | Meeting              |
| MARKETING_STAGES |      74 |    4 | Presentation         |
| MARKETING_STAGES |     163 |    2 | Tender               |
+------------------+---------+------+----------------------+
11 rows in set (0.00 sec)

我调用了一个查询,结果如下:

mysql> select f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name as stage, ac.code_name as conclusion, max(l.logs_time)  
from follow_up f 
join logs l on l.tran_id = f.follow_up_id 
join allcode ast on ast.code_id = f.stage_id 
join allcode ac on ac.code_id = f.conclusion_id 
where l.transaction='FLWUP' and f.client_id = 101 
group by ast.code_name order by ast.srno;
+--------------+------------------------------------+----------------+---------------+---------+------------+---------------------+
| follow_up_id | feedback_open                      | feedback_close | reminder_date | stage   | conclusion | max(l.logs_time)    |
+--------------+------------------------------------+----------------+---------------+---------+------------+---------------------+
|            1 | dsffsdfsdfsd                       | NULL           | 2017-09-20    | Enquiry | Call Back  | 2017-09-20 19:10:41 |
|            3 | Tender stage fb                    | NULL           | NULL          | Tender  | Next       | 2017-09-21 12:36:16 |
|            5 | Metting follow up for next meeting | NULL           | 2017-10-02    | Meeting | Call Back  | 2017-09-21 12:38:30 |
+--------------+------------------------------------+----------------+---------------+---------+------------+---------------------+
3 rows in set (0.00 sec)

+--------------+-----------------------------------------------------+----------------+---------------+---------+------------+---------------------+
| follow_up_id | feedback_open                                       | feedback_close | reminder_date | stage   | conclusion | max(l.logs_time)    |
+--------------+-----------------------------------------------------+----------------+---------------+---------+------------+---------------------+
|            2 | FSGDFHFGHFG                                         | NULL           | 2017-09-20    | Enquiry | Call Back  | 2017-09-20 19:10:41 |
|            4 | Call back tender stage update date from 28 to 30    | NULL           | NULL          | Tender  | Next       | 2017-09-21 12:36:16 |
|            5 | Metting follow up for next meeting                  | NULL           | 2017-10-02    | Meeting | Call Back  | 2017-09-21 12:38:30 |
+--------------+-----------------------------------------------------+----------------+---------------+---------+------------+---------------------+
3 rows in set (0.00 sec)

我无法加入并分组以获得所需的结果。

柱 conclusion_id 和 stage_id 跟进 指的是 code_id 表的 所有代码

问题:
我想要的结果是

阶段id ,
srno 属于 所有代码
follow_up_id 属于 跟进

2 回复 | 直到 9 年前

1

xQbert 9 年前

DEMO

在本专栏中,我的结果与你的不匹配;但我相信你的预期结果是错误的。

当存在多个stage\u ID时,似乎需要每个stage\u ID的最大follow\u ID

我也不喜欢mySQL的扩展group by,我更喜欢在group by中包含select中未聚合的所有列。使用扩展的分组方式往往会隐藏潜在的问题。在这种情况下,仅通过ast进行分组。code_name允许引擎从其他列中选择一个非不同的值。您最终没有得到所需的结果,而且它隐藏了一个事实,即如果不是由于使用/误用扩展组,您将在查询中获得多条记录。

SELECT f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name as stage, ac.code_name as conclusion, max(l.logs_time)  
from follow_up f 
join logs l on l.tran_id = f.follow_up_id 
join allcode ast on ast.code_id = f.stage_id 
join allcode ac on ac.code_id = f.conclusion_id 
JOIN SELECT max(follow_up_ID) MFID, stage_ID 
      FROM follow_up 
      GROUP BY stage_ID) Z 
  on f.follow_up_ID = Z.MFID 
 and F.Stage_ID = Z.Stage_ID
WHERE l.transaction='FLWUP' and f.client_id = 101 
GROUP BY f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name , ac.code_name
ORDER BY ast.srno;

2

0

Rupal Javiya 9 年前

select f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name as stage, ac.code_name as conclusion, max(l.logs_time)  
from follow_up f 
join logs l on l.tran_id = f.follow_up_id 
join allcode ast on ast.code_id = f.stage_id 
join allcode ac on ac.code_id = f.conclusion_id 
where l.transaction='FLWUP' and f.client_id = 101 
group by follow_up.stage_id order by ast.srno, follow_up.follow_up_id DESC;

这应该是可行的,如果不是这样,那么你应该搜索如何在多个列上设置顺序。

参考条款- SQL multiple column ordering