如何从来自另一个查询的记录中减去来自一个查询的记录？

Question

这些是两个查询，返回多个记录，那么是否有任何解决方案从另一个结果中减去一个结果的每行数据？

QUERY1：

SELECT COUNT(*) As AbhidayNotPaidSansthan 
FROM PanjikaranMaster GROUP BY Zila 

QUERY2：

SELECT COUNT(*) as anps FROM AbhidayMaster,PanjikaranMaster 
    WHERE PanjikaranMaster.OfficeRegId=AbhidayMaster.OfficeRegId AND 
    AbhidayMaster.DipositDate Between ('2012-09-19') AND ('2012-09-24') 
GROUP BY PanjikaranMaster.Zila 

Answer 1

与此查询尝试，它会解决你的问题：

SELECT ISNULL(a.cnt,0) - ISNULL(b.cnt,0) AS CNT,a.Zila 
FROM 
    (SELECT Zila,COUNT(*) as cnt As AbhidayNotPaidSansthan 
    FROM PanjikaranMaster GROUP BY Zila) a 
    LEFT JOIN 
    (SELECT Zila,COUNT(*) as cnt FROM AbhidayMaster,PanjikaranMaster 
    WHERE PanjikaranMaster.OfficeRegId=AbhidayMaster.OfficeRegId AND 
    AbhidayMaster.DipositDate Between ('2012-09-19') AND ('2012-09-24') 
    GROUP BY PanjikaranMaster.Zila) b 
    ON a.Zila = b.Zila 

我已经做了类似的测试中SQLFIDDLE