自我加入产生的上个月价值不一致 [英] Self join produces inconsistent value for last month

问题描述

为了在同一行中显示上个月的某些记录，我必须像这样将表连接到自身:

In order to display some records from the previous month in the same row, I had to join my table to itself like this:

SELECT 
      a.[periodOrderNum]
      ,SUM(a.AktivNy) as ActiveNew
      ,SUM(b.[AktivNy]) as ActiveNewPrevMonth
  FROM myTable a
  JOIN myTable b 
    ON a.customerSNKey = b.customerSNKey 
   AND a.periodOrderNum = b.periodOrderNum + 1
  GROUP BY a.[periodOrderNum]

在上面的代码中， periodOrderNum 是分配给期间的订单号.例如，如果当前期间为5，则上一个期间的订单号为4.

In the above code periodOrderNum is an order number assigned to period. E.g if the current period is 5, then the previous period has an order number of 4.

customerSNKey 是客户的序列号密钥，我将其用作 JOIN 条件之一.我用来获取上一个期间值的其他条件( a.periodOrderNum = b.periodOrderNum +1 ).但是结果并不一致.如下图所示，对于订单号280， ActiveNewPrevMonth 的值为20203，而不是订单号279的 ActiveNew 值.

And customerSNKey is customer's serial number key which I use as one of my JOIN conditions. The other condition (a.periodOrderNum = b.periodOrderNum + 1) I use to get the value of previous period. But the results are not consistent. As seen in the picture below, for order number 280, the value of ActiveNewPrevMonth is 20203 which is not the ActiveNew value of order number 279.

我加入联盟时在做什么错了?

What am I doing wrong in my join?

推荐答案

没有一些示例数据，我无法确定哪里出了问题.但是无论哪种方式，我相信您的查询都会返回错误的值.假设您在相同的 periodOrderNum 下有多个行，则可以保证您的 ActiveNew 值被夸大了.怎么样将它们分成这样的子查询:

Without some sample data, I can't tell where went wrong. But either way, your query here will return wrong value anyway I believe. Say if your have multiple rows under same periodOrderNum, your ActiveNew value is pretty much guaranteed to be exaggerated. How about try to separate them into subqueries like this:

 SELECT a.[periodOrderNum]
 ,a.ActiveNew
 ,b.ActiveNewPrevMonth
 FROM
 (
 SELECT 
 customerSNKey, [periodOrderNum]
 ,SUM(AktivNy) as ActiveNew
 FROM myTable
 GROUP BY customerSNKey, [periodOrderNum]
 ) AS a
 INNER JOIN 
 (
 SELECT 
 customerSNKey, [periodOrderNum] + 1 as [periodOrderNum]
 ,SUM(AktivNy) as ActiveNewPrevMonth
 FROM myTable
 GROUP BY customerSNKey, [periodOrderNum] + 1
 ) AS b
 ON a.customerSNKey = b.customerSNKey
 AND a.[periodOrderNum] = b.[periodOrderNum]

这篇关于自我加入产生的上个月价值不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！