sql - 如何在TypeORM中编写子查询加入、排序并返回最新结果

Question

我想加入两个表，对它们进行排序并返回最新使用返回的加入表的修改日期。我已经在 SQL 服务器上成功编写了 SQL 查询，它返回了所需的结果。但是我需要编写这个查询来使用 TypeORM。我如何在 TypeORM 中编写这个。

这是分解。我创建了两个虚拟表，Assets 和 LotBids 表，就像这样

(assetsInSaleEventId varchar(50), 
assetNumber varchar(100), 
lotNumber varchar(100), 
saleEventId varchar(50), 
PRIMARY KEY (assetsInSaleEventId)
)
insert into @Assets values
    --First Sale Event (ID = '1B68BD21-238E-4B00-B7F2-FABC0C2690AA')
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CA', 'ab101', '1', '1B68BD21-238E-4B00-B7F2-FABC0C2690AA'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CB', 'ab102', '2', '1B68BD21-238E-4B00-B7F2-FABC0C2690AA'),
    --Second Sale Event (ID = '1B68BD21-238E-4B00-B7F2-FABC0C2690AB')
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CE', 'ab105', '5', '1B68BD21-238E-4B00-B7F2-FABC0C2690AB'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CF', 'ab106', '6', '1B68BD21-238E-4B00-B7F2-FABC0C2690AB'),
    -- Third Sale Event (ID = '1B68BD21-238E-4B00-B7F2-FABC0C2690AC')
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CI', 'ab109', '9', '1B68BD21-238E-4B00-B7F2-FABC0C2690AC'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CJ', 'ab110', '10', '1B68BD21-238E-4B00-B7F2-FABC0C2690AC'),
    --Fourth Sale Event (ID = '1B68BD21-238E-4B00-B7F2-FABC0C2690AD')
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CM', 'ab113', '13', '1B68BD21-238E-4B00-B7F2-FABC0C2690AD'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CO', 'ab114', '14', '1B68BD21-238E-4B00-B7F2-FABC0C2690AD'),
    --Fifth Sale Event (ID = '1B68BD21-238E-4B00-B7F2-FABC0C2690AE')
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CR', 'ab117', '17', '1B68BD21-238E-4B00-B7F2-FABC0C2690AE'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690CS', 'ab118', '18', '1B68BD21-238E-4B00-B7F2-FABC0C2690AE')
declare @Lots table 
(lotBidsId varchar(50), 
assetsInSaleEventId varchar(50), 
clientNumber varchar(200), 
clientName varchar(100),
auditModifiedDatetime datetime,
primary key (lotBidsId)
)
insert into @Lots values
    -- Lot Bids on Asset '1B68BD21-238E-4B00-B7F2-FABC0C2690CA', for first Sale Event
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LA', '1B68BD21-238E-4B00-B7F2-FABC0C2690CA', 'ray100', 'Raymond', '2021-06-21 15:10:15'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LB', '1B68BD21-238E-4B00-B7F2-FABC0C2690CA', 'ray100', 'Raymond', '2021-06-23 15:10:15'),
    -- Lot Bids on Asset '1B68BD21-238E-4B00-B7F2-FABC0C2690CB', for first Sale Event
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LD', '1B68BD21-238E-4B00-B7F2-FABC0C2690CB', 'ray100', 'Raymond', '2021-06-21 16:10:15'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LE', '1B68BD21-238E-4B00-B7F2-FABC0C2690CB', 'ara100', 'Arash', '2021-06-23 16:10:15'),
    -- Lot Bids on Asset '1B68BD21-238E-4B00-B7F2-FABC0C2690CC', for first Sale Event
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LG', '1B68BD21-238E-4B00-B7F2-FABC0C2690CC', 'ara100', 'Arash', '2021-06-21 17:10:15'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LH', '1B68BD21-238E-4B00-B7F2-FABC0C2690CC', 'ray100', 'Raymond', '2021-06-23 17:10:15'),
    -- Lot Bids on Asset '1B68BD21-238E-4B00-B7F2-FABC0C2690CD', for first Sale Event
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LJ', '1B68BD21-238E-4B00-B7F2-FABC0C2690CD', 'franc100', 'Francis', '2021-06-21 18:10:15'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LK', '1B68BD21-238E-4B00-B7F2-FABC0C2690CD', 'ara100', 'Arash', '2021-06-23 18:10:15'),
    -- Lot Bids on Asset '1B68BD21-238E-4B00-B7F2-FABC0C2690CE', for second Sale Event
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LN', '1B68BD21-238E-4B00-B7F2-FABC0C2690CE', 'franc100', 'Francis', '2021-07-12 18:10:15'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LO', '1B68BD21-238E-4B00-B7F2-FABC0C2690CE', 'ray100', 'Arash', '2021-07-15 18:10:15'),
    -- Lot Bids on Asset '1B68BD21-238E-4B00-B7F2-FABC0C2690CF', for second Sale Event
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LR', '1B68BD21-238E-4B00-B7F2-FABC0C2690CF', 'franc100', 'Francis', '2021-07-12 19:10:15'),
    -- Lot Bids on Asset '1B68BD21-238E-4B00-B7F2-FABC0C2690CG', for second Sale Event
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LU', '1B68BD21-238E-4B00-B7F2-FABC0C2690CG', 'franc100', 'Francis', '2021-07-12 20:10:15'),
    ('1B68BD21-238E-4B00-B7F2-FABC0C2690LV', '1B68BD21-238E-4B00-B7F2-FABC0C2690CG', 'ray100', 'Arash', '2021-07-15 20:10:15')

select a.assetNumber, l.assetsInSaleEventId, l.lastModified, l.clientNumber, l.clientName
from @Assets a 
inner join 
(
    select 
      l.assetsInSaleEventId,
      l.auditModifiedDatetime as lastModified,
      l.clientNumber,
      l.clientName
      from @Lots l 
      where l.auditModifiedDatetime = (select max(l2.auditModifiedDatetime)
      where l2.assetsInSaleEventId = l.assetsInSaleEventId 
      )
   ) l
   on a.assetsInSaleEventId  = l.assetsInSaleEventId
where a.saleEventId = '1B68BD21-238E-4B00-B7F2-FABC0C2690AA' 

以上是我的 SQL 查询，用于连接、排序和返回每个排序结果的最新值。如何在 typeORM 中编写查询？

标签： sqlsql-serverdatabasesubquerytypeorm