使用聚合功能根据MIN时间戳过滤记录 [英] Using aggregation function to filter record based on MIN timestamp

问题描述

SELECT * FROM ABC_CUSTOMER_DETAILS abc_detail
INNER JOIN ABC_CUSTOMERS abc_cust
ON abc_detail.ID=abc_cust.CUSTOMER_ID
WHERE abc_detail.COUNTRY_CODE='KE'
AND CREATION_TIMESTAMP=(SELECT MIN (CREATION_TIMESTAMP)
                        FROM ABC_CUSTOMER_DETAILS abc_detail
                        INNER JOIN ABC_CUSTOMERS abc_cust
                        ON abc_detail.ID=abc_cust.CUSTOMER_ID
                        WHERE abc_detail.COUNTRY_CODE='KE');

从ABC_CUSTOMER_DETAILS到ABC_CUSTOMERS的脚本查询联接记录上方，并从最早的时间戳中选择一个.

Above script query join record from ABC_CUSTOMER_DETAILS to ABC_CUSTOMERS nd select thw one with earliest timestamp.

是否可以在CREATION_TIMESTAMP条件下重复相同的JOIN和WHERE子句?

Anyway if I able not to repeat the same JOIN and WHERE clause in CREATION_TIMESTAMP condition?

推荐答案

有多种方法可以获取最早的记录并避免两次键入相同的条件.

There are several ways to get the earliest record and to avoid having to type the same criteria twice.

使用FETCH FIRST ROWS(自Oracle 12c起可用)

select * 
from abc_customer_details cd
join abc_customers c on c.id = cd.customer_id
where cd.country_code = 'KE'
order by creation_timestamp
fetch first row only;

使用CTE(WITH子句)

with cte as
(
  select * 
  from abc_customer_details cd
  join abc_customers c on c.id = cd.customer_id
  where cd.country_code = 'KE'
)
select *
from cte
where (creation_timestamp) = (select min(creation_timestamp) from cte);

使用窗口功能

select *
from
(
  select cd.*, c.*, min(creation_timestamp) over () as min_creation_timestamp
  from abc_customer_details cd
  join abc_customers c on c.id = cd.customer_id
  where cd.country_code = 'KE'
)
where creation_timestamp = min_creation_timestamp;

(顺便说一下，我在所有这些查询中都更改了连接条件.您似乎不太可能希望在abc_customer_details.id = abc_customers.customer_id上进行连接.)

(I changed the join criteria in all these queries, by the way. It just does seem extremely unlikely you want to join on abc_customer_details.id = abc_customers.customer_id.)

这篇关于使用聚合功能根据MIN时间戳过滤记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！