SQL 如何根据表 1 中的日期从表 2 中选择最小/最大日期（无需从总和中获取太多数据）

How do I select min/max dates from table 2 based on date in table 1 (without getting too much data from sums)

提问by paxdiablo

Related to a question I asked earlier here, I've found a problem which is eluding me (obviously).

与我之前在这里提出的一个问题相关，我发现了一个我无法解决的问题（显然）。

The original question was how to select a min and max date from a daily table based on a monthly table where some daily table dates could be missing. Basically what I needed was columns containing the month date (always the first), the earliest date for that month in the daily table and the latest date for that month in the daily table.

最初的问题是如何根据可能缺少某些日常表日期的月表从日常表中选择最小和最大日期。基本上我需要的是包含月份日期（总是第一个）的列，日表中该月的最早日期和日表中该月的最晚日期。

So, if the last week of January and first week of February were missing from the daily table (and we otherwise had all the dates for January and February but no more), I needed:

因此，如果每日表中缺少 1 月的最后一周和 2 月的第一周（否则我们拥有 1 月和 2 月的所有日期，但仅此而已），我需要：

MonthStart  DayFirst    DayLast
----------  ----------  ----------
2009-01-01  2009-01-01  2009-01-24
2009-02-01  2009-02-08  2009-02-28

The answer was:

答案是：

select
    m.date as m1,
    min(d.date) as m2,
    max(d.date) as m3
from monthly m
join daily d
    on month(d.date) = month(m.date)
    and year(d.date) = year(m.date)
group by m.date
order by m.date

which worked for the specs I gave.

这适用于我给出的规格。

Unfortunately, reality bites, and there are multiple records in the monthly table (and daily table) with the same date. Specifically:

不幸的是，现实叮咬，月表（和日表）中有多条记录具有相同的日期。具体来说：

• the dates are 2007-10-16thru 2007-10-30(15 days), 2007-11-01thru 2007-11-30(30 days) and 2007-12-01thru 2007-12-15(15 days).
• each date has six rows in both tables (because they each have a row for three system names and two periods.

• 日期为2007-10-16至2007-10-30（15 天）、2007-11-01至2007-11-30（30 天）和2007-12-01至2007-12-15（15 天）。
• 每个日期在两个表中都有六行（因为它们每个都有一行用于三个系统名称和两个句点。

The problem is that I sum()a field in the monthly table and the new query is getting values that are much too large (compared to the previous query which did not have the join).

问题是我sum()在每月表中的一个字段和新查询正在获取太大的值（与没有连接的前一个查询相比）。

The aggregation changes the query to be:

聚合将查询更改为：

select
    m.date as m1,
    sum(m.other_field),  -- added this
    min(d.date) as m2,
    max(d.date) as m3
from monthly m
join daily d
    on month(d.date) = month(m.date)
    and year(d.date) = year(m.date)
group by m.date
order by m.date

I think the values are too high due to cross-joining going on since the figures for each month are out by a constant factor, depending on the number of days in the daily table for that month.

我认为由于交叉连接，这些值太高了，因为每个月的数字都是一个常数因子，具体取决于该月每日表中的天数。

My question is this: how do I aggregate the field in the monthly table without that factor coming into play andstill get the min/max dates from the daily table for that month?

我的问题是：如何在不考虑该因素的情况下聚合月表中的字段，并且仍然从该月的日表中获取最小/最大日期？

回答by Andomar

You can group the months in a subquery:

您可以在子查询中对月份进行分组：

select
    m.mindate as m1,
    m.sum_other_field,
    min(d.date) as m2,
    max(d.date) as m3
from (
    select 
         month(date) as month,
         year(date) as year,
         sum(other_field) sum_other_field,
         min(date) mindate
    from monthly
    group by month(date), year(date)
) m
join daily d
    on month(d.date) = m.month
    and year(d.date) = m.year
group by m.month
order by m.year

回答by manji

If the monthlytable contains a single entry for each month, you can do simply this:

如果该monthly表包含每个月的单个条目，您可以简单地执行以下操作：

select
    m.date as m1,
    m.other_field,
    min(d.date) as m2,
    max(d.date) as m3
from monthly m
join daily d
    on month(d.date) = month(m.date)
    and year(d.date) = year(m.date)
group by m.date, m.other_field
order by m.date

otherwise:

除此以外：

select m1, sum(other_field), m2, m3
from (
        select
        m.date as m1,
        m.other_field,
        min(d.date) as m2,
        max(d.date) as m3
    from monthly m
    join daily d
        on month(d.date) = month(m.date)
        and year(d.date) = year(m.date)
    group by m.date, m.other_field) A
group by A.m1, A.m2, A.m3
order by A.m1

Update from pax:Try as I might, I could not get the joinsolutions working properly - they all seemed to return the same wrong data as the original. In the end, I opted for a non-joinsolution since it worked and performance wasn't a big issue, since the tables typically have 24 rows (for monthly) and 700 rows (for daily). I'm editing this answer and accepting it since (1) it actually helped a great deal in getting the correct solution for me; and (2) I'm loathe to write my own answer and claim the glory for myself.

来自 pax 的更新：join尽我所能，我无法使解决方案正常工作 - 它们似乎都返回与原始数据相同的错误数据。最后，我选择了非join解决方案，因为它有效并且性能不是大问题，因为表通常有 24 行（每月）和 700 行（每天）。我正在编辑这个答案并接受它，因为（1）它实际上对我获得正确的解决方案有很大帮助；(2) 我不愿意写我自己的答案并为自己争取荣耀。

Thanks for all your help. The following is what worked for me:

感谢你的帮助。以下是对我有用的内容：

select
    m.date as p1,
    m.grouping_field as p2,
    sum(m.aggregating_field) as p3,
    (select min(date) from daily
        where month(date) = month(m.date)
        and year(date) = year(m.date)) as p4,
    (select max(date) from daily
        where month(date) = month(m.date)
        and year(date) = year(m.date)) as p5
from
    monthly m
group by
    m.date, m.grouping_field

which gave me what I wanted:

这给了我我想要的：

    P1       P2    P3       P4         P5
----------  ----  ----  ----------  ----------
2007-10-01  BoxA  12.3  2007-10-16  2007-10-30
2007-10-01  BoxB  13.6  2007-10-16  2007-10-30
2007-10-01  BoxC   7.4  2007-10-16  2007-10-30
2007-11-01  BoxA  20.3  2007-11-01  2007-11-30
2007-11-01  BoxB  24.2  2007-11-01  2007-11-30
2007-11-01  BoxC  21.7  2007-11-01  2007-11-30
2007-12-01  BoxA   6.9  2007-12-01  2007-12-15
2007-12-01  BoxB   6.4  2007-12-01  2007-12-15
2007-12-01  BoxC   6.9  2007-12-01  2007-12-15