如果有多个条目，如何在SQL中获取记录。 [英] How do fetch record in SQL in case of multiple entries.

问题描述

在下面的查询中，对于一个PromotionId，我们有2或3个ApprovalDate记录，这样我就可以为单个PromotionId获得3行。我只需要一个PromotionId的一个ApprovalDate记录（最新日期）。



怎么做？



我尝试了什么：

In the query below, for one PromotionId we have 2 or 3 records for ApprovalDate and this way I am getting 3 rows for a single PromotionId. I want only one ApprovalDate record(the latestdate) for one PromotionId.

How to do this?

What I have tried:

select PrmMain.Id as PromotionId,
BpaMain.Id as CustomerId,
Convert (Date,PrmMain.datefrom )as StartDateOfPromotion,
Convert (date,PrmMain.DateInitiation )as InitiationDate,
convert(Date ,WfeRecentState.Done )as ApprovalDate,

Prmmain.Phase

from PrmMain 
inner join BpaMain 
on BpaMain.pkey=PrmMain.BpaMainPKey
inner join WfeRecentState
on PrmMain.PKey=WfeRecentState.ObjectPKey
where PrmMain.Status<>'d'
and BpaMain.status<>'d'
and prmmain.phase<>'deleted'
and PrmMain.TacticParentPKey=''
and PrmMain.SysPeriodPKey='00100000007sown5'

推荐答案

尝试将内部联接添加到

inner join (select PrmMain.Id as PromotionId,
MAX(WfeRecentState.Done) as MaxApprovalDate
from PrmMain 
inner join WfeRecentState on PrmMain.PKey=WfeRecentState.ObjectPKey
where PrmMain.Status<>'d'
and prmmain.phase<>'deleted'
and PrmMain.TacticParentPKey=''
and PrmMain.SysPeriodPKey='00100000007sown5' group by PrmMain.Id) as sq on sq.PromotionId = PrmMain.Id AND sq.MaxApprovalDate = WfeRecentState.Done

警告 - 这是未经测试的。



顺便说一下，我真的希望你做这些 Convert（Date 条目的唯一原因是因为基础列的类型为Date Time 而不是因为它们是文本

Caveat - this is untested.

By the way, I really hope that the only reason you are doing those Convert (Date entries are because the underlying columns are of type DateTime and not because they are text

这篇关于如果有多个条目，如何在SQL中获取记录。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！