sql - GROUP BY 一列；为另一个选择任意值

Question

我正在尝试为每个用户选择一行。我不在乎得到哪个图像。此查询在 MySQL 中有效，但在 SQL Server 中无效:

SELECT user.id, (images.path + images.name) as 'image_path'
FROM users
JOIN images ON images.user_id = users.id
GROUP BY users.id

Answer 1

到目前为止发布的使用 MIN/MAX 聚合或 ROW_NUMBER 的解决方案可能不是最有效的(取决于数据分布)，因为它们通常必须检查所有在每组选择一个之前匹配行。

使用AdventureWorks sample database为了说明这一点，以下查询都从事务历史记录表中为每个 ProductID 选择一个 TransactionType 和 ReferenceOrderID:

使用 MIN/MAX 聚合

SELECT
    p.ProductID,
    MIN(th.TransactionType + STR(th.ReferenceOrderID, 11))
FROM Production.Product AS p
INNER JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.ProductID;

使用ROW_NUMBER

WITH x AS 
(
    SELECT 
        th.ProductID, 
        th.TransactionType, 
        th.ReferenceOrderID,
        rn = ROW_NUMBER() OVER (PARTITION BY th.ProductID ORDER BY (SELECT NULL))
    FROM Production.TransactionHistory AS th
)
SELECT
    p.ProductID,
    x.TransactionType,
    x.ReferenceOrderID
FROM Production.Product AS p
INNER JOIN x ON x.ProductID = p.ProductID
WHERE
    x.rn = 1
OPTION (MAXDOP 1);

使用仅限内部的 ANY 聚合

SELECT
    q.ProductID, 
    q.TransactionType, 
    q.ReferenceOrderID 
FROM 
(
    SELECT 
        p.ProductID, 
        th.TransactionType, 
        th.ReferenceOrderID,
        rn = ROW_NUMBER() OVER (
            PARTITION BY p.ProductID 
            ORDER BY p.ProductID)
    FROM Production.Product AS p
    JOIN Production.TransactionHistory AS th ON p.ProductID = th.ProductID
) AS q
WHERE
    q.rn = 1;

有关 ANY 聚合的详细信息，请参阅 this blog post .

使用具有非确定性 TOP 的相关子查询

SELECT p.ProductID,
    (
    -- No ORDER BY, so could be any row
    SELECT TOP (1) 
        th.TransactionType + STR( th.ReferenceOrderID, 11)
    FROM Production.TransactionHistory AS th WITH (FORCESEEK) 
    WHERE
        th.ProductID = p.ProductID
    )
FROM Production.Product AS p;

将CROSS APPLY与TOP (1)结合使用

上一个查询需要串联，对于没有交易历史记录的产品返回 NULL。将 CROSS APPLY 与 TOP 结合使用可以解决这两个问题:

SELECT
    p.Name, 
    ca.TransactionType,
    ca.ReferenceOrderID
FROM Production.Product AS p
CROSS APPLY
(
    SELECT TOP (1) 
        th.TransactionType,
        th.ReferenceOrderID
    FROM Production.TransactionHistory AS th WITH (FORCESEEK) 
    WHERE 
        th.ProductID = p.ProductID
) AS ca;

通过最佳索引，如果每个用户通常拥有许多图像，APPLY 可能是最有效的。