使用max(date)mysql的groupby子句

jyztefdp  于 2021-06-24  发布在  Mysql
关注(0)|答案(1)|浏览(345)

下面是我的table的结构。我要标蓝色的那一排。即使使用多个分组依据,我的查询也不起作用。

SELECT * FROM drug_refills WHERE drug_inventory_id = 1 GROUP BY drug_entity_id, start_date

对于蓝色记号,日期是max,后跟药品\实体\ id(分组依据)

vuktfyat

vuktfyat1#

使用相关子查询建立最后日期

drop table if exists t;
    create table t(drug_refill_id int auto_increment primary key,drug_inventory_id int,drug_entity_id int,start_date date);

    insert into t (drug_inventory_id,drug_entity_id, start_date) values
    (1,1,'2018-01-01'),(1,3,'2018-01-02'),(2,4,'2018-01-01'),(1,5,'2018-01-01'),(1,5,'2018-02-01');

    select * 
    from t 
    where start_date = (select max(start_date) from t t1 where t1.drug_entity_id = t.drug_entity_id) and
            drug_inventory_id = 1;

+----------------+-------------------+----------------+------------+
| drug_refill_id | drug_inventory_id | drug_entity_id | start_date |
+----------------+-------------------+----------------+------------+
|              1 |                 1 |              1 | 2018-01-01 |
|              2 |                 1 |              3 | 2018-01-02 |
|              5 |                 1 |              5 | 2018-02-01 |
+----------------+-------------------+----------------+------------+
3 rows in set (0.00 sec)

顺便说一句,如果你希望答案更接近你的数据,那么在你的问题中包括文本数据,而不是图像。

相关问题