我正在写一个从3个表中获取一些数据的存储过程。现在,我的输出如下所示:
钻机20被列出两次。我只想获取具有最新日期戳的记录。所以我的查询现在看起来像这样:
SELECT
robinson_Rigs.rigId
, robinson_Rigs.rigName
, robinson_Clients.companyName
, robinson_Wells.wellName
, robinson_Wells.county
, max(robinson_Wells.startDate)
, robinson_Wells.directions
FROM robinson_Wells
JOIN robinson_Rigs ON robinson_wells.rigId = robinson_Rigs.rigId
JOIN robinson_Clients on robinson_Wells.clientId = robinson_Clients.clientId
group by robinson_Rigs.rigId
ORDER BY robinson_Rigs.rigId
但我收到此错误:
Msg 8120, Level 16, State 1, Procedure robinson_GetAllDrivingDirections, Line 14
Column 'robinson_Rigs.rigName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
我怎样才能做到这一点?
如果任何其他字段包含该装备的其他值,则仅对除StartDate以外的所有内容进行简单分组仍然会返回一个装备的多行。
而是尝试这样的事情:
SELECT
robinson_Rigs.rigId
, robinson_Rigs.rigName
, robinson_Clients.companyName
, robinson_Wells.wellName
, robinson_Wells.county
, robinson_Wells.startDate
, robinson_Wells.directions
FROM robinson_Wells
JOIN robinson_Rigs ON robinson_wells.rigId = robinson_Rigs.rigId
JOIN
(
SELECT rigId
, MAX(startDate) AS MostRecentDate
FROM robinson_rigs
GROUP BY rigId
) latestRigDate ON robinson_Rigs.RigId = latestRigDate.RigId
AND robinson_rigs.StartDate = latestRigDate.MostRecentDate
JOIN robinson_Clients on robinson_Wells.clientId = robinson_Clients.clientId
ORDER BY robinson_Rigs.rigId
加入的子查询将返回所有装备ID及其最大(最新)日期的列表。通过rigId和startDate将其连接到完整的robinson_rigs表将“过滤”该表,以便仅返回每个装备的最新日期的记录。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句