SQL联合查询

Question

SELECT pv.PropertyID, COUNT(pv.VisitID) AS InitialVisit 
FROM tblPAppointments pa INNER JOIN tblPropertyVisit pv ON pv.AppID = pa.AppID 
WHERE pv.Status = 0 
GROUP BY pv.PropertyID 

UNION ALL 

SELECT jv.PropertyID, COUNT(jv.JobVistID) AS JobVisit 
FROM tblPAppointments pa INNER JOIN tblJobVisits jv ON jv.AppID = pa.AppID 
WHERE jv.VisitStatus = 1 
GROUP BY jv.PropertyID 

我需要得到InitialVisit计数和JobVisit在两个独立的columns.above查询次数，仅返回两列（物业ID，InitialVisit）。

Answer 1

无需使用UNION的。而你不使用tblPAppointments要么

编辑，使之在一个表的任何行。仍然是一排输出虽然

SELECT 
    ISNULL(pv2.PropertyID, jv2.PropertyID), 
    ISNULL(pv2.InitialVisit, 0), 
    ISNULL(jv2.JobVisit, 0) 
FROM 
    (
    SELECT pv.PropertyID, COUNT(pv.VisitID) AS InitialVisit 
    FROM tblPropertyVisit pv 
    WHERE pv.Status = 0 
    GROUP BY pv.PropertyID 
    ) pv2 
    FULL OUTER JOIN 
    (
    SELECT jv.PropertyID, COUNT(jv.JobVistID) AS JobVisit 
    FROM tblJobVisits jv 
    WHERE jv.VisitStatus = 1 
    GROUP BY jv.PropertyID 
    ) jv2 ON pv2.PropertyID = jv2.PropertyID 

Answer 2

使用NULL作为一个占位符列将不会有任何输出：

SELECT pv.PropertyID, 
     COUNT(pv.VisitID) AS InitialVisit, 
     NULL AS jobvisit 
    FROM tblPAppointments pa 
    JOIN tblPropertyVisit pv ON pv.AppID = pa.AppID 
    WHERE pv.Status = 0 
GROUP BY pv.PropertyID 
UNION ALL 
    SELECT jv.PropertyID, 
     NULL AS initialvisit, 
     COUNT(jv.JobVistID) AS JobVisit 
    FROM tblPAppointments pa 
    JOIN tblJobVisits jv ON jv.AppID = pa.AppID 
    WHERE jv.VisitStatus = 1 
GROUP BY jv.PropertyID 

这将返回三列。列别名在第一个查询中是必需的，但不是在第二个查询中 - 我使用别名来明确发生了什么。

请注意，在SQL Server中如此使用NULL将需要您对NULL以外的数据类型使用NULL作为CAST/CONVERT，因为SQL Server将NULL数据类型默认为INT数据类型（就像那样奇怪）。

不使用UNION替代查询：

SELECT x.propertyid, 
      COUNT(y.visitid) AS initialvisit, 
      COUNT(z.jobvisitid) AS jobvisit 
    FROM (SELECT pv.propertyid 
      FROM TBLPROPERTYVISIT pv 
      WHERE EXISTS (SELECT NULL 
          FROM TBLAPPOINTMENTS a 
          WHERE a.appid = pv.appid) 
      UNION 
      SELECT jv.propertyid 
      FROM TBLJOBVISIT jv 
      WHERE EXISTS (SELECT NULL 
          FROM TBLAPPOINTMENTS a 
          WHERE a.appid = jv.appid)) x 
LEFT JOIN TBLPROPERTYVISIT y ON y.propertyid = x.propertyid 
LEFT JOIN TBLJOBVISIT z ON z.propertyid = x.propertyid 
GROUP BY x.propertyid