如何根据表 1 中的日期从表 2 中选择最小/最大日期?

问题描述

我有一个月度表(仅包含具有每月第一天的行，并且具有唯一约束，因此每个行只有一个)和一个每日表，其中每天都有类似的信息(相同的交易，每天只有一个):

I have a monthly table (only holds rows with first day of month, and unique constraint so only one of each) and a daily table with similar information for each day (same deal, only one per day):

Monthly table       Daily table
-------------       -----------
2009-01-01          2009-01-01
2009-02-01          2009-01-02
: :                 : :
2009-09-01          2009-01-31
                    2009-02-01
                    : :
                    2009-02-28
                    2009-03-01
                    : :
                    2009-09-01

但可能会有几天不见.

我想运行一个查询，为月表中的每个日期返回该日期以及日表中该月的最小和最大日期(如果绝对必要，最好使用 DB2 特定的标准 SQL).

I want to run a query that returns, for each date in the monthly table, that date along with the minimum and maximum dates for that month in the daily table (using standard SQL preferably of DB2-specific if absolutely necessary).

所以，如果缺少一月的最后一周和二月的第一周，我需要:

So, if the last week of January and first week of February is missing, I need:

MonthStart  FirstDay    LastDay
----------  ----------  ----------
2009-01-01  2009-01-01  2009-01-24
2009-02-01  2009-02-08  2009-02-28
: :
2009-09-01  2009-09-01  2009-01-30

这是我的查询:

select m.date as m1,
       dx.d1 as m2,
       dx.d2 as m3
from   monthly m,
       ( select min(d.date) as d1,
                max(d.date) as d2
         from   daily d
         where  month(d.date) = month(m.date)
         and    year(d.date) = year(m.date)
       ) as dx;

但我收到了错误:

DSNT408I SQLCODE = -206，错误:M.DATE 不是一列
插入的表格、更新的表格或任何已识别的表格
在 FROM 子句中，或者不是触发的列
触发器表
DSNT418I SQLSTATE = 42703 SQLSTATE 返回码

有人对如何最好地做到这一点有任何建议吗?

Does anyone have any advice on how best to do this?

推荐答案

SELECT m.date as m1,
       MIN(d.date) as m2,
       MAX(d.date) as m3
       -- COUNT(d.date) as NbOfDays  -- if so desired...
FROM monthly m
JOIN daily d ON month(d.date) = month(m.date) and year(d.date) = year(m.date)
WHERE -- some condition
GROUP BY m.date
ORDER BY m.date -- or something else...

请注意，这种类型的查询(如问题中显示的其他查询和迄今为止的回复一样，相对较慢，因为它们意味着表扫描，因此可以为每个查询计算月(x)和年(x)[qualifying] 行.(即，在 WHERE 子句中设置日期范围当然会有所帮助).如果这种类型的查询经常运行并且表的大小很大，那么 it 可能有助于添加这些计算值的列(或可能用于组合值(年-月)，因此不仅不需要计算，而且可以索引基础列.

Note that this type of query (as the other ones shown in the question and in responses so far, are relatively slow, as they imply a table scan so the month(x) and year(x) can be calculated for each [qualifying] row. (i.e. putting a range on date in the WHERE clause would of course help). If this type of query is ran frequently and if the size of table is significant, it may be useful to add a column for these computed values (or possibly for the combined value (Year-Month), so that not only the calculation is unnecessary but the underlying column(s) can be indexed.

这篇关于如何根据表 1 中的日期从表 2 中选择最小/最大日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！