移除Raw测量类型后C#导出Excel无结果的技术问题求助

背景与需求 #

使用C#结合MySQL数据库，通过Inner Join关联多张表获取测量数据，设置两种数据获取模式：

• Standard模式：仅获取input、output、Kalibration Input、Kalibration Output4种测量类型的数据，直接导出至Excel
• Extended模式：获取包含上述4种在内的所有测量类型的数据

已在Visual Studio配置文件中为这4种测量类型设置了对应ID参数。

当前代码与SQL实现 #

Standard模式C#代码 #

if (combocustom.SelectedItem.ToString() == "Standard")
{
    messungTableAdapter1.Fill(richtenDataSet1.Messung, Settings.Default.EinstellungEinganswertId, Settings.Default.EinstellungAusgangswertId, Settings.Default.EinstellungRohId, Settings.Default.EinstellungKaliInId, Settings.Default.EinstellungKaliOtId, dateVon, dateBis, machineId);
    einstellungTableAdapter1.Fill(richtenDataSet1.Einstellung, dateVon, dateBis, machineId);
    kalibrierungTableAdapter1.Fill(richtenDataSet1.Kalibrierung, machineId, dateVon, dateBis);
}

Extended模式C#代码 #

else if (combocustom.SelectedItem.ToString() == "Extended")
{
    messungTableAdapter1.FillBy(richtenDataSet1.Messung, Settings.Default.EinstellungEinganswertId, dateVon, dateBis, machineId, Settings.Default.EinstellungAusgangswertId, Settings.Default.EinstellungRohId, Settings.Default.EinstellungKaliInId, Settings.Default.EinstellungKaliOtId);
    einstellungTableAdapter1.Fill(richtenDataSet1.Einstellung, dateVon, dateBis, machineId);
    kalibrierungTableAdapter1.Fill(richtenDataSet1.Kalibrierung, machineId, dateVon, dateBis);
}

数据查询SQL语句 #

SELECT
    dmc.Code AS DMC,
    dmc.Part_Typ,
    association.cycleTime,
    result.typeString AS [IO/ NIO reason],
    typeMode.String AS [measurement type],
    FORMAT(measure.M_1, '0.000') AS M_1,
    FORMAT(measure.M_2, '0.000') AS M_2,
    FORMAT(measure.M_3, '0.000') AS M_3,
    FORMAT(measure.M_4, '0.000') AS M_4,
    FORMAT(measure.M_5, '0.000') AS M_5,
    FORMAT(measure.M_6, '0.000') AS M_6,
    FORMAT(measure.M_7, '0.000') AS M_7,
    FORMAT(measure.M_8, '0.000') AS M_8,
    FORMAT(measure.M_9, '0.000') AS M_9,
    FORMAT(measure.M_10, '0.000') AS M_10,
    FORMAT(measure.M_11, '0.000') AS M_11,
    FORMAT(measure.M_12, '0.000') AS M_12,
    FORMAT(measure.M_13, '0.000') AS M_13,
    FORMAT(measure.M_14, '0.000') AS M_14,
    FORMAT(measure.M_15, '0.000') AS M_15,
    FORMAT(measure.M_16, '0.000') AS M_16,
    FORMAT(measure.M_17, '0.000') AS M_17,
    FORMAT(measure.M_18, '0.000') AS M_18,
    FORMAT(measure.M_19, '0.000') AS M_19,
    FORMAT(measure.M_20, '0.000') AS M_20,
    CONVERT(DATE, measure.datetime) AS Date,
    CONVERT(TIME, measure.datetime) AS Time
FROM measure
INNER JOIN dmc ON measure.dmc = dmc.dmcId
INNER JOIN typeMode ON measure.type = typeMode.typeId
INNER JOIN association ON measure.associationId = association.Id
INNER JOIN result ON association.result = result.Id
INNER JOIN (
    SELECT
        datetime,
        COUNT(*) AS occurrences
    FROM measure AS measure_1
    WHERE (type = @EingangsId)
    AND (datetime >= @dateVon)
    AND (datetime <= @dateBis)
    AND (machine = @machine)

    OR (type = @AusgangsId)
    AND (datetime >= @dateVon)
    AND (datetime <= @dateBis)
    AND (machine = @machine)

    OR (type = @raw)
    AND (datetime >= @dateVon)
    AND (datetime <= @dateBis)
    AND (machine = @machine)

    OR (type = @caliIn)
    AND (datetime >= @dateVon)
    AND (datetime <= @dateBis)
    AND (machine = @machine)

    OR (type = @caliOut)
    AND (datetime >= @dateVon)
    AND (datetime <= @dateBis)
    AND (machine = @machine)

    GROUP BY datetime
    HAVING (COUNT(*) > 1)
) AS derivedtbl_1 ON measure.datetime = derivedtbl_1.datetime
ORDER BY measure.Id

问题现象 #

移除Raw测量类型相关的参数与SQL条件后，Standard模式导出的Excel无结果；但删除其他类型（如Eingang）时功能正常。

技术排查与解决建议 #

• 检查派生表的分组过滤逻辑：原SQL中的派生表通过HAVING (COUNT(*) > 1)筛选同一时间点下测量记录数大于1的条目。删除Raw类型后，若目标时间点剩余4种类型的记录总数不足2条，派生表将无输出，最终导致整个查询因INNER JOIN无匹配数据返回空结果。可单独执行派生表SQL，替换实际参数值验证是否有输出。
• 验证TableAdapter参数传递顺序：删除Raw参数后，需确认Fill方法的参数顺序是否与TableAdapter配置的SQL参数列表完全匹配。例如原Standard模式Fill方法包含EinstellungRohId参数，删除后若未同步调整后续参数（dateVon、dateBis、machineId）的位置，会导致参数值错位（如将日期传给测量类型ID），从而查询不到数据。
• 修正SQL条件的逻辑优先级：原SQL的WHERE子句中，每个OR分支的条件未整体加括号，虽然当前逻辑因AND优先级高于OR可能正常，但删除Raw后需确认剩余条件的逻辑是否符合预期。建议给每个OR分支添加括号，避免逻辑歧义：

  WHERE 
    ((type = @EingangsId) AND (datetime >= @dateVon) AND (datetime <= @dateBis) AND (machine = @machine))
    OR ((type = @AusgangsId) AND (datetime >= @dateVon) AND (datetime <= @dateBis) AND (machine = @machine))
    OR ((type = @caliIn) AND (datetime >= @dateVon) AND (datetime <= @dateBis) AND (machine = @machine))
    OR ((type = @caliOut) AND (datetime >= @dateVon) AND (datetime <= @dateBis) AND (machine = @machine))
  

• 同步更新TableAdapter配置：在Visual Studio中打开TableAdapter的查询设计器，确认已删除Raw相关的参数，确保代码中Fill方法的参数数量、顺序与配置一致。
• 查看实际执行的SQL与参数：通过MySQL查询日志输出实际执行的SQL语句及参数值，检查是否存在参数值错误、条件过滤过严等问题。
• 调整派生表的过滤规则：若业务允许同一时间点仅存在1条目标类型记录，可将派生表的HAVING (COUNT(*) > 1)改为HAVING (COUNT(*) >= 1)，或直接移除派生表的INNER JOIN（需评估业务逻辑是否允许）。

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