SQL查询按日期查找特定记录

发布于 2024-11-04 04:36:37 字数 985 浏览 0 评论 0原文

在 Oracle 10g 环境中工作。

假设您收到： *注意（持续时间以周为单位）

CLASSNAME                 INSTURCTOR        DAYS     STARTDATE   *DURATION TIMESTART TIMEEND
------------------------- ----------------------- ------------- ---------  -------- --------
Power Dance               Robert Backman    MWF      03-MAR-11          2 0930       1100
Power Dance               Lynn Parker       MWF      03-MAY-11          2 0930       1100
Power Dance               Lynn Parker       MTTh     18-MAY-11          2 1230       0100
Club Stretch              Kevin Lopez      MT       24-OCT-11          3 1930          2015
Club Stretch              Kevin Lopez       F        17-JUN-11          3 1130       1300
Hatha Yoga                Susan Wanzer      MW       25-MAY-11          3 1900       2000

用户希望能够查询给定特定日期的课程的课程名称、讲师、开始时间和结束时间。

我了解如何使用 (Duration * 7) + StartDate 查找 EndDate。我遇到的麻烦是找出一周中的某一天有哪些课程。正如用户在 2011 年 6 月 24 日输入的那样，唯一应该显示的类别应该是 Club Stretch。

原文

Working from an oracle 10g environment.

Suppose you were given: *NOTE (Duration is in Weeks)

CLASSNAME                 INSTURCTOR        DAYS     STARTDATE   *DURATION TIMESTART TIMEEND
------------------------- ----------------------- ------------- ---------  -------- --------
Power Dance               Robert Backman    MWF      03-MAR-11          2 0930       1100
Power Dance               Lynn Parker       MWF      03-MAY-11          2 0930       1100
Power Dance               Lynn Parker       MTTh     18-MAY-11          2 1230       0100
Club Stretch              Kevin Lopez      MT       24-OCT-11          3 1930          2015
Club Stretch              Kevin Lopez       F        17-JUN-11          3 1130       1300
Hatha Yoga                Susan Wanzer      MW       25-MAY-11          3 1900       2000

A user wants to be able to query the Classname, Instructor, Timestart, and TimeEnd for a class given a specific date.

I understand how to find the EndDate using (Duration * 7) + StartDate. The trouble I am having is finding out which classes are running on a day of the week. As in say the user enters in 24-JUN-11, the only class that should show up should be Club Stretch.

分享到QQ

分享到微博

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

发布评论

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

寄意 2024-11-11 04:36:37

这可能只是重新表述您的问题的问题：您想知道哪些课程在指定日期之前开始，并在指定日期或之后结束。
您已经有了开始日期，并且知道如何计算结束日期。

这将是您的解决方案：

SELECT [columns] 
FROM [table] 
WHERE @specifiedDate BETWEEN [startDate] AND [calculatedEndDate]
    AND [days] LIKE @specifiedWeekday

在查找星期一时，@specifiedWeekday 应该具有如下值（在 MS SQL 中）：'%M%'

注意：现在阅读您在 Oracle 中的工作。这是 SQL Server 的答案。我希望它背后的想法能够帮助你。

It might be just a matter of rephrasing your question: you want to know which classes are starting at of before the specified date and are ending at or after this specified date.
You already have the starting date and you know how to calculate the ending date.

This will be your solution:

SELECT [columns] 
FROM [table] 
WHERE @specifiedDate BETWEEN [startDate] AND [calculatedEndDate]
    AND [days] LIKE @specifiedWeekday

@specifiedWeekday should, when looking for a Monday, have a value like this (in MS SQL): '%M%'

NOTE: Just now read your working in Oracle. This is a SQL Server answer. I hope the idea behind it will help you along.

回复收藏 0 原文

星光不落少年眉 2024-11-11 04:36:37

在我看来，您需要首先确定一周中的哪一天，将其存储为要提供给第二个查询的变量。下面第一部分的查询示例：

select to_char(to_date('31-May-2011','dd-Mon-yyyy'),'DAY') from dual
SQL> /

TO_CHAR(T
---------
TUESDAY

或者

select to_char(to_date('31-May-2011','dd-Mon-yyyy'),'DY') from dual
TO_
---
TUE

然后您可以将其与您现在已知的一周中的某一天一起输入到您的其他查询中。如果您需要将其解码为您提到的格式，那么以下代码会将星期几转换为您显示的缩写：

select decode(to_char(to_date('26-May-2011','dd-Mon-yyyy'),'DY'), 'MON','M',
                                                                  'TUE','T',
                                                                  'WED','W',
                                                                  'THU','Th',
                                                                  'FRI','F',
                                                                  'SAT','Sa','Su') 
from dual

将返回您正在使用的 Th :)

Looks to me that you need to first determine the day of the week store that as a variable that you would feed to the second to the second query. Query sample for first part below:

select to_char(to_date('31-May-2011','dd-Mon-yyyy'),'DAY') from dual
SQL> /

TO_CHAR(T
---------
TUESDAY

select to_char(to_date('31-May-2011','dd-Mon-yyyy'),'DY') from dual
TO_
---
TUE

then you could feed that into your other query with your now known day of the week. If you need to decode it to the formats you mention then the following code will convert the day of the week to the abbreviations you are showing:

select decode(to_char(to_date('26-May-2011','dd-Mon-yyyy'),'DY'), 'MON','M',
                                                                  'TUE','T',
                                                                  'WED','W',
                                                                  'THU','Th',
                                                                  'FRI','F',
                                                                  'SAT','Sa','Su') 
from dual

will return the Th you are using :)

回复收藏 0 原文

~没有更多了~