如何避免UPDATE中的GROUP BY或DISTINCT

2020-03-26 sql sql-server azure-sql-server

我有这样的桌子

    A   B   C   D   E   F
00002471    Sd3a28d471  0   24.00   377.500000  1
00002471    Sd3a28d471  0   353.50  377.500000  1
00002471    Sd3a28d471  1   211.00  211.000000  1

00002471    Sd3a28e471  1   343.00  343.000000  1
00002471    Sd3a28e471  0   56.00   242.370000  1
00002471    Sd3a28e471  0   177.06  242.370000  1
00002471    Sd3a28e471  0   9.31    242.370000  1

00002471    Sd3a28f471  0   10.31   10.31   1
00002471    Sd3a28f471  1   10.31   10.31   1

通过对A,B和C列进行分组,我需要检查E中哪个值最低,并将最低行的F列更新为1,将其余列的F更新为0。需要将F列更新为1,其中C为1。我需要的输出如下

    A   B   C   D   E   F
00002471    Sd3a28d471  0   24.00   377.500000  0
00002471    Sd3a28d471  0   353.50  377.500000  0
00002471    Sd3a28d471  1   211.00  211.000000  1

00002471    Sd3a28e471  1   343.00  343.000000  0
00002471    Sd3a28e471  0   56.00   242.370000  1
00002471    Sd3a28e471  0   177.06  242.370000  1
00002471    Sd3a28e471  0   9.31    242.370000  1

00002471    Sd3a28f471  0   10.31   10.31   0
00002471    Sd3a28f471  1   10.31   10.31   1

我在下面的查询中尝试了这个

UPDATE T1
SET T1.F = CASE WHEN T1.E <= T2.E THEN 1 ELSE 0 END
--select t2.*
FROM
(SELECT DISTINCT A,B,C,D,E,F FROM #SalesOrder WHERE E IS NOT NULL) T1 
INNER JOIN 
(SELECT DISTINCT A,B,C,D,E,F FROM #SalesOrder WHERE E IS NOT NULL) T2                           
ON  T1.A    = T2.A
AND T1.B    = T2.B
--AND T1.C  = T2.C
WHERE T1.C = 1 AND T2.C = 0

但是这个查询抛出一个错误

错误信息
Msg 4418,第16级,状态1,行265
派生表“ T1”不可更新,因为它包含聚合,DISTINCT或GROUP BY子句或PIVOT或UNPIVOT运算符。

@Suresh Gajera 在采用不同的值之后,我需要采用不同的A,B,C和E值,就像这样

A   B   C   E
00002471    Sd3a28d471  0   377.500000
00002471    Sd3a28d471  1   211.000000
00002471    Sd3a28e471  0   242.370000
00002471    Sd3a28e471  1   343.000000
00002471    Sd3a28f471  0   10.31   
00002471    Sd3a28f471  1   10.31

之后,我需要比较基于C列的E值,在此情况下第二行与第一行相比是最低的,因此第二行应为1,第一行应为0 ...以同样的方式第三和第四行行应更新..在第5和第6行时,在这种情况下,值是相同的,我需要将F列更新为1,其中C为1

Answers

仅当按A和B(不包括C)分组时,预期结果才有意义。
在这种情况下:

update t
set t.f = case when t.e = tt.mine then 1 else 0 end
from tablename t inner join (
  select a, b, min(e) mine
  from tablename   
  group by a, b
) tt 
on tt.a = t.a and tt.b = t.b

参见演示
或使用CTE和窗口函数MIN()

with cte as (
  select *, min(e) over (partition by a, b) mine
  from tablename 
)
update cte
set f = case when e = mine then 1 else 0 end

参见演示
结果:

>    A | B          |  C |   D | E      |  F
> ---: | :--------- | -: | --: | :----- | -:
> 2471 | Sd3a28d471 |  0 |  24 | 377.50 |  0
> 2471 | Sd3a28d471 |  0 | 353 | 377.50 |  0
> 2471 | Sd3a28d471 |  1 | 211 | 211.00 |  1
> 2471 | Sd3a28e471 |  1 | 343 | 343.00 |  0
> 2471 | Sd3a28e471 |  0 |  56 | 242.37 |  1
> 2471 | Sd3a28e471 |  0 | 177 | 242.37 |  1
> 2471 | Sd3a28e471 |  0 |   9 | 242.37 |  1

Related