备选方案1-将包括所有绑定的行。如果给定线程的所有行的HitCount都为null,则将不包含行:
SELECT Thread, Function, HitCount
FROM (SELECT Thread, Function, HitCount,
MAX(HitCount) over (PARTITION BY Thread) as MaxHitCount
FROM Samples
WHERE FunctionId NOT IN
(SELECT CalleeId FROM Callers)) t
WHERE HitCount = MaxHitCount
ORDER BY ThreadId, HitCount DESC
备选方案2-将包括所有绑定的行。如果给定线程的HitCount不为空,则没有行,则将返回该线程的所有行:
SELECT Thread, Function, HitCount
FROM (SELECT Thread, Function, HitCount,
RANK() over (PARTITION BY Thread ORDER BY HitCount DESC) as R
FROM Samples
WHERE FunctionId NOT IN
(SELECT CalleeId FROM Callers)) t
WHERE R = 1
ORDER BY ThreadId, HitCount DESC
备选方案3-在出现平局的情况下将不确定地选择一行,并丢弃其他行。如果给定线程的所有行的空HitCount将包括一行
SELECT Thread, Function, HitCount
FROM (SELECT Thread, Function, HitCount,
ROW_NUMBER() over (PARTITION BY Thread ORDER BY HitCount DESC) as R
FROM Samples
WHERE FunctionId NOT IN
(SELECT CalleeId FROM Callers)) t
WHERE R = 1
ORDER BY ThreadId, HitCount DESC
替代4和5-如果窗口函数不可用,则使用较旧的构造,并表示比使用联接更干净。优先考虑基准测试。两者都返回参与平局的所有行。当非null值不可用于HitCount时,备选方案4将HitCount为null。备选方案5将不会返回HitCount为null的行。
SELECT *
FROM Samples s1
WHERE FunctionId NOT IN
(SELECT CalleeId FROM Callers)
AND NOT EXISTS
(SELECT *
FROM Samples s2
WHERE s1.FunctionId = s2.FunctionId
AND s1.HitCount < s2.HitCount)
ORDER BY ThreadId, HitCount DESC
SELECT *
FROM Samples s1
WHERE FunctionId NOT IN
(SELECT CalleeId FROM Callers)
AND HitCount =
(SELECT MAX(HitCount)
FROM Samples s2
WHERE s1.FunctionId = s2.FunctionId)
ORDER BY ThreadId, HitCount DESC