添加联合查询的公共字段 [英] Adding common Fields of a union query

问题描述

我有一个查询

hi i have a query

报价:

(从PR_MonthlyHeadDtl中选择Head作为Earnings，sum(Amount)作为EarningAmount，其中FinYearID = 11和BranchID = 1，IsDeleted = 0和Type =''ERN''和EmpID = 163 GROUP BY Amount，Head)
工会
(从PR_SalaryHead中选择S.Head作为Earnings，将sum(H.Amount)作为EarningAmount作为S，PR_MonthlyHeadSetting设置为H，其中H.Isdeleted = 0和H.BranchID = 1和H.IsProcessed = 1和H.EmpID = 163和S .Type =''ERN''和H.FinYearID = 11 GROUP BY Amount，Head)

(select Head as Earnings ,sum(Amount) as EarningAmount from PR_MonthlyHeadDtl where FinYearID=11 and BranchID=1 and IsDeleted =0 and Type=''ERN'' and EmpID=163 GROUP BY Amount,Head)
union
(select S.Head as Earnings,sum(H.Amount) as EarningAmount from PR_SalaryHead as S ,PR_MonthlyHeadSetting as H where H.Isdeleted=0 and H.BranchID=1 and H.IsProcessed=1 and H.EmpID=163 and S.Type=''ERN'' and H.FinYearID=11 GROUP BY Amount,Head)

输出为:

The output is:

Basic	300.00
Basic	15000.00
HRA	300.00
HRA	12000.00

我想添加两个查询的通用标题
例如:

i want to add common headings of both queries
Eg:

报价:

Basic	15300.00
HRA	12300.00

help ....

help....

推荐答案

Select	SUM(Earn.[EarningAmount]),
		Earn.[Earnings]
From
(
	Select	Head as Earnings,
		Sum(Amount) as EarningAmount
	From	PR_MonthlyHeadDtl
	Where	FinYearID = 11
	And	BranchID=1
	And	IsDeleted =0
	And	Type='ERN'
	And	EmpID=163
	GROUP BY Amount,Head

	Union

	Select	S.Head as Earnings,
		Sum(H.Amount) as EarningAmount
	From	PR_SalaryHead as S,
		PR_MonthlyHeadSetting as H
	Where	H.Isdeleted=0
	And	H.BranchID=1
	And	H.IsProcessed=1
	And	H.EmpID=163 
	And	S.Type='ERN' 
	And	H.FinYearID=11
	GROUP BY Amount,Head
)[Earn]
Group By Earn.[Earnings]

萨克斯们，我明白了

thax guys,I got it

select sum(EarningAmount) from(
(select Head as Earnings ,sum(Amount) as EarningAmount from PR_MonthlyHeadDtl where FinYearID=11 and BranchID=1 and IsDeleted =0 and Type='ERN' and EmpID=163 GROUP BY Amount,Head)
union
(select S.Head as Earnings,sum(H.Amount)  as EarningAmount from PR_SalaryHead as S ,PR_MonthlyHeadSetting as H  where H.HeadID=S.HeadID and H.Isdeleted=0 and H.BranchID=1   and H.IsProcessed=1  and H.EmpID=163 and S.Type='ERN' and H.FinYearID=11  GROUP BY Amount,Head))as q1

您也可以考虑在查询中构建视图.

You can also consider building a view on your query.

这篇关于添加联合查询的公共字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！