访问查询 (SQL) 以返回按 WEEKS 排序(分组)的记录 [英] Access query (SQL) to return records sorted (grouped by) WEEKS
问题描述
问候 SQL 大师，
我不知道你能不能帮我，但我会努力的.我有几个按年份分组的大型数据库(每年在不同的数据库中).我希望能够比较从一年到下一年特定周的值.例如，向我展示 2008 年的第 17 周与 2002 年的第 17 周."
I don't know if you can help me, but I will try. I have several large databases grouped by year (each year in a different database). I want to be able to compare values from a particular week from one year to the next. For example, "show me week 17 of 2008 vs. week 17 of 2002."
我有以下理想的周定义:
I have the following definition of weeks that ideally I would use:
- 每年仅 52 周，每周 7 天(只需 364 天)，
- 第一周的第一天从 1 月 2 日开始 - 这意味着我们不使用 1 月 1 日的数据，并且
- 在闰年，第一周的第一天也从 1 月 2 日开始，然后我们跳过 2 月 29 日.
有什么想法吗?
提前致谢.
推荐答案
最好避免创建表，因为这样您就必须更新和维护它才能使您的查询工作.DatePart('ww',[myDate]) 会给你周数.在决定哪一周属于哪一年时，您可能会遇到一些问题 - 例如，如果 2003 年 1 月 1 日是星期三，那么该周是属于 2002 年的第 52 周还是 2003 年的第 1 周?您的会计部门将在一周中的某一天作为您的周末(通常是周六).我通常只选择天数最多的年份.DatePart 将始终将第一周计为 1，在上面的示例中将上周计为 53.无论哪种方式，您都可能不太在意.您可以为每年创建查询
Best to avoid creating a table because then you have to update and maintain it to get your queries to work.
DatePart('ww',[myDate]) will give you the week number. You may run into some issues though deciding which week belongs to which year - for example if Jan 1 2003 is on Wednesday does the week belong as week 52 in 2002 or week 1 in 2003? Your accounting department will have a day of the week that is your end of week (usually Sat). I usually just pick the year that has the most days in it. DatePart will always count the first week as 1 and in the case of the example above the last week as 53. You may not care that much either way. You can create queries for each year
SELECT DatePart('ww',[myDate]) as WeekNumber,myYearTable.* as WeekNumber
FROM myYearTable
然后加入查询以获取您的数据.如果一张桌子有 52 周，一张桌子有 53 周(大多数将显示为 53)，那么你会在年底失去几天.或者你可以在周末的那一天完成——这总是给你星期六，这会将下一年的晚一周推到下一年.
and then join the queries to get your data. You'll loose a couple days at the end of the year if one table has 52 weeks and one has 53 (most will show as 53). Or you can do it by your weekending day - this always gives you Saturday which would push a late week into the following year.
(7-Weekday([myDate]))+[myDate]
然后
DatePart('ww',(7-Weekday([myDate]))+[myDate])
希望有帮助
这篇关于访问查询 (SQL) 以返回按 WEEKS 排序(分组)的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
