英文:
Oracle SQL: Select Value From Comparing Date With Another Tables Rows
问题
我正在尝试编写ORACLE SQL，但在其中陷入了困境。我没有任何可以展示的内容。
我有一个名为START_PERIOD的表格
| id | start_date | end_date |
|---|---|---|
| 1 | 2022年01月01日 | 2022年03月31日 |
| 2 | 2022年04月01日 | 2022年06月30日 |
| 3 | 2022年07月01日 | 2022年09月30日 |
| 4 | 2022年10月01日 | 2022年12月31日 |
我有一个名为APPLICATION_FORM的第二个表格。APPLICATION_FORM表格包含一个名为"CS_START_DATE"的日期字段，以及一个名为SP_ID的字段（该值是START_PERIOD.ID）。
根据APPLICATION_FORM.CS_START_DATE的值，分配了APPLICATION_FORM.SP_ID，并且这是从表START_PERIOD派生的，即APPLICATION_FORM.CS_START_DATE为2022年02月01日落在了2022年01月01日和2022年03月31日之间，因此APPLICATION_FORM.SP_ID为1。
不幸的是，SP_ID和CS_START_DATE的关系已经更改，现在不正确。
我想要使用APPLICATION_FORM.CS_START_DATE与表START_PERIOD中的每个日期范围进行比较（如果它落在START_DATE和END_DATE之间），然后从表START_PERIOD返回正确的ID。然后，我将使用此ID与APPLICATION_FORM.SP_ID进行比较，以查看是否存在不匹配。理论上，APPLICATION_FORM.CS_START_DATE始终是正确的，但APPLICATION_FORM.SP_ID可能不正确。
英文:
I'm trying to write ORACLE SQL but are tying myself in knots with it. I have nothing to show.
I have a table called START_PERIOD
| id | start_date | end_date |
|---|---|---|
| 1 | 01/01/2022 | 31/03/2022 |
| 2 | 01/04/2022 | 30/06/2022 |
| 3 | 01/07/2022 | 30/09/2022 |
| 4 | 01/10/2022 | 31/12/2022 |
I have a second table called APPLICATION_FORM. The APPLICATION_FORM table holds a date field called "CS_START_DATE", plus a field called SP_ID (this value is START_PERIOD.ID ).
APPLICATION_FORM.SP_ID is assigned based on the value of APPLICATION_FORM.CS_START_DATE and this is derived from table START_PERIOD, that is a APPLICATION_FORM.CS_START_DATE of 01/02/2022 falls between 01/01/2022 and 31/03/2022 therefore the APPLICATION_FORM.SP_ID is 1.
Unfortunately the SP_ID and CS_START_DATE relationship has been altered and is now incorrect.
I want to use APPLICATION_FORM.CS_START_DATE and compare it against each date range within table START_PERIOD (where it falls between START_DATE AND END_DATE) and return the correct ID from table START_PERIOD. I'll then use this ID to compare it against the APPLICATION_FORM.SP_ID to see if there is a mismatch. In theory the APPLICATION_FORM.CS_START_DATE is always correct but the APPLICATION_FORM.SP_ID could be incorrect.
答案1
得分: 0
你可以尝试使用以下SQL查询：
SELECT start_period.id AS correct_id,
application_form.sp_id AS incorrect_id
FROM start_period, application_form
WHERE application_form.cs_start_date BETWEEN start_period.start_date
AND start_period.end_date
英文:
You can try with the following SQL
SELECT start_period.id AS correct_id,
application_form.sp_id AS incorrect_id
FROM start_period, application_form
WHERE application_form.cs_start_date BETWEEN start_period.start_date
AND start_period.end_date
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论