如何将两个SELECT语句添加到一起

Question

我有两个单独的查询，它们收集相关数据并计算电信服务提供的两种不同服务(短信和电话计划，数据计划)的数量，两个查询都能够从指定的字段中获取数据，但我希望使用一个查询完成所有操作。

SELECT a.OperatorName, count(b.planName) as 'Plans'
FROM dbo.Operator a INNER JOIN dbo.Plan b
on a.OperatorID = b.OperatorID
GROUP BY a.OperatorName
SELECT a.OperatorName, count(B.intplanName) as 'Plans'
FROM dbo.Operator AS a
LEFT JOIN
InternetPlan as B
ON a.OperatorID = B.OperatorID
GROUP BY a.OperatorName

输出(第一):

OperatorName           | Plans
Verizon                   6
Google                    10
SomethineElse             4

输出(二):

OperatorName           | Plans
Verizon                   2
Google                    9
SomethineElse             9

所需输出

OperatorName           | Plans
Verizon                   9
Google                    19
SomethineElse             13

这两个查询都返回相关信息，当我试图组合它们时，问题就开始了。

下面指定的代码不工作，我不确定它在哪里不能正常工作以及为什么。

SELECT a.OperatorName, count(b.planName) as 'Plan'
FROM dbo.Operator AS a
INNER JOIN 
Plan as b
on a.operatorID = b.operatorID
INNER JOIN
InternetPlan as c
on c.operatorID = b.operatorID
GROUP BY a.OperatorName

上面的查询输出如下:

OperatorName           | Plans
Verizon                   12
Google                    90
SomethineElse             36

应该是

OperatorName           | Plans
Verizon                   9
Google                    19
SomethineElse             13

Answer 1

您应该像这样从内部查询中删除计数:

SELECT tem.Plan, count(Plan) FROM
(
SELECT a.OperatorName, b.planName as 'Plan'
FROM dbo.Operator a INNER JOIN dbo.Plan b
on a.OperatorID = b.OperatorID
UNION ALL
SELECT a.OperatorName, b.intplanName as 'Plan'
FROM dbo.Operator a INNER JOIN dbo.InternetoPlan b
on a.OperatorID = b.OperatorID
) AS tem
GROUP BY Plan
ORDER BY Plan

Answer 2

基于@sagi的答案，您可以将Operator拉到联合的外部，以避免对其进行两次查询

SELECT
o.OperatorName,
COUNT(*)
FROM dbo.Operator o
INNER JOIN 
(
SELECT p.OperatorID, p.planName as Plan
FROM dbo.Plan p
UNION ALL
SELECT ip.OperatorID, ip.intplanName as Plan
FROM dbo.InternetoPlan ip
) AS p ON p.OperatorID = o.OperatorID
GROUP BY o.OperatorID, o.OperatorName
ORDER BY o.OperatorName;

Answer 3

每个运营商您想要获得计划计数和互联网计划计数。因此，从操作符表中选择并连接两个计数:

select o.operatorname, coalesce(p.cnt, 0) + coalesce(ip.cnt, 0) as plans
from dbo.operator o
left join
(
select operatorid, count(*) as cnt
from dbo.plan
group by operatorid
) p on p.operatorid = o.operatorid
left join
(
select operatorid, count(*) as cnt
from internetplan
group by operatorid
) ip on ip.operatorid = o.operatorid
order by o.operatorname;

对于SELECT子句中的子查询或侧连接(OUTER APPLY)也可以实现相同的目的。