代码之家 › 专栏 › 技术社区 › Mahesh G

带Select的MYSQL查询具有Distinct With LIMIT条件

sql mysql

Mahesh G · 技术社区 · 6 年前

目前,我有一个如下的表,我想提取id为的记录,这些记录具有估计的\u日期,分配给update\u index value的最小值,如果该值为null,我想转到分配给下一个update\u index的值,

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);
 `

4 回复 | 直到 6 年前

Gordon Linoff 6 年前

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
                       );

Tushar Kumawat 6 年前

你不能这样做:

选择t.id,t.update\u index,t.estimated\u date 来自表名t 其中t.update\u index=(从tablename t1中选择MIN(update\u index),其中t1.id=t.id,t1.estimated\u date不为空)

nitzien 6 年前

    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

Yogesh Sharma 6 年前

使用子查询 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 那么第一种方法将起作用。