sql - 子查询上的 Groupwise MAX()

Question

我正在尝试计算返回表中的最大值以及该表中的其他值。但是，我这样做的表不是“真正的”表，它是由子查询生成的。这给我带来了问题，因为我不认为我可以在不重新指定整个子查询的情况下加入它两次。

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

这是架构和我的 SQL Server 特定查询，以防它们有用:

类(class):

• 内部编号
• 可变字符名称
• int schoolId

学校:

• 内部编号
• 可变字符名称

合作伙伴关系:

• 内部编号
• varchar 合作伙伴名称

学校合作伙伴关系:

• 内部编号
• 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

如果您需要有关我正在努力实现的目标的更多信息，请参阅 Custom sorting algorithm for a large amount of data :此查询将是一个较大查询的子查询，该查询按最合适的合作伙伴关系对学校进行排序。

Answer 1

也许，在谈到两次加入子查询时，你的脑海中已经浮现出这样的技巧:

SELECT a.*
FROM atable a
INNER JOIN (
  SELECT
    col1,
    MAX(col2) AS max_col2
  FROM atable
  GROUP BY col1
) m
ON a.col1 = m.col1 AND a.col2 = m.max_col2
;

作为一种与 DBMS 无关的方式(至少，一种同时在 SQL Server 和 SQLite 中工作的方式)来完成这项工作本来是非常好的如果这是关于单个表的.

相反，您有一个子查询。但是，我看不出有其他方法可以完成您所要求的。因此，在这种情况下，我可以为您看到两个选项(一个可能不适用于您的特定情况，但通常仍然是一个选项):

1. 做您想避免的事情，即专门为查找每个组的聚合值而复制子查询，然后将其连接回同一个子查询，如上所示。

2. 临时保留子查询的结果，然后将上述技术应用于临时结果集。

第一个选项确实不是很有吸引力，更何况第二个选项可能会奏效。

第二个选项的一个问题是临时数据集在 SQL Server 和 SQLite 中的实现方式不同。在 SQLite 中，您为此使用 CREATE TEMPORARY TABLE 语句。 SQL Server 不支持 CREATE TABLE 语句上下文中的 TEMPORARY 关键字，而是在开头使用特殊字符 (#)表示该表实际上是一个临时表。

因此，我能看到的唯一解决方法是使用普通 表作为临时存储。您可以创建一次，然后在每次运行查询时删除其内容，就在插入临时结果集之前:

DELETE FROM TempTable;
INSERT INTO TempTable (
  schoolId,
  bestPartnershipSetId,
  bestPartnershipScore
)
SELECT
  pp.schoolId,
  partnershipScores.partnershipId,
  partnershipScores.partnershipScore,
FROM
  ...
;
SELECT ...
FROM TempTable
...
;

或者您可以在每次运行查询时创建和删除它:

CREATE TABLE TempTable (
  ...
);
INSERT INTO TempTable (...)
SELECT ...
FROM ...
;
SELECT ...
FROM TempTable
...
;
DROP TABLE TempTable;

请注意，像这样使用普通表作为临时存储在 SQL Server 中不是并发友好的。如果这可能会造成问题，您可能不得不放弃此选项并以第一个选项结束。 (但这可能是您想要独立于平台的解决方案时必须支付的成本，尤其是当平台与 SQL Server 和 SQLite 一样不同时。)