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

amrnrhlw 于 2021-06-19 发布在 Mysql

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

目前，我有一个如下的表，我想提取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);
 `

sql mysql

来源：https://stackoverflow.com/questions/52389175/mysql-query-with-select-having-distinct-with-limit-condition

4条答案

按热度按时间

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分组

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

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

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

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

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

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

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

我来回答

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

4条答案

相关问题

热门标签

最新问答