Insert Into Temp表多个select语句

Question

下面有2个选择语句，我想显示如下输出。目前，我无法将第一个查询与第二个查询连接。

fanme   lname  JobTitle   AcceptanceRate
Jim    Cary     Manager    0.666666

select fname, lname, JobTitle
from [User]
where userID=8

Select convert(decimal, x.QuoteAccepted) / y.AllQuotes as  AcceptanceRate 
from  (
select count(*) as QuoteAccepted
from Quote
where ProjectManager_UserID=8 and QuoteStatusID=6
) x
join (
select count(*) as AllQuotes
from Quote
where ProjectManager_UserID=8
) 
y on 1=1 

我尝试创建临时表并插入，但它一直显示错误:

列名或提供的值数量与表定义不匹配。

是否有可能做到这一点?

Answer 1

您可以使用条件聚合:

select u.*,
(select avg(case when q.QuoteStatusID = 6 then 1.0 else 0 end) as QuoteAccepted
from Quote q
where q.ProjectManager_UserID = u.userId
) as AcceptanceRate
from users u
where u.UserID = 8

如果您希望所有项目经理都这样做:

select ProjectManager_UserID,
avg(case when QuoteStatusID = 6 then 1.0 else 0 end) as AcceptanceRate
from Quote
group by ProjectManager_UserID;