SQL仅在条件为条件的列上选择最小值为行的行 [英] SQL Select only rows with Minimum Value on a Column with Where Condition

问题描述

表格:

| id | productId | orderIndex | rejected |
------------------------------------------
| 1  |  1        |   0        |   1      |
| 2  |  1        |   1        |   0      |
| 3  |  1        |   2        |   0      |
| 4  |  2        |   0        |   0      |
| 5  |  2        |   1        |   1      |
| 6  |  3        |   0        |   0      |

如何为每个productId选择一个具有最小orderIndex且不被拒绝的行?

How can I select one row per productId with minimum orderIndex that not rejected?

预期结果:

| id | productId | orderIndex | rejected |
------------------------------------------
| 2  |  1        |   1        |   0      |
| 4  |  2        |   0        |   0      |
| 6  |  3        |   0        |   0      |

我尝试了此查询，但没有收到正确的结果:

I tried this query, but don't recieved correct result:

SELECT id, productId, min(orderIndex)
FROM table
WHERE rejected = 0
GROUP BY productId

此人也无法正常工作

SELECT id, productId, min(orderIndex)
FROM (
    SELECT id, productId, orderIndex
    FROM table
    WHERE rejected = 0
) t
GROUP BY productId

推荐答案

您可以首先选择不被拒绝的产品的最小orderIndex，如下所示:

You can start by selecting the minimum orderIndex of products that are not rejected like this:

SELECT productId, MIN(orderIndex)
FROM myTable
WHERE rejected = 0
GROUP BY productId;

一旦有了，您就可以在productId和minOrderIndex匹配的条件下将其与原始表连接起来:

Once you have that, you can join it with your original table on the condition that productId and minOrderIndex match:

SELECT m.id, m.productId, m.orderIndex
FROM myTable m
JOIN(
  SELECT productId, MIN(orderIndex) AS minOrderIndex
  FROM myTable
  WHERE rejected = 0
  GROUP BY productId) tmp ON tmp.productId = m.productId AND tmp.minOrderIndex = m.orderIndex;

我的查询假设没有重复的(productId，orderIndex)对.只要那些不存在，就可以正常工作.这是一个 SQL小提琴示例.

My query makes the assumption that there are no duplicate (productId, orderIndex) pairs. As long as those don't exist, this will work just fine. Here is an SQL Fiddle example.

这篇关于SQL仅在条件为条件的列上选择最小值为行的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！