如何标记具有最大值的行

Question

我有一个用于标识每行的ID、一篇文章的ID、一个供应商的ID和一个库存。

+----+------------+-------------+-------+
| ID | article_ID | supplier_ID | stock |
+----+------------+-------------+-------+
| 1  | 1          | 1           | 18    |
| 2  | 1          | 2           | 1     |
| 3  | 1          | 3           | 20    |
| 4  | 2          | 1           | 5     |
| 5  | 2          | 2           | 40    |
| 6  | 3          | 1           | 56    |
| 7  | 3          | 3           | 7     |
| 8  | 3          | 4           | 12    |
+----+------------+-------------+-------+

我需要标记出提供最大库存的供应商，并获取以下结果：

+----+------------+-------------+-------+------+
| ID | article_ID | supplier_ID | stock | flag |
+----+------------+-------------+-------+------+
| 1  | 1          | 1           | 18    | 0    |
| 2  | 1          | 2           | 1     | 0    |
| 3  | 1          | 3           | 20    | 1    |
| 4  | 2          | 1           | 5     | 0    |
| 5  | 2          | 2           | 40    | 1    |
| 6  | 3          | 1           | 56    | 1    |
| 7  | 3          | 3           | 7     | 0    |
| 8  | 3          | 4           | 12    | 0    |
+----+------------+-------------+-------+------+

我理解的问题是需要从一个select语句中选择出最大库存值所在的行ID，但我不知道如何实现。我卡住了...需要stackoverflow的骑士们帮助！

- Ceci Semble Absurde.

Answer 1

使用窗口函数：

select t.*,
       (case when stock = max(stock) over (partition by article_id)
             then 1 else 0
        end) as flag
from t;

注意：如果多个供应商的最大值相同，则会标记它们。如果您只想要一个，请使用row_number()：

select t.*,
       (case when row_number() over (partition by article_id order by stock desc) = 1
             then 1 else 0
        end) as flag
from t;

- Gordon Linoff

Answer 2

您也可以使用APPLY：

最初的回答。

SELECT t.*, (CASE WHEN t.stock = t1.maxstock THEN 1 ELSE 0 END) AS flag
FROM table t CROSS APPLY
     (SELECT MAX(t1.stock) as maxstock 
      FROM table t1
      WHERE t1.article_id = t.article_id
     ) t1;

- Yogesh Sharma

Answer 3

我认为这会对你有所帮助。

                create table #temp

            (
            ID int,
            article_ID int,
            supplier_ID int,
            stock int
            )

            insert into #temp values (1  , 1 , 1, 18)
            insert into #temp values (2  , 1          , 2           , 1    )
            insert into #temp values (3  , 1          , 3           , 20   )
            insert into #temp values (4  , 2          , 1           , 5    )
            insert into #temp values (5  , 2          , 2           , 40   )
            insert into #temp values (6  , 3          , 1           , 56   )
            insert into #temp values (7  , 3          , 3           , 7    )
            insert into #temp values (8  , 3          , 4           , 12   )



            Select ID,article_ID,supplier_ID,stock,case when Stock >= 20 Then 1 Else 0 End As Flag from #temp

            Drop table #temp

- Mohammad Shehroz

---

但是为什么亲爱的@Tony？ - Mohammad Shehroz

插入到 #temp 表中值为 (9, 4, 1, 1)。 - Ceci Semble Absurde.

它无法正常工作，因为您将标志设置为任何库存大于20的行，而不是该行具有该article_id的最大库存值。 - digital.aaron