英文:
Joining tables with dates IN SQL
问题
如何在SQL中连接这两个表以获得所需的视图？
我有很多产品。
我尝试将产品连接起来，然后在开始日期和结束日期之间选择日期，但没有成功。
我该如何做？
英文:
spread-sheet rendering of two tables given and view wanted
How I can JOIN these tables in SQL to have that view?
I have a lot of products.
I TRIED TO JOIN PRODUCT THEN DATE BETWEEN START_DATE AND END_DATE and it's not working.
How I can do this?
答案1
得分: 1
使用子查询是解决这种情况的更好方式，因为您需要一个范围输出。
SELECT
*
FROM
table1
WHERE
table1.data BETWEEN (SELECT data_start FROM table2) AND (SELECT data_end FROM table2)
其输出与您的预期输出相匹配。
这里是Fiddle
并确保table2中只有1条记录，以便查询可以精确选择一个起始日期和结束日期，否则在BETWEEN子查询中使用MAX与值。
英文:
Using subquery is a better way to address this scenario since you need a ranged output.
SELECT
*
FROM
table1
WHERE
table1.data BETWEEN (SELECT data_start FROM table2) AND (SELECT data_end FROM table2)
Its output matches your expected output.
Here's the Fiddle
And make sure there's only 1 record in table2 so that query can pick exactly one start and end date, otherwise, use MAX with the values in the BETWEEN subqueries.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论