编写Sql脚本，以一对多的方式获取数据

Question

我想写一个sql脚本从两个表中获取数据一个是父表，第二个是子表

出价

BidId | Code | Date

1 | H | 2022-05-12
2 | B | 2022-05-13

BidResult

BidResultId | BidId | Emi

12 | 1 | 50

13 | 1 | 20

14 | 2 | 30

Bid and BidResult

我想从投标表中获取列日期，代码，BidId和BidResultId，从BidResult的EMI，但想要最小EMI的另一个列

从上面的例子中，我想获取bidId 1

代码H，日期2022-05-12,BidResultId 12, Emi 50和最小Emi在50和20之间，即20

所以我写了下面的查询

Select B.BidId,BR.EMI As Installment,
(Select Min(BR.EMI ) from BidResult BR
Inner Join Bid B on B.BidId = BR.BidResultId
where B.BidId = 5) As MinInstallment,
B.Code,
BR.BidResultId,
CONVERT(DATE, B.Date) As BidDate
from Bid B
Inner Join BidResult BR On B.BidId = BR.BidId
where B.BidId= 5 and B.TypeId = 1

所有字段都是直接的，除了BidId的最小EMI值

请检查查询并建议是否有任何修改

Answer 1

解决方案1:使用group by

select BID, min(EMI) EMI
into #temp
from BIDResult
group by BID
select b.Date, b.Code, b.BidId, br.BidResultId, br.EMI 
from BID b
inner join #temp on t.BID = b.BID
inner join BIDResult br on br.BID = t.BID and br.EMI = t.EMI

方案2:没有group by

select *, ROW_Number() over(PARTITION by BID order by EMI) RowNumber
into #temp
from BIDResult
select b.Date, b.Code, b.BidId, br.BidResultId, br.EMI  
from BID b
inner join #temp t on t.BID = b.BID and t.RowNumber = 1