如何使用 SQL 选择给定工作日的条目? [英] How can I select entries for a given weekday using SQL?

问题描述

我可以使用此查询来选择日期为星期一的所有订单:

I could use this query to select all orders with a date on a monday:

SELECT * from orders WHERE strftime("%w", date)="1";

但据我所知，使用索引无法加快速度，因为必须计算每一行 strftime.

But as far as I know, this can't be speed up using an index, as for every row strftime has to be calculated.

我可以添加一个存储工作日的附加字段，但我想避免它.是否有使用索引的解决方案，或者我错了并且这个查询实际上工作正常?(这意味着它不必遍历每一行来计算结果.)

I could add an additional field with the weekday stored, but I want to avoid it. Is there a solution that makes use of an index or am I wrong and this query actually works fine? (That means it doesn't have to go through every row to calculate the result.)

推荐答案

如果您想要所有星期一，您需要进行现场扫描或顺序扫描.您可以做的是计算实际日期，例如一年内所有星期一的日期.条件 WHERE date IN ('2009-03-02', '2009-02-23', ...) 将使用索引

If you want all Mondays ever, you'd need a field or sequential scan. What you could do, is calculate actual dates for example for all Mondays within a year. The condition WHERE date IN ('2009-03-02', '2009-02-23', ...) would use index

这篇关于如何使用 SQL 选择给定工作日的条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！