sql - 将两个表合并为一个表，两个表都具有不同的 where 子句

Question

我有如下两个 SQL 查询:

第一次查询

SELECT TC.TCName, 
sum(BS.BLDOS) as BLDOS,
sum(BS.CollectedAmount) as CollectedAmount, 
(0.35*sum(BS.BLDOS)) as TargetAmount
FROM [Customer] C 
INNER JOIN [dbo].[BillingStatus] BS ON BS.CustomerID = C.CustomerID 
INNER JOIN [dbo].[TCDetails] TC ON TC.CustomerID = BS.CustomerID
WHERE TC.TCName in (Select distinct(TCName) 
                    FROM [dbo].[TCDetails] )
GROUP BY TCName 
order by [TCName] ASC

我得到一个结果:

   ||TCName ||  BLDOS  || CollectedAmount || TargetAmount||
    | Aarti  |  81234.2 |    1678.76       |  789065     |
    | Dev    |  181234.2|     678.76       |  2389065    | 

第二个查询是:

SELECT COUNT(*) as count 
FROM [dbo].[TCDetails] 
where TCName in (Select distinct(TCName) FROM [dbo].[TCDetails] ) 
AND CallDate is NOT NULL 
GROUP BY TCName

我得到:

  ||count||
     231
     123

我想这样打印

   ||TCName ||  BLDOS  || CollectedAmount || TargetAmount|| count ||
    | Aarti  |  81234.2 |    1678.76       |  789065     |   231   |
    | Dev    |  181234.2|     678.76       |  2389065    |   123   |

我很困惑，因为两个查询都有不同的 wheres 子句

Answer 1

试试这个

SELECT  * FROM 
(
SELECT TC.TCName, sum(BS.BLDOS) as BLDOS, sum(BS.CollectedAmount) as CollectedAmount, (0.35*sum(BS.BLDOS)) as TargetAmount
FROM [Customer] C INNER JOIN
     [dbo].[BillingStatus] BS ON BS.CustomerID = C.CustomerID INNER JOIN
     [dbo].[TCDetails] TC ON TC.CustomerID = BS.CustomerID
WHERE TC.TCName in (Select distinct(TCName) FROM [dbo].[TCDetails] )
GROUP BY TCName order by [TCName] ASC
) S JOIN
(
SELECT TCName,COUNT(*) as count FROM [dbo].[TCDetails] 
where TCName in (Select distinct(TCName) FROM [dbo].[TCDetails] ) 
AND CallDate is NOT NULL GROUP BY TCName
) T ON S.TCName=T.TCName;

运算符(operator):

+--------------------------------------------------------------+
|TCName  |  BLDOS   | CollectedAmount  | TargetAmount| count   |
+--------------------------------------------------------------+
| Aarti  |  81234.2 |    1678.76       |  789065     |   231   |
| Dev    |  181234.2|     678.76       |  2389065    |   123   |
+--------------------------------------------------------------+