sql - 处理第一条记录的子查询的最有效方法

Question

我有一个客户 ID 表和一个客户数据表。客户可以更改 ID，但在某些情况下我想查询他们的最早日期，而在其他情况下我想查询最新日期。例如，我有两个表:

tbl_a
id        active_id
a         aa
b         aa
c         aa
d         bb
e         bb

tbl_b
id         date      city
a        1/1/2012    Dallas
b        1/1/2013    Houston
c        2/1/2013    Austin
d        1/1/2003    San Antonio
e        3/3/2013    El Paso

tbl_c
id    value
a      50
b      50
b      50
c      50
d      10
e      10
e      10

在对来自 tbl_c 的数据求和时，如何查询数据以返回具有 EARLIEST 日期和 MOST RECENT 城市的单个 active_id？例如，返回以下内容:

active_id    date      city     sum(value)
aa           1/1/2012  Austin   200 
bb           1/1/2003  El Paso  30

在语义方面，我试图找到任何 active_id 的第一个日期和最近的城市，同时对交易值求和。

Answer 1

如果您可以使用窗口函数(并且无权访问 first_value 函数):

with cte as (
    select
        row_number() over(partition by a.active_id order by b.date asc) as rn1,
        row_number() over(partition by a.active_id order by b.date desc) as rn2,
        a.active_id, b.date, b.city
    from tbl_a as a
        inner join tbl_b as b on b.id = a.id
)
select
    c.active_id,
    c1.date,
    c2.city
from (select distinct active_id from cte) as c
    left outer join cte as c1 on c1.active_id = c.active_id and c1.rn1 = 1
    left outer join cte as c2 on c2.active_id = c.active_id and c2.rn2 = 1

sql fiddle demo

如果您可以使用 first_value功能:

with cte as (
    select
        first_value(b.date) over(partition by a.active_id order by b.date asc) as date,
        first_value(b.city) over(partition by a.active_id order by b.date desc) as city,
        a.active_id
    from tbl_a as a
        inner join tbl_b as b on b.id = a.id
)
select distinct
    active_id, date, city
from cte