时间戳

首页 > 解决方案 > SQL 从两个表中选择不同的记录

sql - SQL 从两个表中选择不同的记录

问题描述

我正在尝试编写一个 SQL 语句，该语句将根据最近的 SaleDate 从另一个表中返回一组不同的 CompanyNames，其中包含另一个表的指定日期范围。T01 = 帐户 T02 = TransHeader

重要的字段是：T01.ID、T01.CompanyName T02.AccountID、T02.SaleDate

T01.ID = T02.AccountID

我要返回的是每个 CompanyName 的 Max SaleDate，没有任何重复的 CompanyName，只有 Max(SaleDate) 作为 LastSale。我将使用 Where 子句来限制 SaleDate 范围。

我尝试了以下操作，但它返回了该范围内所有 SalesDates 的所有记录。这导致同一家公司被多次上市。

当前的 MS-SQL 查询

SELECT T01.CompanyName, T02.LastSale
FROM
    (SELECT DISTINCT ID, IsActive, ClassTypeID, CompanyName FROM Account) T01
    FULL OUTER JOIN 
    (SELECT DISTINCT AccountID, TransactionType, MAX(SaleDate) LastSale FROM TransHeader group by AccountID, TransactionType, SaleDate) T02
    ON T01.ID = T02.AccountID
WHERE ( ( T01.IsActive = 1 )AND 
        ( (Select Max(SaleDate)From TransHeader Where AccountID = T01.ID AND TransactionType in (1,6) AND SaleDate is NOT NULL) 
            BETWEEN '01/01/2016' AND '12/31/2018 23:59:00' AND (Select Max(SaleDate)From TransHeader Where AccountID = T01.ID AND TransactionType in (1,6) AND SaleDate is NOT NULL) IS NOT NULL
        ) 
      )
ORDER BY T01.CompanyName

我认为 FULL OUTER JOIN 是票，但它没有用，我被卡住了。

样本数据科目表(T01)

ID     CompanyName   IsActive   ClassTypeID
1      ABC123        1          1
2      CDE456        1          1
3      EFG789        1          1
4      Test123       0          1
5      Test456       1          1
6      Test789       0          1

示例数据转头表 (T02)

AccountID  TransactionType  SaleDate
1          1                02/03/2012
2          1                03/04/2013 
3          1                04/05/2014
4          1                05/06/2014
5          1                06/07/2014  
6          1                07/08/2015 
1          1                08/09/2016
1          1                01/15/2016
2          1                03/20/2017
2          1                03/21/2017 
3          1                03/04/2017
3          1                04/05/2018
3          1                05/27/2018
4          1                06/01/2018
5          1                07/08/2018 
5          1                08/01/2018
5          1                10/11/2018
6          1                11/30/2018

期望的结果

CompanyName     LastSale    (Notes note returned in the result)
ABC123          01/15/2016  (Max(SaleDate) LastSale for ID=1)
CDE456          03/21/2017  (Max(SaleDate) LastSale for ID=2)
EFG789          05/27/2018  (Max(SaleDate) LastSale for ID=3)
Testing456      10/11/2018  (Max(SaleDate) LastSale for ID=5)

ID=4 & ID=6 are note returned because IsActive = 0 for these records.

标签： sqlsql-serverdistinctmultiple-tablesfull-outer-join

解决方案

一种选择是在 select 子句中选择最大日期。

select
  a.*,
  (
    select max(th.saledate)
    from transheader th
    where th.accountid = a.id
    and th.saledate >= '2016-01-01'
    and th.saledate < '2019-01-01'
  ) as max_date
from account a
where a.isactive = 1
order by a.id;

如果您只想在给定日期范围内显示具有销售日期的交易标题，那么您可以将最大日期与帐户进行内部连接。为此，您必须对每个帐户的日期聚合进行分组：

select a.*, th.max_date
from account a
join
(
  select accountid, max(saledate) as max_date
  from transheader
  and saledate >= '2016-01-01'
  and saledate < '2019-01-01'
  group by accountid
) th on th.accountid = a.id
where a.isactive = 1
order by a.id;

推荐阅读