代码之家 › 专栏 › 技术社区 › paxdiablo

如何根据表1中的日期从表2中选择最小/最大日期(而不从汇总中获取太多数据)

db2 select database sql

paxdiablo · 技术社区 · 15 年前

与我之前问的问题有关 here 我发现了一个很明显的问题。

最初的问题是如何从日表中根据月表选择最小和最大日期,其中可能缺少一些日表日期。基本上我需要的是包含月日期(总是第一个)、日表中该月的最早日期和日表中该月的最晚日期的列。

因此,如果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

答案是:

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

这对我提供的规格很有用。

不幸的是,现实咬人,并且在同一日期的月表(和日报表)中有多个记录。明确地:

日期是 2007-10-16 通过 2007-10-30 (15天) 2007-11-01 通过 2007-11-30 (30天) 2007-12-01 通过 2007-12-15 (15天)。
两个表中的每个日期都有六行(因为每个日期都有一行用于三个系统名称和两个句点)。

问题是我 sum() 月表和新查询中的一个字段获取的值太大(与上一个没有联接的查询相比)。

聚合将查询更改为:

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

我认为这些值太高是因为交叉连接正在进行,因为每个月的数字是由一个常量因子输出的,这取决于该月的日表中的天数。

我的问题是:在不考虑这个因素的情况下,如何在月表中汇总字段? 和仍然从该月的日表中获取最小/最大日期?

2 回复 | 直到 15 年前

paxdiablo 15 年前

如果 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

否则:

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

从pax更新: 尽我所能,我无法得到 join 解决方案工作正常-它们似乎都返回了与原始数据相同的错误数据。最后,我选择了非- 参加 解决方案,因为它工作并且性能不是一个大问题,因为表通常有24行(每月)和700行(每天)。我正在编辑这个答案并接受它,因为(1)它实际上帮助我获得了正确的解决方案;(2)我不愿意写自己的答案并为自己赢得荣誉。

谢谢你的帮助。以下是对我有用的:

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

这给了我我想要的:

    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

Andomar 15 年前

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

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