带有连接的SQL查询,但只想要连接表中的最新记录
我有一个包含引擎(EngineId,Make,Model,SerialNumber等)的表的数据库和引擎上具有Test值的表(TestId,EngineId,值1,值2等)。带有连接的SQL查询,但只想要连接表中的最新记录
我想从引擎表中选择所有记录以及最新的测试值(可能有多个测试记录,但我只想要最新的)。这看起来很简单,但由于某种原因,我无法完全掌握SQL语法。
我可以从类似下面的东西开始,但然后我卡住了。
SELECT e.*, t.Value1, t.Value2
FROM Engine e
left join Event ev on e.EngineID = ev.EngineID
left join Test t on ev.EventID = t.EventID
WHERE ev.EventType = 'TEST'
我可以关闭以下内容,但它只返回带有测试数据的记录。我想没有测试数据返回引擎记录:
SELECT e.*, t.Nox, t.CO
FROM Engine e
left join Event ev on e.EngineID = ev.EngineID
left join Test t on ev.EventID = t.EventID
left join EventType et on ev.EventTypeId = et.EventTypeID
WHERE Upper(et.Name) = 'TEST'
AND ev.EventDate= (SELECT MAX(EventDate)
FROM Event ev2
left join EventType et2 on ev2.EventTypeID = et2.EventTypeID
WHERE ev2.EngineID = e.EngineID AND
Upper(et2.Name) = 'TEST')
我可以亲近下面,但我仍然需要加入以某种方式测试表:
SELECT DISTINCT e.*, om.maxdate
FROM Engine e
LEFT JOIN (
select ev2.EngineID, max(EventDate) as maxdate
from Event ev2
LEFT JOIN EventType et on ev2.EventTypeID = et.EventTypeID
WHERE Upper(et.Name) = 'TEST'
group by EngineID
) om on e.EngineID = om.EngineID
--LEFT JOIN Test t on om.EventID = t.EventID
我可以做,如果一个存储过程帮助。
你问了一些示例数据,所以在这里。我想要返回所有的引擎记录以及最新的测试值。
引擎
EngineId Make Model SerialNumber
1 Waukesha 5794 C-62124
2 Waukesha 5800 F-199374
3 NULL NULL B-200305
4 Waukesha 6000 D-999777
5 Waukesha 6100 E-877324
6 Briggs & Stratton LW300 SL-122375
7 Briggs & Stratton LW350 SL-375200
8 Briggs & Stratton GS250 LS-988755
9 Briggs & Stratton 5794 SN12345
测试
TestID EventID EngineID TestDate Description Value1 Value2
1 7 1 03/01/17 12:00 AM Testing 30 50
9 8 1 01/01/17 12:00 AM Testing 20 20
17 11 1 12/01/16 12:00 AM Testing 16 15
可以使用
SELECT TOP 1 e. *,t.Value1,t.Value2
让高层记录,以确保顶部是最新的,责令表
ORDER BY column1, column2, ... ASC|DESC
希望这有助于....
如果你意味着对给定的发动机最新的测试活动(基于EVENTDATE),那么它就是:
SELECT e.*, t.Value1, t.Value2
FROM Engine e
LEFT JOIN Event ev
ON e.EngineID = ev.EngineID
AND ev.EventType = 'TEST'
AND ev.EventDate = (SELECT MAX(ev2.EventDate)
FROM Event ev2
WHERE ev2.EngineID = e.EngineID
AND ev2.EventType = 'TEST')
LEFT JOIN Test t
ON ev.EventID = t.EventID
请注意,如果最新的事件有更多的测试记录,所有这些都将被返回,因为它们都共享最新的EventDate。但是这似乎是正确的,因为你没有区分分配给同一事件的多个测试记录,所以它们是平等的。
我需要返回没有测试数据的引擎记录。 – codecypher
@codecypher - 当然,从'Engine'到'Event'的'LEFT JOIN'确保了这一点。 – miroxlav
我认为发生的事情是WHERE子句缩小范围,并且不返回没有测试事件的引擎记录。 – codecypher
当你说你想要最新的测试值时,这意味着你将有某种方法来识别最新的测试值。这可以通过检索最新的eventdate来完成。
您将不得不在查询中使用子查询。
SELECT e.*, t.Value1, t.Value2
FROM Engine e
left join Event ev on e.EngineID = ev.EngineID
left join Test t on ev.EventID = t.EventID
WHERE ev.EventType = 'TEST'
AND ev.EventDate= (SELECT MAX(EventDate)
FROM Event
WHERE Event.EventID = t.EventId
AND Event.EngineID = ev.EngineId)
这应该给你最新的测试结果为事件在发动机
可以使用rank()
功能只得到每个引擎的最后一次测试。
;with Tcte as (
SELECT e.*
, t.Value1
, t.Value2
, rank() over (partition by e.EngineID order by t.TestDate desc) r
FROM Engine e
left join Event ev on e.EngineID = ev.EngineID
left join Test t on ev.EventID = t.EventID WHERE ev.EventType = 'TEST')
SELECT * FROM Tcte WHERE r=1
试试这个
;with testValues as
(select ev.EngineID, ev.EventId ,t.TestId,t.Value1, t.Value2
,row_number() over(partition by ev.EventId order by ev.EventDate desc)rowNumber
from Event ev
left join Test t on ev.EventID = t.EventID
WHERE ev.EventType = 'TEST'
)
SELECT e.*, v.Value1, v.Value2
FROM Engine e
left join testValues v on e.EngineID = v.EngineID and v.rowNumber=1
或者这
;with testData as
(
SELECT e.*, t.Value1, t.Value2
,row_number() over(partition by ev.EventId order by ev.EventDate desc)rowNumber
FROM Engine e
left join Event ev on e.EngineID = ev.EngineID and ev.EventType = 'TEST'
left join Test t on ev.EventID = t.EventID
)
select * from testData where rowNumber=1
感谢您的建议。我只想返回一个测试记录,即使他们没有测试记录,我也想返回所有引擎记录。它显示你的代码返回所有的测试记录,并且只返回包含测试数据的引擎记录 – codecypher
尝试第一个查询,即使它们没有测试记录,它也会返回所有引擎记录 –
另外,我还使用连接本身的过滤条件编辑了第二个查询(左连接e.EngineID = ev的事件ev .EngineID和ev.EventType ='TEST') –
更新您的问题添加适当的数据样本和预期的结果 – scaisEdge
请与您的数据样本和预期结果提供为好。 –