选择组中的所有行，其中组中的一列具有一个特定值，而另一列在下一行具有另一特定值 [英] Select all rows in a group where within the group, one column has one specific value, and another column has another specific value on the next row

问题描述

我是SQL的新手，所以我不确定哪种方法最适合这种任务:

I am new to SQL, so I’m not sure which approach is best for this kind of task:

我有一个表，其中所有行组都与同一项目相关，该项目的名称显示在第一列中.其他详细信息显示在其他列中.我试图基于第一列中的相同值来检索每个组的所有行，其中每当某个值出现在一个列中时，另一个值就会出现在下一行的不同列中.

I have a table where groups of rows all relate to the same item, whose name appears in the first column. Other details appear in the other columns. I am trying to retrieve all rows for every group based on having the same value in the first column, where every time a certain value appears in one column, another value appears in a different column in the following row.

 | Fruit  | Value1| Value2|
 --------------------------
 | APPLE  |  A    |       |
 | APPLE  |       | E     |
 | PEAR   |  A    |       |
 | PEAR   |       | X     |
 | FIG    |       | X     |
 | FIG    |  A    |       |
 | CHERRY |  A    |       |
 | CHERRY |       | X     |
 | CHERRY |  A    |       |
 | CHERRY |       | X     |
 | GRAPE  |       | X     |
 | GRAPE  |       | T     |
 | ORANGE |  A    |       |
 | ORANGE |       | X     |
 | ORANGE |       | Y     |
 | ORANGE |       | Z     |
 | PEACH  |  B    |       |
 | PEACH  |  A    |       |
 | PEACH  |       | X     |
 | MANGO  |  B    |       |
 | MANGO  |  C    |       |
 | MANGO  |  D    |       |

从上表中，我想选择一个给定水果的所有行，其中Value1在一行上是A，Value2在下一行上是X，并且在该水果的任何一行上Value1中都没有出现A

From the above table, I would like to select all rows for a given Fruit, where Value1 is A on one row, Value2 is X on the following row, and nothing other than A appears in Value1 on any row for that Fruit.

从上表中，查询应提供如下结果:

From the above table, the query should deliver results that look like this:

 | Fruit  | Value1| Value2|
 --------------------------
 | PEAR   |  A    |       |
 | PEAR   |       | X     |
 | CHERRY |  A    |       |
 | CHERRY |       | X     |
 | CHERRY |  A    |       |
 | CHERRY |       | X     |
 | ORANGE |  A    |       |
 | ORANGE |       | X     |
 | ORANGE |       | Y     |
 | ORANGE |       | Z     |

• APPLE被排除，因为在Value1 = A的那一行之后的行中， Value2！= X.
排除
• FIG是因为Value2 = X出现在Value1 = A之前的行上， 而不是后面的行.
• GRAPE被排除，因为在Value1 = A处没有行.
• 将PEACH排除在外是因为Value1！= A至少有一行.
• MANGO被排除，因为没有行的值为Value1 = A，并且因为 没有行，其中Value2 = X.
• APPLE is excluded because on the row after the one where Value1=A, Value2!=X.
• FIG is excluded because Value2=X occurs on the row before Value1=A, instead of the row after.
• GRAPE is excluded because there is no row where Value1=A.
• PEACH is excluded because there is at least one row where Value1!=A.
• MANGO is excluded because there is no row were Value1=A, and because there is no row where Value2=X.

在我看来有些棘手的部分是在行组的级别执行多项检查，但仍返回匹配组的所有行.

The part that seems a bit tricky to me is performing several checks at the level of the group of rows, but still returning all the rows of the matching group.

预先感谢您的提示和建议.让我知道您是否需要我澄清这个问题.该数据库是z/OS上的DB2 V10.

Thanks in advance for tips and suggestions. Let me know if you need me to clarify the question. The database is DB2 V10 on z/OS.

推荐答案

除了FIG(在您的示例中)需要订单列(id或其他)，查询可解决您的问题:

Except for the FIG (in your example), which requires an order column (id or whatever), the query bellow solves your problem:

select * from fruits 
where fruit not in (select fruit from fruits where value1 is not null and value1 <> '' and value1 <> 'A')
and exists (select fruit from fruits f2 where f2.fruit = fruits.fruit and value2 = 'X')

这篇关于选择组中的所有行，其中组中的一列具有一个特定值，而另一列在下一行具有另一特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！