问题描述
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
这篇关于如何将其融合为一个代码?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!