SQL：为每个唯一键选择最大值？

一尘不染

SQL：为每个唯一键选择最大值？

sql

抱歉，我不确定该如何表述，而且我对SQL真的不是很好。数据库引擎i SQL Server Compact。我目前有这个查询：

SELECT *
FROM Samples
WHERE FunctionId NOT IN
(SELECT CalleeId FROM Callers)
ORDER BY ThreadId, HitCount DESC

这给了我：

ThreadId   Function  HitCount
       1        164      6945
       1       3817         1
       4       1328      7053

现在，我只希望结果具有每个Thread唯一值的最大命中计数。换句话说，应该删除第二行。我不确定如何实现这一目标。

[EDIT]如果有帮助，这是同一查询的另一种形式：

SELECT *
FROM Samples s1
LEFT OUTER JOIN Callers c1
    ON s1.ThreadId = c1.ThreadId AND s1.FunctionId = c1.CalleeId
WHERE c1.ThreadId IS NULL
ORDER BY ThreadId

[编辑]我最终进行了模式更改，以避免这样做，因为建议的查询看起来相当昂贵。感谢您的所有帮助。

阅读 201

2021-03-17

共1个答案

一尘不染

SQL Server Compact是否支持窗口函数？

备选方案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

2021-03-17