mysql查询,select具有distinct with limit条件

amrnrhlw  于 2021-06-19  发布在  Mysql
关注(0)|答案(4)|浏览(291)

目前,我有一个如下的表,我想提取id为的记录,这些记录具有估计的\u日期,分配给update\u index value的最小值,如果该值为null,我想转到分配给下一个update\u index的值,
例如,对于
id=73,我想得到值2017-06-13 00:00:00
id=75,我想得到值2017-01-01 00:00:00
id=76,我想得到值2018-06-01 00:00:00
输入表

+----+--------------+---------------------+
| id | update_index |    estimated_date   |
+----+--------------+---------------------+
| 73 |       1      | 2017-06-13 00:00:00 |
+----+--------------+---------------------+
| 73 |       2      | 2017-01-13 00:00:00 |
+----+--------------+---------------------+
| 73 |       3      | 2017-05-13 00:00:00 |
+----+--------------+---------------------+
| 73 |       4      |         NULL        |
+----+--------------+---------------------+
| 75 |       1      | 2017-01-01 00:00:00 |
+----+--------------+---------------------+
| 75 |       2      |         NULL        |
+----+--------------+---------------------+
| 75 |       3      | 2019-01-01 00:00:00 |
+----+--------------+---------------------+
| 76 |       1      |         NULL        |
+----+--------------+---------------------+
| 76 |       2      | 2018-06-01 00:00:00 |
+----+--------------+---------------------+

输出表

+----+--------------+---------------------+
| id | update_index |    estimated_date   |
+----+--------------+---------------------+
| 73 |       1      | 2017-06-13 00:00:00 |
+----+--------------+---------------------+
| 75 |       1      | 2017-01-01 00:00:00 |
+----+--------------+---------------------+
| 76 |       2      | 2018-06-01 00:00:00 |
+----+--------------+---------------------+

我试过下面的值,但我总是只得到一个记录,你能帮我吗?

SELECT  id,update_index,estimated_date
FROM tablename where estimated_date = (
       SELECT DISTINCT estimated_date
       FROM tablename
        where estimated_date is not null
       ORDER BY id, udpate_index ASC
       LIMIT 1);
 `
z4bn682m

z4bn682m1#

你不能这样做:
选择t.id,t.update\u index,t.estimated\u date from tablename t where t.update\u index=(从tablename t1选择min(update\u index),其中t1.id=t.id,t1.estimated\u date不为空)按t.id分组

guykilcj

guykilcj2#

使用子查询 LIMIT 条款:

select t.*
from table t
where t.update_index = (select t2.update_index
                        from table t2
                        where t2.id = t.id and t2.estimated_date is not null
                        order by t2.estimated_date desc
                        limit 1
                       );

然而,你的样本数据更让我想到:

select t.*
from table t
where t.estimated_date = (select max(t2.estimated_date)
                          from table t2
                          where t2.id = t.id and t2.estimated_date is not null
                         );

如果你和 estimated_date 那么第一种方法将起作用。

8zzbczxx

8zzbczxx3#

我将使用相关子查询:

select t.*
from tablename t
where t.update_index = (select t2.update_index
                        from tablename t2
                        where t2.id = t.id and t2.estimated_date is not null
                        order by t2.update_index asc
                        limit 1
                       );
njthzxwz

njthzxwz4#

请试试这个。

select t2.*
    from
    (select id, min(update_index) updated_index 
    from input_table 
    where estimated_date is not null
    group by 1 )t1
    left join input_table t2
    on t1.id = t2.id
    and t1.update_index = t2.update_index

相关问题