子查询上的分组MAX（）

Question

我正在尝试计算表中的最大值以及该表中的其他值。但是，我为此做的表不是“真实”表，而是由子查询生成的表。这给我带来了问题，因为我认为如果不重新指定整个子查询就无法两次加入它。

我目前有一个SQL Server解决方案，使用ROW_NUMBER() OVER (PARTITION BY providerId ORDER BY partnershipSetScore DESC) rnk，但我正在寻找与DBMS无关的版本，因为该项目的单元测试在不具有此功能的Sqlite
DB中运行。

如果它们有用，这是架构和我的SQL Server特定查询：

Course：

• int id
• varchar name
• int schoolId

School：

• int id
• varchar name

Partnership：

• int id
• varchar partnershipName

SchoolPartnership：

• int id
• int schoolId
• int partnershipId

这是查询：

SELECT
    schoolId,
    partnershipId AS bestPartnershipSetId,
    partnershipScore AS bestPartnershipScore
FROM
(
    SELECT
        pp.schoolId,
        partnershipScores.partnershipId,
        partnershipScores.partnershipScore,
        ROW_NUMBER() OVER (PARTITION BY schoolId ORDER BY partnershipScore DESC) rnk
    FROM schoolPartnership pp
    INNER JOIN (
        SELECT
            pp.partnershipId,
            (
                (CASE WHEN SUM(CASE WHEN c.name LIKE '%French%' THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END)
                + (CASE WHEN SUM(CASE WHEN c.name LIKE '%History%' THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END)
            ) AS partnershipScore
        FROM schoolPartnership pp
        INNER JOIN course c ON c.schoolId = pp.schoolId
        GROUP BY partnershipId
    ) AS partnershipScores ON partnershipScores.partnershipId = pp.partnershipId
) AS schoolPartnershipScores
WHERE rnk = 1

Answer 1

我没有找到解决方案（除了重复子查询，这是我一直在努力避免的事情），因此我只为PHP中的每个partnershipScore标识了MAX行，并丢弃了其他任何行。这不是一个理想的解决方案，但是由于我需要跨平台方法，因此没有太多其他选择可供我选择。