按月销售订单之间的差异 [英] difference btween sales orders by month

问题描述

Pid Sales Month1

1月10日

2月30日

3 60 Mar

4 50 Apr







基于Month1如何显示销售价值差异





赞

OutPut



Pid Sales Month1差异

1 10月0日

2 30 2月20日

3 60 Mar -30

4 50 4月10日

 选择 t1.pid， t1.sales，t1.month1，
 isnull（（ select  t2.sales  from  table1 t2 其中 t2.pid = t1.pid  -   1 ） -  t1.sales， 0 ） as  differe 
  from  table1 t1 

取决于您的数据库及其版本。

如果它足够新，您可以使用分析函数滞后

 < span class =code-keyword>选择 Pid，
销售额，
 Month1，
 LAG（销售额， 1 ， 0 ） OVER （ PARTITION  < span class =code-keyword> BY  Pid  ORDER   BY  Month1） -  sales < span class =code-keyword> as  difference 
  FROM  MyTable 

对于其他解决方案，您需要声明数据库和模型之间有点不同



< update>

你也可以使用Row_Number：

 使用排序为（
 选择 Pid，
销售额，
 Month1，
 Row_Number（） OVER （ PARTITION   BY  Pid  ORDER   BY  Month1）rn 
 < span class =code-keyword> FROM  MyTable 
）
 选择 O1.Pid，
 O1.Sales ，
 O1.Month1，
 O2.Sales  -  O1.sales  as 区别
  FROM 有序O1 左 外 加入 Ordered O2   O1.rn = O2.rn  -   1  

< ; /更新>

Pid Sales Month1
1 10 Jan
2 30 Feb
3 60 Mar
4 50 Apr



based on Month1 How to Display sales values diffrence


Like
OutPut

Pid Sales Month1 Differe
1 10 Jan 0
2 30 Feb -20
3 60 Mar -30
4 50 Apr 10

解决方案

Try this:

select t1.pid, t1.sales, t1.month1,
isnull((select t2.sales from table1 t2 where t2.pid = t1.pid - 1) - t1.sales, 0) as differe
from table1 t1

Depends on your database and version thereof.
If it's new enough you can use the analytical function Lag

Select  Pid,
        Sales,
        Month1,
        LAG(sales, 1, 0) OVER (PARTITION BY Pid ORDER BY Month1) - sales as difference
FROM    MyTable

For other solutions you need to state your Database and model as it differs a bit between them

<update>
You can also use Row_Number:

With ordered as (
    Select  Pid,
            Sales,
            Month1,
            Row_Number() OVER (PARTITION BY Pid ORDER BY Month1) rn
    FROM    MyTable
    )
Select  O1.Pid,
        O1.Sales,
        O1.Month1,
        O2.Sales - O1.sales as Difference
FROM    Ordered O1 left outer Join Ordered O2 on O1.rn = O2.rn - 1

</update>

这篇关于按月销售订单之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！