SQL仅选择具有Where Condition的列上具有最小值的行

Table:

| 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      |

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

如何为每个productId选择一行,并且最小orderIndex不被拒绝?

Expected result:

| 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

This one don't work also:

这个也不起作用:

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

2 个解决方案

#1

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

您可以从选择未被拒绝的产品的最小orderIndex开始,如下所示:

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

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

完成后,您可以在productId和minOrderIndex匹配的条件下将其与原始表连接:

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;

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.

我的查询假设没有重复(productId,orderIndex)对。只要那些不存在,这将工作得很好。这是一个SQL小提琴示例。

#2

http://sqlfiddle.com/#!9/0196f/2

SELECT DISTINCT t.*
FROM table1 t
INNER JOIN (
SELECT productId, min(orderIndex) minIdx
FROM table1
WHERE rejected = 0
GROUP BY productId
  ) t1
ON t.productId = t1.productId
  AND t.orderIndex = t1.minIdx;

#1