SQL Server内连接查询时salesId字段返回错误数据问题排查

咱们先拆解下你的查询逻辑：你通过invoice字段关联Transaction和M_Trans两张表，筛选2018年2月的交易，去重后返回订单ID、发票号、PPn和salesId，但salesId结果不符合预期。我来帮你一步步排查可能的问题：

1. 先确认关联条件的合理性 #

你的关联条件是sfc.invoice=inv.invoice，首先要验证这个字段是不是两张表之间唯一且准确的关联键：

• 挑一个出现问题的invoice值，分别查询两张表的对应记录：

  -- 查询Transaction表中该invoice对应的salesId
  SELECT inv.invoice, inv.salesId FROM Transaction inv WHERE inv.invoice = '某异常invoice值'
  

  -- 查询M_Trans表中该invoice的所有记录
  SELECT sfc.invoice, sfc.* FROM M_Trans sfc WHERE sfc.invoice = '同一个异常invoice值'
  

  如果M_Trans里一个invoice对应多条记录，inner join后会产生笛卡尔积，再加上distinct可能会随机保留一条salesId，导致结果不符合预期。

2. 检查Transaction表中salesId的唯一性 #

salesId来自Transaction表，你需要明确：每个invoice对应的salesId是不是唯一的？

• 执行下面的语句，看看2018年2月的交易中，有没有同一个invoice对应多个不同salesId的情况：

  SELECT inv.invoice, COUNT(DISTINCT inv.salesId) AS salesIdCount
  FROM Transaction inv 
  WHERE inv.date >= '2018-02-01' AND inv.date < '2018-03-01'
  GROUP BY inv.invoice
  HAVING COUNT(DISTINCT inv.salesId) > 1
  

  如果存在这种情况，说明业务上可能存在数据重复，你需要明确哪个salesId是正确的——比如取最新的记录、或者取状态为有效的记录，再添加对应的过滤条件。

3. 审视distinct的作用 #

distinct是对整行所有字段的组合去重，如果两行数据除了salesId不同，其他字段（Order ID、Faktur、PPn）都相同，那么这两行都会被保留；如果其他字段组合重复但salesId不同，distinct会去掉重复的整行，但可能会留下随机的salesId值。这时候你需要明确业务上应该保留哪一个salesId。

4. 优化日期筛选（附带小建议） #

你的日期筛选可以改成更简洁且索引友好的写法，避免DATEPART可能带来的性能问题：

WHERE inv.date >= '2018-02-01' AND inv.date < '2018-03-01'

针对不同场景的修正方案 #

场景1：M_Trans中一个invoice对应多条记录 #

先对M_Trans做去重，确保每个invoice只对应一条nofaktur记录，再关联Transaction：

SELECT DISTINCT 
  inv.transId as [Order ID], 
  sfc.nofaktur as Faktur, 
  inv.amount as PPn, 
  inv.salesId 
FROM Transaction inv 
INNER JOIN (
  SELECT DISTINCT invoice, nofaktur 
  FROM M_Trans
) sfc ON sfc.invoice = inv.invoice 
WHERE inv.date >= '2018-02-01' AND inv.date < '2018-03-01' 
ORDER BY sfc.nofaktur asc

场景2：Transaction中一个invoice对应多个salesId #

用窗口函数取每个invoice下符合业务规则的唯一salesId（比如最新的记录）：

SELECT 
  inv.transId as [Order ID], 
  sfc.nofaktur as Faktur, 
  inv.amount as PPn, 
  inv.salesId 
FROM (
  SELECT *, 
         ROW_NUMBER() OVER(PARTITION BY invoice ORDER BY date DESC) as rn
  FROM Transaction 
  WHERE date >= '2018-02-01' AND inv.date < '2018-03-01'
) inv 
INNER JOIN M_Trans sfc ON sfc.invoice = inv.invoice 
WHERE inv.rn = 1
ORDER BY sfc.nofaktur asc

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