两个选择结果的微积分(乘法)

Question

我正试图将从一个唯一查询的SELECT语句中得到的两个数字相乘。我想得到提供者的数量和提议的数量(我所做的查询显示了这一点(，并在同一行上相乘(我做不到(。

我做了一个非常简单的例子向您展示(代码如下(：演示FIDDLE

创建2个在2个部门工作的提供商：

CREATE TABLE ##Provider
(
id INT,
p_name VARCHAR(50),
id_dep INT
)
INSERT INTO ##Provider (id, p_name, id_dep) VALUES 
(1, 'toto', 10),
(2, 'toto', 11),
(3, 'tata', 9);

创建4个关于2个部门的提案：

CREATE TABLE ##Proposal
(
id INT,
c_name VARCHAR(50),
id_dep INT
)
INSERT INTO ##Proposal (id, c_name, id_dep) VALUES 
(1, 'propA', 10),
(2, 'propB', 09),
(3, 'propC', 10),
(4, 'propD', 10);

创建部门表：

CREATE TABLE ##Department
(
id INT,
d_name VARCHAR(50)
)
INSERT INTO ##Department (id, d_name) VALUES 
(9, 'dep9')
,(10, 'dep10')
,(11, 'dep11');

在这里，我可以按部门显示提供商和提案的数量(真正的查询要复杂得多，所以我想保留2个子请求(：

select 
id,
d_name,
nb_provider = (
SELECT COUNT(DISTINCT Id)
FROM ##Provider p
WHERE p.id_dep = dep.id
),
nb_proposal = (
SELECT COUNT(DISTINCT Id)
FROM ##Proposal pp
WHERE pp.id_dep = dep.id
)
from ##Department dep
WHERE dep.id = 10

但我无法显示这两个数字的计算结果：

select 
id,
d_name,
nb_provider = (
SELECT COUNT(DISTINCT Id)
FROM ##Provider p
WHERE p.id_dep = dep.id
),
nb_proposal = (
SELECT COUNT(DISTINCT Id)
FROM ##Proposal pp
WHERE pp.id_dep = dep.id
),
calcul = (nb_provider * nb_proposal)  --> DOESN'T WORK
from ##Department dep
WHERE dep.id = 10

我没有尝试太多，因为我不确定这是否可能。。。也许我应该使用UNION？

Answer 1

我建议横向连接：

select 
d.id,
d.d_name,
p.nb_provider,
pp.nb_proposal
(p.nb_provider * pp.nb_proposal) calcul
from ##department d
outer apply (
select count(distinct id) nb_provider
from ##provider p
where p.id_dep = d.id
) p
outer apply (
select count(distinct id) nb_proposal
from ##proposal pp
where pp.id_dep = d.id
) pp
where d.id = 10