C#基于多条件对比并筛选DataTable行的技术需求

原始数据 #

SHOPID   ITEMID   OLD_BATCHNO   NEW_BATCHNO   OLD_QTY   NEW_QTY

SHOP01   ITEM01    BATCH0001                     1        
SHOP01   ITEM01                  BATCH0001                 1
SHOP02   ITEM02    BATCH0002                     2         
SHOP02   ITEM02                  BATCH0002                 3
SHOP03   ITEM03    BATCH0003                     4        
SHOP03   ITEM03                  BATCH0003                 5
SHOP04   ITEM04    BATCH0004                     4
SHOP05   ITEM05                  BATCH0005                 5

预期结果 #

SHOPID   ITEMID   OLD_BATCHNO   NEW_BATCHNO   OLD_QTY   NEW_QTY

SHOP02   ITEM02    BATCH0002     BATCH0002       2         3
SHOP03   ITEM03    BATCH0003     BATCH0003       4         5
SHOP04   ITEM04    BATCH0004                     4
SHOP05   ITEM05                  BATCH0005                 5

筛选条件 #

• 基于SHOPID和ITEMID分组匹配
• 满足以下任一子条件：
  • 组内OLD_BATCHNO与NEW_BATCHNO不匹配（空值视为不匹配）
  • 组内OLD_BATCHNO与NEW_BATCHNO匹配，但OLD_QTY与NEW_QTY不匹配
  • 组内仅存在单条记录（仅含旧批次或仅含新批次数据）

实现方案 #

SQL 实现 #

通过分组聚合判断组内特征，再关联原始数据筛选目标行：

WITH grouped_data AS (
    SELECT 
        SHOPID,
        ITEMID,
        MAX(OLD_BATCHNO) AS old_batch,
        MAX(NEW_BATCHNO) AS new_batch,
        MAX(OLD_QTY) AS old_qty,
        MAX(NEW_QTY) AS new_qty,
        COUNT(*) AS record_count
    FROM your_table
    GROUP BY SHOPID, ITEMID
)
SELECT 
    t.SHOPID,
    t.ITEMID,
    t.OLD_BATCHNO,
    t.NEW_BATCHNO,
    t.OLD_QTY,
    t.NEW_QTY
FROM your_table t
JOIN grouped_data g ON t.SHOPID = g.SHOPID AND t.ITEMID = g.ITEMID
WHERE 
    g.record_count = 1
    OR (g.old_batch = g.new_batch AND g.old_qty != g.new_qty)
    OR (g.old_batch IS NULL OR g.new_batch IS NULL OR g.old_batch != g.new_batch)

Python Pandas 实现 #

利用分组聚合标记合法组，再提取原始数据中对应行：

import pandas as pd

# 构造示例数据
df = pd.DataFrame([
    ["SHOP01", "ITEM01", "BATCH0001", None, 1, None],
    ["SHOP01", "ITEM01", None, "BATCH0001", None, 1],
    ["SHOP02", "ITEM02", "BATCH0002", None, 2, None],
    ["SHOP02", "ITEM02", None, "BATCH0002", None, 3],
    ["SHOP03", "ITEM03", "BATCH0003", None, 4, None],
    ["SHOP03", "ITEM03", None, "BATCH0003", None, 5],
    ["SHOP04", "ITEM04", "BATCH0004", None, 4, None],
    ["SHOP05", "ITEM05", None, "BATCH0005", None, 5]
], columns=["SHOPID", "ITEMID", "OLD_BATCHNO", "NEW_BATCHNO", "OLD_QTY", "NEW_QTY"])

# 分组计算组内关键特征
grouped = df.groupby(["SHOPID", "ITEMID"]).agg(
    old_batch=("OLD_BATCHNO", "first"),
    new_batch=("NEW_BATCHNO", "last"),
    old_qty=("OLD_QTY", "first"),
    new_qty=("NEW_QTY", "last"),
    count=("SHOPID", "size")
).reset_index()

# 筛选合法的SHOPID+ITEMID组
valid_groups = grouped[
    (grouped["count"] == 1)
    | ((grouped["old_batch"] == grouped["new_batch"]) & (grouped["old_qty"] != grouped["new_qty"]))
    | ((grouped["old_batch"] != grouped["new_batch"]) | grouped["old_batch"].isna() | grouped["new_batch"].isna())
]

# 提取原始数据中符合条件的行
result = df.merge(valid_groups[["SHOPID", "ITEMID"]], on=["SHOPID", "ITEMID"], how="inner")

# 输出结果
print(result.to_string(index=False))

内容的提问来源于stack exchange，提问作者gymcode