mysql - 按 bool 列标志选择记录

Question

我有一张这样的表:

+----+--------------+-----------+------------+
| id | natural_id   | flag      | date       |
+----+--------------+-----------+------------+                                           
| 1  |           1  |  true     | 01/01/2000 |                                      
| 2  |           1  |  true     | 02/01/2000 |
| 3  |           1  |  false    | 03/01/2000 |
| 3  |           2  |  true     | 01/01/2000 |
| 4  |           2  |  false    | 02/01/2000 |
| 5  |           2  |  false    | 03/01/2000 |
+----+--------------+-----------+------------+

我正在尝试编写 select，它将根据 first flag< 显示具有 natural_id 和 date 的记录/strong> 错误值:

+--------------+------------+
| natural_id   | date       | 
+--------------+------------+                                      
|           1  |  03/01/2000|                               
|           2  |  02/01/2000|
+---------------------------+

我不知道如何在 SQL 中执行此操作。在纯 SQL 中甚至可能吗？

Answer 1

按 natural_id 分组:

select 
  natural_id,
  min(date) date
from tablename
where flag = 'false'
group by natural_id

如果标志的类型是BOOLEAN，你可以这样做:

select 
  natural_id,
  min(date) date
from tablename
where not flag
group by natural_id