使用CROSS APPLY [英] Using CROSS APPLY

问题描述

我有一个表Car，其中有汽车ID(smallint)，还有另一个表，其中包含与每辆汽车相关的事件.

I have table Car with car ID's (smallint), and another table with events related with each car.

现在，我想获取按特定条件选择的汽车的最新赛事，但这似乎不起作用.

Now I want to get latest event for cars selected by certain criteria, but this does not seem to work.

当我这样查询以获取每辆汽车的最新事件时，它运行正常:

When I have query like this to get the latest event for every car it works ok:

SELECT * FROM [dvm_data].[dbo].[Car] CD 
CROSS APPLY (
  SELECT TOP 1 * FROM [dvm_data].[dbo].[CarData] WHERE CarIndex = CD.ID) MD

另一方面，当我尝试在第一个SELECT中使用WHERE限制汽车时，它不再起作用:

On the other hand, when I try to limit cars using WHERE in first SELECT, it no longer works:

SELECT * FROM [dvm_data].[dbo].[Car] WHERE ID > 100 CD 
CROSS APPLY (
  SELECT TOP 1 * FROM [dvm_data].[dbo].[CarData] WHERE CarIndex = CD.ID) MD

在这种情况下，我会收到错误消息:

In this case I get error message:

第15级，州1，第1行的消息102 "CD"附近的语法不正确. Msg 102，第15级，状态1，第3行 "MD"附近的语法不正确.

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'CD'. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'MD'.

第一个查询本身可以正常工作:

The first query in itself works fine:

SELECT * FROM [dvm_data].[dbo].[Car] WHERE ID > 100

我在这里想念的是什么?通常，使用CROSS APPLY的示例在第一个SELECT查询中没有WHERE，这意味着什么吗?

What I am missing here? Usually examples using CROSS APPLY do not have WHERE in the first SELECT query, does this mean something?

另一件事，如果我想在第一个查询中使用DISTINCT来查找具有特定事件类型的汽车，然后为这些汽车查找最新事件该怎么办.可能是这样，但这也行不通:

Another thing, what if I want to use DISTINCT in first query, to find cars that had certain type of event, and then finding latest event for these cars. It would be something like this, but also this does not work:

SELECT DISTINCT ID FROM [dvm_data].[dbo].[CarData] WHERE EventID = 32 CD 
CROSS APPLY (
  SELECT TOP 1 * FROM [dvm_data].[dbo].[CarData] WHERE CarIndex = CD.ID) MD

我想这应该很容易，但是目前我缺少一些东西...

I suppose this should be very easy, but currently I am missing something...

推荐答案

您的where子句在错误的位置！请尝试以下方法:

your where clause is on the wrong position! try this instead:

SELECT * FROM [dvm_data].[dbo].[Car] CD 
CROSS APPLY (
    SELECT TOP 1 * 
    FROM [dvm_data].[dbo].[CarData] 
    WHERE CarIndex = CD.ID
) MD
WHERE CD.ID > 100 

这篇关于使用CROSS APPLY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！