MySQL是否选择行如果下一行是不同的值?

I've Googled a lot but I can't quite figure this out. I apologize if this is a trivial question.

我在谷歌上搜了很多次，但我搞不清楚。如果这是一个微不足道的问题，我道歉。

I would like to SELECT a row if the 'batter' column equals a certain value AND the next row is not the same batter. I'm trying to get the row that represents the final pitch of the at bat. If it is the final pitch, then the next row would be a new batter. I'm using an auto-incremented id column if that helps.

如果“面糊”列等于某个值，而下一行不是相同的面糊，我想选择一行。我试图得到代表击球的最后音高的那一行。如果这是最后的投球，那么下一排将会是一个新的击球手。如果有用的话，我正在使用一个自动递增的id列。

EDIT:

编辑:

Here's how the data is organized. Right now if I want to know how many times batter "276055" struck out I would do:

以下是数据的组织方式。现在，如果我想知道击球手276055三振了多少次，我会这样做:

SELECT * FROM `mlb2012` WHERE batter = "276055" AND atbat_event = "strikeout" AND atbat_pitch = "1"

But if I want to know how many times he struck out looking, I would need to know the pitch_des of the last pitch of the atbat.

但是如果我想知道他三振了多少次，我就需要知道阿特巴最后一次投球的音高。

ID | Batter | atbat_pitch | atbat_event | pitch_des      | 
----------------------------------------------------------
1  | 457477 | 1           | Double      | Called Strike
2  | 457477 | 2           | Double      | In play, no out
3  | 452121 | 1           | Strikeout   | Foul
4  | 452121 | 2           | Strikeout   | Foul
5  | 452121 | 3           | Strikeout   | Called Strike
6  | 543569 | 1           | Walk        | Ball

2 个解决方案

#1

Pseduo code (90% likely to work):

Pseduo代码(90%可能工作):

set @last_batter_id = NULL;

select batter_id, @last_batter_id,
  case when batter = "certain value" and batter_id != @last_batter_id then 'use me' else 'skip me' end as my_action,
  @last_batter_id := batter_id
from my_table

If you want just the batters that you want to use, wrap the last query:

如果您只想要您想要使用的打击者，请封装最后一个查询:

select *
from (
   [ query from above ]
) foo
where foo.my_action = 'use me'

#2

Intriguing question.

有趣的问题。

Looks like you want to join that table to itself based on the rowid on the current row joining the rowid on the previous and checking the 'batter' field is not equal. Use left outer join to handle the last row (ie will return if there is no subsequent row to be joined to). Dont' know mysql syntax but try something like:

看起来您希望根据当前行上加入rowid的rowid将该表连接到自己，并检查“击球手”字段是否不相等。使用左外连接来处理最后一行(如果没有后续要连接的行，将返回)。不知道mysql语法，但试试以下方法:

SELECT fr.* 
FROM mytable fr 
LEFT OUTER JOIN mytable nr 
ON fr.@rowid = nr.@rowid+1 
WHERE fr.batter <> nr.batter

(you can replace the left outer join and on clauses with where conditions and += arguments - I assume you've got the basic MySQL syntax down).

(您可以用where conditions和+= arguments替换左外连接和on子句——我假设您已经掌握了基本的MySQL语法)。

#1