时间戳

首页 > 解决方案 > 使用左连接选择我们需要列的最旧值的行

sql - 使用左连接选择我们需要列的最旧值的行

问题描述

想象以下场景，我们有两个表。一张表存储我们的玩家及其状态、创建日期和 ID，另一张表保存他们的登录活动。

我想检索所有登录超过N年或从未登录过的玩家。如您所见，我们有一个 id = 4 的玩家从未登录过游戏，所以我们也想将他包括在我们的列表中通过检查他的创作日期。

也可以在活动表中为玩家提供多个条目，因此我们的兴趣是只为该玩家带来具有 [max(LoginDate) < N ] 的行。

我已经有了解决方案，但我渴望看到不同的方法。

PLAYER_TABLE
--------------------------------------
|PlayerId |CreationDate | Status     |
--------------------------------------
|1        |01.01.2000     | ACTIVE   |
--------------------------------------
|2        |01.01.2019     | ACTIVE   |          
--------------------------------------
|3        |01.01.2001     | SUSPENDED|          
--------------------------------------
|4        |01.01.2004     | ACTIVE   |          
--------------------------------------

ACTIVITY_TABLE
-----------------------
|AccountId |LoginDate  |
-----------------------
|1        |01.01.2005 |
-----------------------
|1        |05.06.2007 |          
-----------------------
|2        |03.05.2010 |         
-----------------------
|3        |01.01.2018 |         
-----------------------

标签： sqloracleleft-joinoracle-sqldevelopercommon-table-expression

解决方案

使用 CTE（公用表表达式）来实现分析并让我们基于单个日期进行过滤：

WITH CTE AS(
SELECT player.PlayerId
     , activity.ddt 
     , coalesce(max(Activity.Logindate) over (partition by Activity.AccountID), player.CreationDate) as  LastLoginOrCreation
FROM PLAYER_TABLE player
LEFT JOIN  ACTIVITY_TABLE activity
  ON activity.AccountId = player.PlayerId)

SELECT * 
FROM CTE 
WHERE player.status != 'SUSPENDED'
  and LastLoginOrCreation <= to_date('2015-01-01', 'yyyy-MM-dd')

避免 CTE 消除了内联选择，但仍然必须过滤两个日期。

SELECT player.PlayerId
     , activity.ddt 
     , coalesce(max(Activity.Logindate) over (partition by Activity.AccountID), player.CreationDate) as  LastLoginOrCreation
FROM PLAYER_TABLE player
LEFT JOIN  ACTIVITY_TABLE activity
  ON activity.AccountId = player.PlayerId
WHERE (player.CREATION_DATE <= to_date('2015-01-01', 'yyyy-MM-dd') 
   OR activity.LoginDate <= to_date('2015-01-01', 'yyyy-MM-dd'))
  AND player.status != 'SUSPENDED';

推荐阅读