如何根据表1中的日期从表2中选择最小/最大日期？ [英] How do I select min/max dates from table 2 based on date in table 1?
问题描述
我有一个月表（只保留行与第一个月的日子，唯一的约束，所以每个只有一个）和每天的表与每天相似的信息（相同的交易，每天只有一个）：
每月表每日表
------------- -------- ---
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
但可能会有几天没有。
我想运行一个查询，为每个表中的每个日期返回该日期，以及该日期表中该月份的最小和最大日期（如果绝对需要，则使用标准SQL，最好是DB2特定的）。 / p>
所以，如果一月的最后一个星期二月的第一周失踪，我需要：
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。日期）为d2
从每日d
其中月（d.date）=月（m.date）
和年（d.date）=年（m.date）
）作为dx;
但我收到错误：
< blockquote>
DSNT408I SQLCODE = -206，错误：M.DATE不是一个COLUMN OF
插入表，更新表或任何已标识的表
在条款中，或不是触发栏
表触发器
DSNT418I SQLSTATE = 42703 SQLSTATE返回码
有没有人有任何建议，最好如何做？
SELECT m.date as m1，
MIN（d.date）as m2，
MAX（d。日期）为m3
- COUNT（d.date）为NbOfDays - 如果需要...
从每月m
JOIN每日d ON月（d.date）=月（ m.date）和year（d.date）= year（m.date）
WHERE - 某些条件
GROUP BY m.date
ORDER BY m.date - 或somethin g else ...
请注意，这种类型的查询（如问题和迄今为止的回应相对较慢，因为它们表示扫描表，所以可以为每个[排序]行计算月（x）和年（x）。 （即将WHERE子句中的日期范围当然可以帮助）。如果这种类型的查询运行频繁，并且如果表的大小很大，那么对于这些计算值添加一列（或者可能是组合值（Year-Month）），我可能很有用所以不仅计算是不必要的，而且可以索引基础列。
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
but there may be days missing.
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
Here's the query I have:
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;
but I'm getting the error:
DSNT408I SQLCODE = -206, ERROR: M.DATE IS NOT A COLUMN OF
AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED
IN A FROM CLAUSE, OR IS NOT A COLUMN OF THE TRIGGERING
TABLE OF A TRIGGER
DSNT418I SQLSTATE = 42703 SQLSTATE RETURN CODE
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...
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中选择最小/最大日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
