DAYSOFWEEK 之间的 SQL

发布于 2024-08-09 04:15:29 字数 403 浏览 9 评论 0原文

我进行了广义搜索,查找一周中不同日期的平均价格。问题是我不知道如何要求一个奇怪的范围。 1-7 工作正常,(周一至周日),但如果用户将周日传递到周二......即 7-2 它不起作用。

1-7(1,2,3,4,5,6,7) 7-2 (7,1,2)

等。

除了 BETWEEN 之外,我还能如何传递更智能的范围或其他东西?

SELECT item_id, DAYOFWEEK(bookdate) as date, bookdate, AVG(price) AS price 
FROM `availables` WHERE (item_id = 16 and DAYOFWEEK(bookdate) BETWEEN 2 AND 7) 
GROUP BY DAYOFWEEK(bookdate)

I have a generalized search where I'm looking up average prices for the various days of the week. The problem is I'm not sure how to ask for an odd range. 1-7 works fine, (monday-sunday), but if the user passes a sunday to a tuesday... ie 7-2 it doesn't work.

1-7 (1,2,3,4,5,6,7)
7-2 (7,1,2)

etc.

How else can I pass a range that is more intelligent or something besides BETWEEN?

SELECT item_id, DAYOFWEEK(bookdate) as date, bookdate, AVG(price) AS price 
FROM `availables` WHERE (item_id = 16 and DAYOFWEEK(bookdate) BETWEEN 2 AND 7) 
GROUP BY DAYOFWEEK(bookdate)

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

情话难免假 2024-08-16 04:15:29

也许您正在寻找的是 IN 语法:

SELECT item_id, DAYOFWEEK(bookdate) as date, bookdate, AVG(price) AS price 
FROM `availables` WHERE (item_id = 16 and DAYOFWEEK(bookdate) IN (7,1,2)) 
GROUP BY DAYOFWEEK(bookdate)

Perhaps what you are looking for is the IN syntax:

SELECT item_id, DAYOFWEEK(bookdate) as date, bookdate, AVG(price) AS price 
FROM `availables` WHERE (item_id = 16 and DAYOFWEEK(bookdate) IN (7,1,2)) 
GROUP BY DAYOFWEEK(bookdate)
云巢 2024-08-16 04:15:29

使用“in”子句执行此操作,如下所示:dayofweek(bookdate) in (1,2,7)

这也允许非连续日期的灵活性(例如,仅选择星期一和星期五的记录)

Do it with an 'in' clause, like this: dayofweek(bookdate) in (1,2,7)

This also allows the flexibility of non-continuous dates (select records for Monday and Friday only, for example)

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文