有17个子查询用例语句，有更好的方法吗

Question

我有一个运行速度很慢的非常恶心的查询：

SELECT TOP 10 d.AccountID, COUNT(mp.PhoneTypeID) AS Num_Phones,
sum(case when mp.PhoneTypeID = 1 then 1 else 0 end) AS type1,
sum(case when mp.PhoneTypeID = 2 then 1 else 0 end) AS type2,
sum(case when mp.PhoneTypeID = 3 then 1 else 0 end) AS type3,
sum(case when mp.PhoneTypeID = 4 then 1 else 0 end) AS type4,
sum(case when mp.PhoneTypeID = 5 then 1 else 0 end) AS type5,
sum(case when mp.PhoneTypeID = 6 then 1 else 0 end) AS type6,
sum(case when mp.PhoneTypeID = 7 then 1 else 0 end) AS type7,
sum(case when mp.PhoneTypeID = 8 then 1 else 0 end) AS type8,
sum(case when mp.PhoneTypeID = 9 then 1 else 0 end) AS type9,
sum(case when mp.PhoneTypeID = 10 then 1 else 0 end) AS type10,
sum(case when mp.PhoneTypeID = 11 then 1 else 0 end) AS type11,
sum(case when mp.PhoneTypeID = 12 then 1 else 0 end) AS type12,
sum(case when mp.PhoneTypeID = 13 then 1 else 0 end) AS type13,
sum(case when mp.PhoneTypeID = 14 then 1 else 0 end) AS type14,
sum(case when mp.PhoneTypeID = 15 then 1 else 0 end) AS type15,
sum(case when mp.PhoneTypeID = 16 then 1 else 0 end) AS type16,
sum(case when mp.PhoneTypeID = 17 then 1 else 0 end) AS type17
INTO #phoneshit
FROM #days d
INNER JOIN note.phone np on d.AccountID = np.AccountID INNER JOIN main.phone 
mp ON np.PhoneID = mp.PhoneID
GROUP BY d.AccountID

我想不出任何其他方法可以像这样将手机类型过滤到新的列中，更不用说更快的了。有人知道吗？在这种情况下，最好的方法是什么？

Answer 1

对于应该考虑在#days临时表的列AccountID上添加索引的性能问题，联接应该快得多。

为了"可读性"，您可以在PhoneTypeID上使用PIVOT

;with
x as (
select AccountID, PhoneTypeID
from #days d
join note.phone np on d.AccountID = np.AccountID
join main.phone mp ON np.PhoneID = mp.PhoneID
),
p as (
select *
from x
pivot ( count(PhoneTypeID) for PhoneTypeID in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17])) p
),
n as (
select AccountID, COUNT(PhoneTypeID) Num_Phones
from x
group by AccountID
)   
select p.AccountID, n.Num_Phones, [1] type1, [2] type2, [3] type3, [4] type4, [5] type5, [6] type6, [7] type7 , [8] type8, [9] type9, [10] type10, [11] type11, [12] type12, [13] type13, [14] type14, [15] type15, [16] type16, [17] type17
into #phoneshit
from p
join n on p.AccountID = n.AccountID