mysql状态的最大值

wgx48brx 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(290)

我有下面的表格记录了信件和行动的细节，为他们采取的。
字母表

+-----------+-------------+
| letter_id | description |
+-----------+-------------+
|         1 | A           |
|         2 | B           |
|         3 | C           |
|         4 | D           |
+-----------+-------------+

动作表

+-----------+--------+------------+---------------+
| action_id | ref_no |    date    | action_status |
+-----------+--------+------------+---------------+
|         1 |      1 | 2018-09-20 | On-Going      |
|         2 |      1 | 2018-09-22 | Finished      |
|         3 |      3 | 2018-09-16 | On-Going      |
|         4 |      4 | 2018-09-26 | On-Going      |
|         5 |      4 | 2018-09-27 | Finished      |
+-----------+--------+------------+---------------+

需要得到以下输出

+-----------+-------------+------------+---------------+
| letter_id | description |    date    | action_status |
+-----------+-------------+------------+---------------+
|         1 | A           | 2018-09-22 | Finished      |
|         2 | B           | -          | Pending       |
|         3 | C           | 2018-09-16 | On-Going      |
|         4 | D           | 2018-09-27 | Finished      |
+-----------+-------------+------------+---------------+

我使用了以下查询

select letter.letter_id,letter.description, action.date, action.action_status
            from letter
           left join action on letter.letter_id=action.ref_no
            where (date in     
                    (          
                        select max(date) from action   
                            where letter.letter_id=action.ref_no 
                    ))

但是上面的查询生成以下输出

+-----------+-------------+------------+---------------+
| letter_id | description |    date    | action_status |
+-----------+-------------+------------+---------------+
|         1 | A           | 2018-09-20 | On-Going      |
|         1 | A           | 2018-09-22 | Finished      |
|         2 | B           | -          | Pending       |
|         3 | C           | 2018-09-16 | On-Going      |
|         4 | D           | 2018-09-26 | On-Going      |
|         4 | D           | 2018-09-27 | Finished      |
+-----------+-------------+------------+---------------+

我不明白我做错了什么。有人能帮我吗？

mysql

来源：https://stackoverflow.com/questions/52541645/mysql-max-value-of-status

1条答案

按热度按时间

svmlkihl1#

DROP TABLE IF EXISTS action;

CREATE TABLE action
(action_id SERIAL PRIMARY KEY
,letter_id INT NOT NULL
,date DATE NOT NULL
,action_status VARCHAR(20) NOT NULL
);

INSERT INTO action VALUES
(1,101,'2018-09-20','On-Going'),
(2,101,'2018-09-22','Finished'),
(3,103,'2018-09-16','On-Going'),
(4,104,'2018-09-26','On-Going'),
(5,104,'2018-09-27','Finished');

SELECT x.* 
  FROM action x 
  JOIN 
     ( SELECT letter_id, MAX(date) max_date FROM action GROUP BY letter_id ) y 
    ON y.letter_id = x.letter_id 
   AND y.max_date = x.date;
+-----------+-----------+------------+---------------+
| action_id | letter_id | date       | action_status |
+-----------+-----------+------------+---------------+
|         2 |       101 | 2018-09-22 | Finished      |
|         3 |       103 | 2018-09-16 | On-Going      |
|         5 |       104 | 2018-09-27 | Finished      |
+-----------+-----------+------------+---------------+

想必你能猜出剩下的

赞(0）回复(0）举报 2021-06-18

我来回答

mysql状态的最大值

1条答案

相关问题

热门标签

最新问答