如何在单个结果中连接两个查询记录 [英] How to join two query records in single result

问题描述

1）查询： -

选择SUM（expense.amount）作为预付款，其中schemeno ='W2 / 2013/11584'和exp_date<'2013-04-01'且passingDate不为null 
 

结果



Premonth

--------

Null





2）查询： -

在sch_mas.sch_no上从费用内连接sch_mas中选择SUM（expense.amount）为expenseamt​​，monyr，schemeno，sch_mas.sch_amount，sch_mas.san_no，sch_mas.disp 
 = expense.schemeno其中exp_date>'2013-03-31'和exp_date<'2014-03-31'
和schemeno ='W2 / 2013/11584'分组由expense.monyr，expense.schemeno，sch_mas组成。 disp，sch_mas.sch_amount，sch_mas.san_no 

结果





expenseamt​​ | monyr | schemeno

--------- --------

716274 | 201307 | W2 / 2013/11584



我的尝试：

如何在单个结果中加入两个查询记录

Premonth | expenseamt​​ | monyr | schemeno

--------- -------- ------- ----------

Null | 716274 | 201307 | W2 / 2013/11584

解决方案

; cte as（
选择SUM（expense.amount）作为来自其中的计划的预付款='W2 / 2013/11584'和exp_date<'2013-04-01'和passingDate不是
），cte2 as（
选择SUM（expense.amount）作为expenseamt​​，monyr，schemeno， sch_mas.sch_amount，sch_mas.san_no，sch_mas.disp 
来自费用内连接sch_mas，位于sch_mas.sch_no = expense.schemeno，其中exp_date>'2013-03-31'和exp_date<'2014-03-31'
和schemeno ='W2 / 2013/11584'分组由expense.monyr，expense.schemeno，sch_mas.disp，sch_mas.sch_amount，sch_mas.san_no 
）select * from cte，cte2 

1)query:-

Select SUM(expen.amount) as premonth from expen  where schemeno='W2/2013/11584' and exp_date<'2013-04-01' and  passingDate is not null

Result

Premonth
--------
Null


2)query:-

Select SUM(expen.amount) as expenamt, monyr,schemeno,sch_mas.sch_amount,sch_mas.san_no,sch_mas.disp
    from expen  inner join sch_mas on sch_mas.sch_no=expen.schemeno where exp_date>'2013-03-31' and exp_date<'2014-03-31'
	and schemeno='W2/2013/11584' group by expen.monyr,expen.schemeno,sch_mas.disp,sch_mas.sch_amount,sch_mas.san_no

Result


expenamt |monyr |schemeno
--------- --------
716274 | 201307|W2/2013/11584

What I have tried:

how to join two query records in single result 

Premonth |expenamt |monyr |schemeno
--------- -------- ------- ----------
Null | 716274 | 201307| W2/2013/11584

解决方案

;with cte as (
Select SUM(expen.amount) as premonth from expen  where schemeno='W2/2013/11584' and exp_date<'2013-04-01' and  passingDate is not null
),cte2 as (
Select SUM(expen.amount) as expenamt, monyr,schemeno,sch_mas.sch_amount,sch_mas.san_no,sch_mas.disp
    from expen  inner join sch_mas on sch_mas.sch_no=expen.schemeno where exp_date>'2013-03-31' and exp_date<'2014-03-31'
	and schemeno='W2/2013/11584' group by expen.monyr,expen.schemeno,sch_mas.disp,sch_mas.sch_amount,sch_mas.san_no
)select * from cte,cte2

这篇关于如何在单个结果中连接两个查询记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！