使用 Oracle SQL 选择生日在给定范围内的员工
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9861529/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting employees with birthdays in given range using Oracle SQL
提问by Mite Mitreski
For selecting birthdays between two months where FROMDATE and TODATE are some parameters in a prepared statement I figured something like this:
为了在两个月之间选择生日，其中 FROMDATE 和 TODATE 是准备好的语句中的一些参数，我想像这样：
select
p.id as person_id,
...
...
where e.active = 1
and extract(month from TODATE) >= extract(month from e.dateOfBirth)
and extract(month from e.dateOfBirth) >= extract(month from FROMDATE)
order by extract(month from e.dateOfBirth) DESC,
extract(day from e.dateOfBirth) DESC
How can this be improved to work with days as well?
如何改进这一点以使其与天一起工作？
采纳答案by Mite Mitreski
At the end we picked litter different solution where we add fist create the anniversary date :
最后，我们选择了垃圾不同的解决方案，我们添加拳头创建周年纪念日：
where
...
and (to_char(sysdate,'yyyy') - to_char(e.dateofbirth,'yyyy')) > 0
and add_months(e.dateofbirth,
(to_char(sysdate,'yyyy') - to_char(e.dateofbirth,'yyyy')) * 12)
>:fromDate:
and :toDate: > add_months(e.dateofbirth,
(to_char(sysdate,'yyyy') - to_char(e.dateofbirth,'yyyy')) * 12)
order by extract(month from e.dateofbirth) DESC,
extract(day from e.dateofbirth) DESC)
回答by APC
There's more than one way to search date ranges in Oracle. For your scenario I suggest you turn the month and day elements of all the dates involved into numbers.
在 Oracle 中搜索日期范围的方法不止一种。对于您的场景，我建议您将所有涉及的日期的月份和日期元素转换为数字。
select
p.id as person_id,
...
...
where e.active = 1
and to_number (to_char( e.dateOfBirth, 'MMDD'))
between to_number (to_char( FROMDATE, 'MMDD'))
and to_number (to_char( TODATE, 'MMDD'))
order by extract(month from e.dateOfBirth) DESC,
extract(day from e.dateOfBirth) DESC
This won't use any index on the e.dateOfBirthcolumn. Whether that matters depends on how often you want to run the query.
这不会在e.dateOfBirth列上使用任何索引。这是否重要取决于您希望运行查询的频率。
@AdeelAnsari comments:
@AdeelAnsari 评论：
" I don't like to to_char the predicate, in order to make use of index"
“我不喜欢 to_char 谓词，以便利用索引”
What index? A normal index on dateOfBirthisn't going to be of any use, because the index entries will lead with the year element. So it won't help you find all the records of people born on any23rd December.
什么指数？正常的索引dateOfBirth没有任何用处，因为索引条目将以年份元素开头。因此，它不会帮助您找到任何12 月 23 日出生的人的所有记录。
A function-based index - or in 11g, a virtual column with an index (basically the same thing) - is the only way of indexing parts of a date column.
基于函数的索引——或者在 11g 中，带有索引的虚拟列（基本相同）——是索引日期列部分的唯一方法。
回答by manix
This is the query that I am using for birtdates in the next 20 days:
这是我在接下来的 20 天内用于 birtdates 的查询：
SELECT A.BIRTHDATE,
CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12)) AGE_NOW,
CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE + 20) / 12)) AGE_IN_20_DAYS
FROM USERS A
WHERE
CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12)) <> CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE + 20) / 12));
ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12))return the age in format 38.9, 27.2, etcApplying
ceil()will give us the difference in years that we need to determinate if this person is near to have a birthdate. Eg.- Age: 29.9
- 29.9 + (20 days) = 30.2
- ceil(30.1) - ceil(29.9) = 1
ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12))以 38.9、27.2 等格式返回年龄申请
ceil()将给我们提供需要确定此人是否临近生日的年份差异。例如。- 年龄：29.9
- 29.9 +（20 天）= 30.2
- ceil(30.1) - ceil(29.9) = 1
This is the result of querying at december 16:
这是查询的结果december 16：
BIRTHDATE AGE_NOW AGE_IN_20_DAYS
12/29/1981 35 36
12/29/1967 49 50
1/3/1973 44 45
1/4/1968 49 50
回答by Randy
you should be able to use
你应该可以使用
SELECT * FROM mytbale
where dateofbirth between start_dt and end_dt
alternate:
备用：
you can convert dates to the day of the year using:
您可以使用以下方法将日期转换为一年中的某一天：
to_char( thedate, 'ddd' )
then check the range (note this has the same issue as @Dems answer where you should not span the end of the year as in Dec 25th through Jan 10th.)
然后检查范围（请注意，这与@Dems 的回答存在相同的问题，您不应该像 12 月 25 日到 1 月 10 日那样跨越年底。）
回答by MatBailie
Do you need maximum performance, and so are willing to make a change to the schema? Or are the number of records so small, and performance relatively un-important, that you want a query that will work with the data as-is?
您是否需要最高性能，因此愿意对架构进行更改？还是记录数量如此之少，而性能相对不重要，以至于您需要一个可以按原样处理数据的查询？
The simplest and fastest way to do this is to store a second data-of-birth field, but 'without' the year. I put quotes around 'without' because a date can't actually not have a year. So, instead, you just base it on another year.
最简单和最快的方法是存储第二个出生数据字段，但“没有”年份。我在“没有”周围加上引号，因为日期实际上不能没有一年。因此，相反，您只需将其建立在另一年的基础上。
Re-dating every DoB to the year 2000 is a good choice in my experience. Because it includes a leap-year and is a nice round number. Every DoB and FromDate and ToDate will work in the year 2000...
根据我的经验，将每个 DoB 重新约会到 2000 年是一个不错的选择。因为它包含一个闰年并且是一个很好的整数。每个 DoB 和 FromDate 和 ToDate 将在 2000 年工作......
WHERE
DoB2000 >= FromDate
AND DoB2000 <= ToDate
(This assumes you also index the new field to make the search quick, otherwise you still get a scan, though it MAY be faster than the following alternative anyway.)
（这假设您还为新字段建立索引以加快搜索速度，否则您仍然会得到扫描，尽管它可能比以下替代方案更快。）
Alternatively, you can keep using the EXTRACT pattern. But that will have an unfortunate consequence; it's extremely messy and you will never get an Index Seek, you will always get an Index Scan. This is because of the fact that the searched field is wrapped in a function call.
或者，您可以继续使用 EXTRACT 模式。但这会带来不幸的后果；它非常混乱，你永远不会得到索引搜索，你总是会得到索引扫描。这是因为搜索字段包含在函数调用中。
WHERE
( EXTRACT(month FROM e.DateOfBirth) > EXTRACT(month FROM FromDate)
OR ( EXTRACT(month FROM e.DateOfBirth) = EXTRACT(month FROM FromDate)
AND EXTRACT(day FROM e.DateOfBirth) >= EXTRACT(day FROM FromDate)
)
)
AND
( EXTRACT(month FROM e.DateOfBirth) < EXTRACT(month FROM ToDate)
OR ( EXTRACT(month FROM e.DateOfBirth) = EXTRACT(month FROM ToDate)
AND EXTRACT(day FROM e.DateOfBirth) <= EXTRACT(day FROM ToDate)
)
)
回答by MJB
I don't know how this behaves in terms of performance, but I'd try using regular date subtraction. Say, for example, you want birth days between January 1st and July 1st. Anyone who is between 25 and 25.5 would qualify. That is, anyone whose partial age is less than 1/2 year would qualify. The math is easy at 1/2, but it is the same regardless of the window. In other words, "within one month" means +/- 1/12 of a year, within 1 day means +/- 1/365 of a year, and so on.
我不知道这在性能方面的表现如何，但我会尝试使用常规的日期减法。例如，假设您希望出生日期介于 1 月 1 日和 7 月 1 日之间。任何介于 25 和 25.5 之间的人都有资格。也就是说，任何部分年龄小于 1/2 岁的人都有资格。数学在 1/2 处很容易，但无论窗口如何，它都是一样的。换句话说，“一个月内”表示一年的 +/- 1/12，一天内表示一年的 +/- 1/365，依此类推。
The year does not matter in this method, so I'll use current year when creating the variable.
在此方法中年份无关紧要，因此我将在创建变量时使用当前年份。
Also, I would think of the center of the range, and then do absolute values (either that, or always use a future date).
另外，我会考虑范围的中心，然后做绝对值（要么，要么总是使用未来的日期）。
For example
例如
select personid
from mytable
where abs(mod(dob - target_birth_date)) < 1/52
would give you everyone with a birthday within a week.
会在一周内给你每个人的生日。
I realize this code is barely pseudocode, but is seems like it might allow you to do it, and you might still use indices if you tweaked it a little. Just trying to think outside the box.
我意识到这段代码几乎不是伪代码，但看起来它可能允许你这样做，如果你稍微调整一下，你可能仍然使用索引。只是想跳出框框思考。
回答by Sinan
That's the solution!!!
这就是解决方案！！！
SELECT
*
FROM
PROFILE prof
where
to_date(to_char(prof.BIRTHDATE, 'DDMM'), 'DDMM') BETWEEN sysdate AND sysdate+5
or
或者
add_months(to_date(to_char(prof.BIRTHDATE, 'DDMM'), 'DDMM'),12) BETWEEN sysdate AND sysdate+5
回答by samuel
Guys I have a simpler solution to this problem
伙计们，我有一个更简单的解决方案来解决这个问题
- step 1. convert the month into number,
- step 2. concatenate the day(in two digits) to the month
- step 3. then convert the result to number by doing to_number(result)
- step 4. Once you have this for the start date and end date, then do a between function on it and u are done.
- 步骤 1. 将月份转换为数字，
- 步骤 2. 将日期（两位数）连接到月份
- 步骤 3. 然后通过执行 to_number(result) 将结果转换为数字
- 第 4 步。一旦你有了这个开始日期和结束日期，然后在它上面做一个功能，你就完成了。
sample code:
示例代码：
SELECT date_of_birth
FROM xxxtable
where to_number(to_number(to_char(to_date(substr(date_of_birth,4,3),'mon'),'mm'))||substr(date_of_birth,1,2)) between
to_number(to_number(to_char(to_date(substr(:start_date_variable,4,3),'mon'),'mm'))||(substr(:start_date_variable,1,2))) and
to_number(to_number(to_char(to_date(substr(:end_date_variable,4,3),'mon'),'mm'))||(substr(:end_date_variable,1,2)));
