正在更新字段

Question

我有一个随机的给你们的：我有一张有4个字段的桌子ProductCode，LocationCode，Primary，StockInLocation

一些产品代码在表中只有1个条目，其他产品代码有2个条目，其中1应该是主要位置，另一个不应该(主要是1或0)

例如：
产品代码 nbsp 位置代码 nbsp 主要 nbsp 库存位置
ABC nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 5
BCD nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 2 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 3
CDE nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 3 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 5
CDE nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 5 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 0 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 22
DEF nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；4 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；7
DEF nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 7 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 0 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；10
EFG nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 6 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 5

我想做的是将"主要"字段重置为1，其中库存位置最高，或者表中只有1个条目，所有其他主要字段都应该为0

到目前为止，我的第一个问题是更新plocate a设置a."主要"="0">

现在我需要第二个查询，它将根据上述规则正确设置主字段，结果如下表所示：
Productcode nbsp nbsp；位置代码 nbsp 主要 nbsp 库存位置
ABC nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 5
BCD nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 2 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 3
CDE nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 3 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 0 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 5
CDE nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 5 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 22
DEF nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；4 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；0 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；7
DEF nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 7 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；10
EFG nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 6 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp；1 nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp nbsp 5

不幸的是，这让我很困惑，因为我不知道如何只更新表中只有1个条目的行(例如产品ABC、BCD、EFG)和库存最多的行(如CDE第2行和DEF第2行)。有人理论吗？

Answer 1

我无法回答您使用的特定软件，但我有一个通用算法，您应该能够翻译它？我使用了pandas，任何像样的数据库都应该具有相同类型的功能。

设置测试数据：

import pandas as pd
df = pd.DataFrame({
"ProductCode" : ["ABC","BCD", "CDE", "CDE", "DEF", "DEF", "EFG"],
"LocationCode" : [1, 2, 3, 5, 4, 7, 6],
"Primary" : [1, 1, 1, 0, 1, 0, 1],
"StockInLocation" : [5, 3, 5, 22, 7, 10, 5]})

定义一个函数，在给定产品代码的各个位置输出最大库存：

max_stock = lambda x : df.loc[df["ProductCode"] == x, "StockInLocation"].max()

将Primary列设置为零：

df["Primary"] = [0]*len(df)

循环遍历唯一ProductCode值的列表，并将Primary设置为1，其中StockInLocation是该ProductCode:的最大值

for x in df["ProductCode"].unique():
df.loc[(df["ProductCode"] == x) & (df["StockInLocation"] == max_stock(x)), "Primary"] = 1