通过组mysql获取最大ID [英] get max ids by group mysql
本文介绍了通过组mysql获取最大ID的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有这张叫 sw_sowing
-----------------------------------------------------------------
id | id_unit | date | id_variety | type | status |
-----------------------------------------------------------------
1 | 1 | 2017-08-10 | 1 | SW | 200 |
-----------------------------------------------------------------
2 | 1 | 2017-10-10 | 1 | ER | 100 |
-----------------------------------------------------------------
3 | 1 | 2017-11-30 | 2 | SW | 100 |
-----------------------------------------------------------------
4 | 2 | 2017-12-10 | 3 | SW | 200 |
-----------------------------------------------------------------
5 | 2 | 2017-12-10 | 3 | ER | 100 |
-----------------------------------------------------------------
6 | 3 | 2017-08-05 | 4 | SW | 200 |
-----------------------------------------------------------------
7 | 3 | 2017-12-13 | 4 | ER | 100 |
-----------------------------------------------------------------
8 | 3 | 2018-01-04 | 1 | SW | 100 |
-----------------------------------------------------------------
9 | 3 | 2018-01-04 | 2 | SW | 100 |
-----------------------------------------------------------------
我想知道id_unit的当前状态和id_variety(取决于日期).我有这个查询:
I want to know the current status and id_variety of the id_unit depending on a date. I have this query:
SELECT sw_sowing.id_unit, sw_sowing.id_variety, sw_sowing.type, sw_sowing.status
FROM (
SELECT MAX(id) AS id
FROM sw_sowing
WHERE date <= '2018-09-06'
GROUP BY id_unit
) AS sw
INNER JOIN sw_sowing ON sw_sowing.id = sw.id
它给我以下结果:
-----------------------------------------------------
id_unit | id_variety | type | status |
-----------------------------------------------------
1 | 2 | SW | 100 |
-----------------------------------------------------
2 | 3 | ER | 100 |
-----------------------------------------------------
3 | 2 | SW | 100 |
-----------------------------------------------------
问题在于， id_unit = 3 具有两个不同的 id_variety ，其 state = 100 ，但是前一个查询给出了最后一个我想要两个 id_variety .
The problem is that the id_unit = 3 has two different id_variety with state = 100, but the previous query give the last one and I want the two id_variety.
类似这样的东西:
-----------------------------------------------------
id_unit | id_variety | type | status |
-----------------------------------------------------
1 | 2 | SW | 100 |
-----------------------------------------------------
2 | 3 | ER | 100 |
-----------------------------------------------------
3 | 1 | SW | 100 |
-----------------------------------------------------
3 | 2 | SW | 100 |
-----------------------------------------------------
我希望你能帮助我！
推荐答案
只需在GROUP BY上包含id_variety.
Just include id_variety on the GROUP BY.
SELECT sw_sowing.id_unit, sw_sowing.id_variety, sw_sowing.type, sw_sowing.status
FROM (
SELECT MAX(id) AS id
FROM sw_sowing
WHERE date <= '2018-09-06'
GROUP BY id_unit,id_variety
) AS sw
INNER JOIN sw_sowing ON sw_sowing.id = sw.id
这篇关于通过组mysql获取最大ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
