将每个组的最大值设置为其组ID

Question

这可能是一个很简单的问题，和IT技术有关。但我现在非常分心，并没有找到任何解决方法。

我有一个像这样的表格：

ID | Value | Gr_Id | Gr_Value
------------------------------
a  |0      |1      |Null
b  |2      |2      |Null
c  |4      |2      |Null
d  |1      |3      |Null
e  |3      |4      |Null
f  |3      |4      |Null
g  |2      |5      |Null
h  |3      |5      |Null

期望输出：

ID | Value | Gr_Id | Gr_Value
------------------------------
a  |0      |1      |0
b  |2      |2      |4
c  |4      |2      |4
d  |1      |3      |1
e  |3      |4      |3
f  |3      |4      |3
g  |2      |5      |3
h  |3      |5      |3

我想更新组的值，并设置group_id的最大值。

谢谢。

- Stad

Answer 1

我认为这将解决你的问题：

SELECT ID, Value, Gr_Id, (
           SELECT MAX(Value)
           FROM tableName t2 
           WHERE t1.Gr_Id = t2.Gr_Id
) as Gr_Value
FROM tableName t1

试一下，希望能有所帮助

- mortb

Answer 2

使用 OUTER APPLY 可以做到这一点。

SELECT ID,Value,Gr_Id,M.Gr_Value
FROM URTable
OUTER APPLY
(
 SELECT MAX (Value) as Gr_Value
 FROM URTable tmp
 WHERE tmp.Gr_Id=URTable.Gr_Id
)M

- Jaydip Jadhav

Answer 3

尝试使用公共表达式来实现：

CREATE TABLE #t
(ID char,Value int, Gr_Id int, gr_value int)

INSERT #t(id, value, gr_id) 
values
('a',0,1),('b',2,2),('c',4,2),('d',1,3),
('e',3,4),('f',3,4),('g',2,5),('h',3,5)

;WITH CTE as
(
  SELECT 
    gr_value, 
    max(value) over(partition by gr_id) max_gr_value
  FROM #t
)
UPDATE CTE SET gr_value = max_gr_value

SELECT * FROM #t

结果：

ID   Value  Gr_Id  Gr_value
a    0      1      0
b    2      2      4
c    4      2      4
d    1      3      1
e    3      4      3
f    3      4      3
g    2      5      3
h    3      5      3

- t-clausen.dk

Answer 4

这应该可以：

UPDATE MyTable --whatever your table is called
SET Gr_Value = MaxValues.Value
FROM MyTable
INNER JOIN (
    SELECT Gr_Id, MAX(Value) AS Value
    FROM MyTable
    GROUP BY Gr_Id) AS MaxValues ON MaxValues.Gr_Id = MyTable.Gr_Id

- Ben

Answer 5

create table Gtable(ID varchar, Value int , Gr_Id int, Gr_Value int)
Insert into Gtable Values('a', 0, 1, null)
Insert into Gtable Values('b', 2, 2, null)
Insert into Gtable Values('c', 4, 2, null)
Insert into Gtable Values('d', 1, 3, null)
Insert into Gtable Values('e', 3, 4, null)
Insert into Gtable Values('f', 3, 4, null)
Insert into Gtable Values('g', 2, 5, null)
Insert into Gtable Values('h', 3, 5, null)

 select A.Id, A.Value, A.Gr_Id, C.maxV Gr_Value from Gtable A
JOIN 
    (   select A.Gr_Id, max(B.Value) maxV from
            ( select Distinct Gr_Id from Gtable ) A
                JOIN Gtable B On A.Gr_Id=B.Gr_Id 
                Group by A.Gr_Id
                ) C
                On A.Gr_Id=C.Gr_Id

---

Id  Value   Gr_Id   Gr_Value
a   0   1   0
b   2   2   4
c   4   2   4
d   1   3   1
e   3   4   3
f   3   4   3
g   2   5   3
h   3   5   3

- Anil

Answer 6

查询

UPDATE t1
SET t1.Gr_Value = t2.val
FROM tblGroup t1
JOIN (SELECT Gr_Id, MAX(Value) AS val FROM tblGroup GROUP By Gr_Id) t2
ON t1.Gr_Id = t2.Gr_Id;

- Ullas