如何将其融合为一个代码？ [英] How can I fuse this into one code?

问题描述

SELECT     dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
COUNT(dbo.tblRequests.RequestNo)  AS   Quantity
FROM         dbo.tblCedula INNER JOIN
             dbo.tblRequests ON dbo.tblCedula.RequestNo = dbo.tblRequests.RequestNo
             RIGHT OUTER JOIN
             dbo.tblBasicPermitFeeSchedule ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
 GROUP BY dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType

和这个：

and this:

SELECT     dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
COUNT(dbo.tblRequests.RequestNo)  AS   Quantity
FROM         dbo.tblBarangayClearance INNER JOIN
             dbo.tblRequests ON dbo.tblBarangayClearance.RequestNo = dbo.tblRequests.RequestNo
             RIGHT OUTER JOIN
             dbo.tblBasicPermitFeeSchedule ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
 GROUP BY dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType

在f第一个代码我有这个结果：

RecId CertType数量

1 Cedula 4

2清仓0



和第二个代码我有这个结果：

RecId CertType数量

1 Cedula 0

2清仓2



现在我想融合它所以我可以得到这样的结果：

RecId CertType数量

1 Cedula 4

2清仓2



注意：tblBarangayClearance和tblCedula彼此无关。但它们与tblRequests和tblBasicPermitFeeSchedule相关，如果它在代码中不明显。

提前谢谢。

in the first code i have this result:
RecId CertType Quantity
1 Cedula 4
2 Clearance 0

and in the second code i have this result:
RecId CertType Quantity
1 Cedula 0
2 Clearance 2

Now i want to fuse it so i can have a result something like this:
RecId CertType Quantity
1 Cedula 4
2 Clearance 2

Note:tblBarangayClearance and tblCedula is not related to each other. But they are related to tblRequests and tblBasicPermitFeeSchedule if it's not obvious in the code.
Thanks in advance.

推荐答案

/*This should get you close*/
--one way is to use union all to get all rows then you perform the GROUPING once on the entire set. 
SELECT     dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
COUNT(dbo.tblRequests.RequestNo)  AS   Quantity
FROM         dbo.tblCedula INNER JOIN
             dbo.tblRequests ON dbo.tblCedula.RequestNo = dbo.tblRequests.RequestNo
             RIGHT OUTER JOIN
             dbo.tblBasicPermitFeeSchedule ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
 
UNION ALL

SELECT     dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
COUNT(dbo.tblRequests.RequestNo)  AS   Quantity
FROM         dbo.tblBarangayClearance INNER JOIN
             dbo.tblRequests ON dbo.tblBarangayClearance.RequestNo = dbo.tblRequests.RequestNo
             RIGHT OUTER JOIN
             dbo.tblBasicPermitFeeSchedule ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
 GROUP BY dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType

 --another way is to use a subquery
SELECT dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
	COUNT(dbo.tblRequests.RequestNo) AS QuantityRequests.
	(
		SELECT dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
		COUNT(dbo.tblRequests.RequestNo) AS Quantity
		FROM dbo.tblBarangayClearance 
		INNER JOIN dbo.tblRequests 
			ON dbo.tblBarangayClearance.RequestNo = dbo.tblRequests.RequestNo
		RIGHT OUTER JOIN dbo.tblBasicPermitFeeSchedule
			ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
		GROUP BY dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType
	) AS QuantityRequestNo
FROM dbo.tblCedula 
INNER JOIN dbo.tblRequests 
	ON dbo.tblCedula.RequestNo = dbo.tblRequests.RequestNo
RIGHT OUTER JOIN dbo.tblBasicPermitFeeSchedule 
	ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId

这篇关于如何将其融合为一个代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！