如何获取SQL Server中的计数？

问题描述：

我已经尝试了很多数字如何从两个表中对于获得计数主表如何获取SQL Server中的计数？

我有三个表

enter image description here

使用我需要得到这个输出这些表中的值。。

enter image description here

尝试，但能得到期望的结果 http://en.wikipedia.org/wiki/Join_(SQL) SQL - LEFT OUTER JOIN and WHERE clause http://forums.devshed.com/oracle-development-96/combination-of-left-outer-join-and-where-clause-383248.html

试图左外连接，右外连接，内连接，... – AnandMohanAwasthi

_show_我们你试过了什么。 –

我正在尝试给定的解决方案，请给我一点时间来标记正确答案并投票。什么让你们降级？ – AnandMohanAwasthi

答

你必须首先GROUP BY子查询，然后JOIN主表：

SELECT 
    a.AttributeId  
    , COALECSE(cntE, 0) AS cntE 
    , COALECSE(cntM, 0) AS cntM 
FROM 
    AttributeMaster AS a 
    LEFT JOIN 
    (SELECT 
      AttributeId 
     , COUNT(*) AS cntE 
     FROM 
      EmployeeMaster 
     GROUP BY 
      AttributeId 
    ) em 
     ON em.AttributeId = a.AttributeId 
    LEFT JOIN 
    (SELECT 
      AttributeId 
     , COUNT(*) AS cntM 
     FROM 
      MonthlyDerivedMaster 
     GROUP BY 
      AttributeId 
    ) mdm 
     ON mdm.AttributeId = a.AttributeId

感谢您的解决方案，这是杀我..非常感谢.. – AnandMohanAwasthi

答

SELECT AttributeId, 
(SELECT COUNT(Eid) FROM EmployeeMaster WHERE AttributeMaster.AttributeId = EmployeeMaster.AttributeId) as master_eid, 
(SELECT COUNT(Eid) FROM MonthnlyDerivedMaster WHERE AttributeMaster.AttributeId = MonthnlyDerivedMaster.AttributeId) as monthly_eid 
FROM AttributeMaster

如何获取SQL Server中的计数？

相关推荐