mysql - 在 MySQL 中选择所有具有不同和条件的行

Question

大家好，我是 mySQL 的新手，我遇到了查询问题。我试图编写一些查询，从表中获取所有记录，如果我有两个日期相同的记录，我只需要在这两个记录之间获取 manual_selection = 1 的记录。

所以结果应该是我表中除 id = 1401 和 id = 1549 之外的所有记录

my table

我尝试结合如何获得这样的记录:

SELECT * FROM project.score WHERE project_id = 358 
AND crawled_at IN(SELECT crawled_at FROM project.score WHERE project_id = 358 
AND manual_selection = 1 GROUP BY crawled_at)
ORDER BY crawled_at;      

SELECT * FROM project.score WHERE project_id = 358 
GROUP BY crawled_at HAVING manual_selection = 1;  

但我一直只获得 manual_selection = 1 的行。我不知道如何在 manual_selection = 1 的情况下区分具有重复“crawled_at”的行。有人可以帮助我吗？

Answer 1

试试这个:

select main.id, main.project_id, main.crawled_at, main.score, main.manual_selection
from dcdashboard.moz_optimization_keywords as main
left join dcdashboard.moz_optimization_keywords as non_manual_selection on non_manual_selection.crawled_at = main.crawled_at and non_manual_selection.manual_selection != 1
group by main.crawled_at;

问题数据集的结果:

+------+------------+---------------------+-------+------------------+
| id   | project_id | crawled_at          | score | manual_selection |
+------+------------+---------------------+-------+------------------+
|  807 |        360 | 2016-02-06 00:00:00 |    76 |                0 |
| 1001 |        360 | 2016-02-20 00:00:00 |    76 |                0 |
|  223 |        360 | 2016-11-28 00:00:00 |    76 |                0 |
|  224 |        360 | 2016-12-05 00:00:00 |    76 |                0 |
|  670 |        360 | 2016-12-19 00:00:00 |    76 |                0 |
| 1164 |        360 | 2017-04-19 00:00:00 |    78 |                1 |
| 1400 |        360 | 2017-09-13 00:00:00 |    96 |                1 |
| 1548 |        360 | 2017-09-15 00:00:00 |    96 |                1 |
+------+------------+---------------------+-------+------------------+
8 rows in set (0.00 sec)