Mysql日期与IN语句的比较

Question

我有一个sql查询

我有以下查询

SELECT * FROM articles where `id` =1 AND `datatime` > ='datetime1';
UNION ALL
SELECT * FROM articles where `id` =2 AND `datatime` > ='datetime2';
UNION ALL
SELECT * FROM articles where `id` =3 AND `datatime` > ='datetime3';
UNION ALL
SELECT * FROM articles where `id` =4 AND `datatime` > ='datetime4';

哪个工作正常

现在的问题是，如果有更大的列表，可能超过 10000，那么我该如何处理这个查询。还有其他方法可以执行此查询吗？

Answer 1

您应该在一个查询中执行此操作，而不是联合。

SELECT * FROM articles where 
(`id` =1 AND `datatime` > ='datetime1')
or
(`id` =2 AND `datatime` > ='datetime2')
or
(`id` =3 AND `datatime` > ='datetime3')
or
(`id` =4 AND `datatime` > ='datetime4');

你也可以这样做:

SELECT * FROM articles where 
(id, `datatime`) IN (SELECT 1, 'datetime1'
                     UNION ALL
                     SELECT 2, 'datetime2'
                     UNION ALL
                     SELECT 3, 'datetime3'
                     UNION ALL
                     SELECT 4, 'datetime4'
                    );

如果数据时间值始终相同，您可以这样做:

SELECT * FROM articles where 
id IN (1, 2, 3, 4)
and datatime = 'datetime_value';

如果您的值列表变得非常大，最好先将这些值放入表中并加入其中。

SELECT * FROM articles a
INNER JOIN your_values_table yvt ON a.id = yvt.id AND a.datatime = yvt.datatime;