C#使用OleDb查询Access日期范围数据时UNION子句失效问题

你的问题核心很明确：UNION拼接的后两个子查询完全没有应用日期范围过滤，所以不管你指定的f1和f2是什么，这两部分都会返回所有符合“无对应关联记录”的数据，而不是限定在目标日期区间内的结果。

核心修改点 #

需要给后两个UNION分支分别加上日期过滤条件，同时确保关联逻辑准确：

1. 第二个子查询（仅存在于MASTER DATA的记录）：
   要筛选的是MASTER DATA中在指定日期范围内、且在RegistroEnfermedad中没有对应（员工号+日期）匹配的记录，所以需要在WHERE子句中同时添加日期范围条件和NOT EXISTS关联条件。

2. 第三个子查询（仅存在于RegistroEnfermedad的记录）：
   同理，要筛选的是RegistroEnfermedad中在指定日期范围内、且在MASTER DATA中没有对应（员工号+日期）匹配的记录，同样需要在WHERE子句中添加日期范围条件和NOT EXISTS关联条件。

修改后的完整查询代码 #

String query2 = " SELECT [MASTER DATA].[Employee No],[MASTER DATA].[Firstname],[MASTER DATA].[Lastname],Format([MASTER DATA].[ST Date],'dd/mm/yyyy') AS [Illness Date]," + 
                " 'BOTH' AS [Report] FROM [MASTER DATA]" + 
                " INNER JOIN RegistroEnfermedad ON " + 
                " [MASTER DATA].[Employee No] = RegistroEnfermedad.IdEmpleado AND " + 
                " [MASTER DATA].[ST Date] = RegistroEnfermedad.FechaDeBaja AND " + 
                " [MASTER DATA].[ST Date] BETWEEN DateValue('" + f1 + "') AND DateValue('" + f2 + "')" +
                " UNION" + 
                " SELECT [MASTER DATA].[Employee No],[MASTER DATA].[Firstname],[MASTER DATA].[Lastname],Format([MASTER DATA].[ST Date],'dd/mm/yyyy') AS [Illness Date], 'ILLNESS REPORT' AS [Report]" + 
                " FROM [MASTER DATA]" + 
                " WHERE [MASTER DATA].[ST Date] BETWEEN DateValue('" + f1 + "') AND DateValue('" + f2 + "')" +
                " AND NOT EXISTS(" + 
                " SELECT 1 FROM RegistroEnfermedad WHERE" + 
                " [MASTER DATA].[Employee No] = RegistroEnfermedad.IdEmpleado AND" + 
                " [MASTER DATA].[ST Date] = RegistroEnfermedad.FechaDeBaja)" + 
                " UNION " + 
                " SELECT RegistroEnfermedad.IdEmpleado, [MASTER DATA].[Firstname],[MASTER DATA].[Lastname], Format(RegistroEnfermedad.FechaDeBaja,'dd/mm/yyyy') AS [Illness Date],'MY REPORT' AS[Report]" + 
                " FROM RegistroEnfermedad INNER JOIN [MASTER DATA] ON RegistroEnfermedad.IdEmpleado = [MASTER DATA].[Employee No]" +
                " WHERE RegistroEnfermedad.FechaDeBaja BETWEEN DateValue('" + f1 + "') AND DateValue('" + f2 + "')" +
                " AND NOT EXISTS(" + 
                " SELECT 1 FROM [MASTER DATA] WHERE " + 
                " [MASTER DATA].[Employee No] = RegistroEnfermedad.IdEmpleado AND " + 
                " [MASTER DATA].[ST Date] = RegistroEnfermedad.FechaDeBaja)" + 
                " ORDER BY [Employee No]";

额外优化建议 #

• 避免SQL注入与日期格式问题：直接拼接字符串传递日期值容易引发格式错误（比如不同区域设置的日期格式冲突），还存在SQL注入风险。建议改用参数化查询，示例如下：

  String query2 = @"SELECT [MASTER DATA].[Employee No],[MASTER DATA].[Firstname],[MASTER DATA].[Lastname],Format([MASTER DATA].[ST Date],'dd/mm/yyyy') AS [Illness Date], 'BOTH' AS [Report] 
                    FROM [MASTER DATA]
                    INNER JOIN RegistroEnfermedad ON 
                        [MASTER DATA].[Employee No] = RegistroEnfermedad.IdEmpleado AND 
                        [MASTER DATA].[ST Date] = RegistroEnfermedad.FechaDeBaja AND 
                        [MASTER DATA].[ST Date] BETWEEN @StartDate AND @EndDate
                    UNION
                    SELECT [MASTER DATA].[Employee No],[MASTER DATA].[Firstname],[MASTER DATA].[Lastname],Format([MASTER DATA].[ST Date],'dd/mm/yyyy') AS [Illness Date], 'ILLNESS REPORT' AS [Report]
                    FROM [MASTER DATA]
                    WHERE [MASTER DATA].[ST Date] BETWEEN @StartDate AND @EndDate
                        AND NOT EXISTS(
                            SELECT 1 FROM RegistroEnfermedad WHERE
                                [MASTER DATA].[Employee No] = RegistroEnfermedad.IdEmpleado AND
                                [MASTER DATA].[ST Date] = RegistroEnfermedad.FechaDeBaja)
                    UNION 
                    SELECT RegistroEnfermedad.IdEmpleado, [MASTER DATA].[Firstname],[MASTER DATA].[Lastname], Format(RegistroEnfermedad.FechaDeBaja,'dd/mm/yyyy') AS [Illness Date],'MY REPORT' AS[Report]
                    FROM RegistroEnfermedad INNER JOIN [MASTER DATA] ON RegistroEnfermedad.IdEmpleado = [MASTER DATA].[Employee No]
                    WHERE RegistroEnfermedad.FechaDeBaja BETWEEN @StartDate AND @EndDate
                        AND NOT EXISTS(
                            SELECT 1 FROM [MASTER DATA] WHERE 
                                [MASTER DATA].[Employee No] = RegistroEnfermedad.IdEmpleado AND 
                                [MASTER DATA].[ST Date] = RegistroEnfermedad.FechaDeBaja)
                    ORDER BY [Employee No]";
  
  // 添加参数并执行查询
  OleDbCommand cmd = new OleDbCommand(query2, yourConnection);
  cmd.Parameters.AddWithValue("@StartDate", DateTime.Parse(f1));
  cmd.Parameters.AddWithValue("@EndDate", DateTime.Parse(f2));
  

• 简化EXISTS子查询：EXISTS只需要判断是否存在记录，不需要查询具体字段，用SELECT 1代替字段列表会更高效。

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