如何分组，并从两个表中选择，每个给定的id需要两条记录 [英] how to group by, and select from two tables, need two records for each given id

问题描述

我是SQL查询的新手。
您能否帮我解决以下问题？

表1 QuoteObservations：

  id值quotePointId asOfTime 
  

表2 QuotePoints：

  id，quotoType 
  

quoteType可能是LastPrice，RepoRate等。

我需要选择id和value，asOfTime，quoteType，最高的asOfTime从表1中用quoteType = LastPrice（= 1）或RepoRate （= 2）在表2中;我需要返回两条记录，一条用于最后价格，一条用于每个ID的回购利率，但最终价格和回购利率应该是最高的asOfTime。

我有这个，但它给出了最后的价格或回购利率，具有较高的asOfTime。

  SELECT QuoteObservations.id，QuoteObservations.value ，
 QuoteObservations.quotePointId，max（QuoteObservations.asOfTime）asOfTime，
 QuoteObservations.dataProviderId，QuotePoints.quoteType 
从QuoteObservations，QuotePoints 
 WHERE（QuoteObservations.id = 1 OR QuoteObservations .id = 2）
 AND QuoteObservations.quotePointId = QuotePoints.id 
 AND（QuotePoints.quoteType = 1或QuotePoints.quoteType = 2）
由QuoteObservations.id，QuoteObservations.value，
 QuoteObservations.quotePointId，QuoteObservations.dataProviderId，QuotePoints.quoteType; 
  

解决方案

我猜这个查询有用，但我敢打赌有一个更好，更有效的方式来做到这一点，任何人都可以帮助吗？

  select q。* 
 from（
 select QuoteObservations.id，QuoteObservations.value，QuoteObservations.quotePointId ，max（QuoteObservations.asOfTime）as asOfTime，QuoteObservations.dataProviderId，qp.quoteType $ b $ from [QuoteObservations] 
内连接报价点qp 
上qp.id = QuoteObservations.quotePointId 
其中quotePointId = 1 
通过QuoteObservations.id，QuoteObservations.value，QuoteObservations.quotePointId，QuoteObservations.dataProviderId 
）q 
内部连接（
 
）选择QuoteObservations.id， QuoteObservations.value，QuoteObservations.quotePointId，max（QuoteObservations.asOfTime）asOfTime，QuoteObservations.dataProviderId，qp.quoteType $ b $ from [QuoteObservations] 
内部连接报价点qp 
对qp.id = QuoteObservations .quotePointId 
其中quotePointId = 2 
 group by QuoteObservations.id，QuoteObservations.value，QuoteObservations.quotePointId，QuoteObservations.dataProviderId 
）p 
 on q.id = p.id 
内连接（
 select QuoteObservations.id ，QuoteObservations.value，QuoteObservations.quotePointId，max（QuoteObservations.asOfTime）asOfTime，QuoteObservations.dataProviderId，qp.quoteType $ b $ from [QuoteObservations] 
内连接报价点qp 
上qp.id = QuoteObservations.quotePointId 
其中quotePointId = 10 
组由QuoteObservations.id，QuoteObservations.value，QuoteObservations.quotePointId，QuoteObservations.dataProviderId 
）s 
上s.id = p.id 
  

I am new to SQL query. Can you please help me with the following?

table 1 QuoteObservations:

id value quotePointId asOfTime 

table 2 QuotePoints:

id, quotoType

quoteType could be LastPrice, RepoRate, etc

I need to select id and value, asOfTime, quoteType, with highest asOfTime from table 1 with quoteType=LastPrice ( =1) or RepoRate (=2) in table 2; I need to return two records, one for last price, one for repo rate for each id, but both last price and repo rate should be with the highest asOfTime.

I have this, but it gives either last price or repo rate whichever has the higher asOfTime.

SELECT QuoteObservations.id, QuoteObservations.value, 
QuoteObservations.quotePointId, max(QuoteObservations.asOfTime) as asOfTime, 
QuoteObservations.dataProviderId, QuotePoints.quoteType 
FROM QuoteObservations, QuotePoints 
WHERE (QuoteObservations.id =1 OR QuoteObservations.id = 2 ) 
AND QuoteObservations.quotePointId = QuotePoints.id 
AND (QuotePoints.quoteType = 1 or QuotePoints.quoteType = 2)
group by QuoteObservations.id, QuoteObservations.value, 
QuoteObservations.quotePointId,QuoteObservations.dataProviderId, QuotePoints.quoteType;

解决方案

I craeted this query, it works, but I bet there is a better and more efficient way to do it, can anyone out there help?

select q.*
from (
            select     QuoteObservations.id, QuoteObservations.value,   QuoteObservations.quotePointId, max(QuoteObservations.asOfTime) as asOfTime, QuoteObservations.dataProviderId, qp.quoteType
            from [QuoteObservations]
            inner join QuotePoints qp
            on qp.id = QuoteObservations.quotePointId
            where quotePointId = 1
            group by QuoteObservations.id, QuoteObservations.value, QuoteObservations.quotePointId, QuoteObservations.dataProviderId
    ) q
inner join (

            select     QuoteObservations.id, QuoteObservations.value, QuoteObservations.quotePointId, max(QuoteObservations.asOfTime) as asOfTime, QuoteObservations.dataProviderId, qp.quoteType
            from [QuoteObservations]
            inner join QuotePoints qp
            on qp.id = QuoteObservations.quotePointId
            where quotePointId = 2
            group by QuoteObservations.id, QuoteObservations.value, QuoteObservations.quotePointId, QuoteObservations.dataProviderId
) p
    on  q.id = p.id
inner join (
            select     QuoteObservations.id, QuoteObservations.value, QuoteObservations.quotePointId, max(QuoteObservations.asOfTime) as asOfTime, QuoteObservations.dataProviderId, qp.quoteType
            from [QuoteObservations]
            inner join QuotePoints qp
            on qp.id = QuoteObservations.quotePointId
            where quotePointId = 10
            group by QuoteObservations.id, QuoteObservations.value, QuoteObservations.quotePointId, QuoteObservations.dataProviderId
) s
    on  s.id = p.id

这篇关于如何分组，并从两个表中选择，每个给定的id需要两条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！