连接来自两个不同查询的行 [英] Join rows from two different queries

问题描述

我有一个表sales_table，其中包含id，日期和销售字段.

I have a table sales_table with a id,date and sales fields.

没有            s -------------------------------------------------- -
1       1-nan 2           3-JAN           12500，点击 3           4-扬           8000，点击 4           5-JAN           12000，点击 5           8-扬          万，点击              nbsp; ";              &b ;          "
"   b ;         "
"      b ;      "
          &bb       "
"         b                   nbsp; b  "                            4000 br>

no          date          sales
----------------------------------------------------
1          1-Jan          10,000
2          3-Jan          12,500
3          4-Jan          8,000
4          5-Jan          12,000
5          8-Jan          10,000
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
100          13-Mar          4000

日期是唯一的，但并不总是连续的. no是唯一的，并且与每个较高日期的no顺序递增.

the date is unique but not always in series. the no is unique and in series with a incremented no of each higher date.

我希望了解当前日期和以前日期之间的差异.像

I am looking to get the difference between the sales of the current and previous date. something like

没有               日期                                      销售                                      diff             
-------------------------------------------------- ----------------------------------------
1     ;         1月1日 10,000              0             
2                                      3月份                            12,500                                     2500             
3                                      4月份                            8,000                                     -4500              
4                                      5月1日 12,000                           4000             
5                                      8月1日 10,000              -2000            

no               date               sales               diff              
------------------------------------------------------------------------------------------
1               1-Jan               10,000               0              
2               3-Jan               12,500               2500              
3               4-Jan               8,000               -4500              
4               5-Jan               12,000               4000              
5               8-Jan               10,000               -2000              

我正在使用sql查询-

I am using a sql query-

select t1.no,t1.date,t1.sales (t1.sales-2.sales) as diff 
from sales_table as t1,sales_table as t2 
where(t1.no=t2.no+1) order by t1.date

这很好，除了我从2号开始记录.

This works fine except i get records starting from no 2.

所以我写了另一个SQL查询-

so i have written another another sql query -

select no,date,sales,sales-sales as diff 
from sales_table 
where(no=1) 

输出为- 1月1日，10000,0.

which outputs as- 1, 1-Jan,10000,0.

如何连接这两个查询中的行?

How can i join rows from both these queries?

推荐答案

取决于您的数据库，有几个不同的选项.一种选择是修改现有查询以使用OUTER JOIN:

There are a couple of different options depending on your database. One option is to modify your existing query to use an OUTER JOIN:

select t1.no,t1.date,t1.sales,(t1.sales-coalesce(t2.sales,t1.sales)) as diff 
from sales_table as t1
    left join sales_table as t2 on t1.no=t2.no+1
order by t1.date

• SQL小提琴演示
• SQL Fiddle Demo

这篇关于连接来自两个不同查询的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！