我试图为每个用户选择一行.我不关心我得到的图像.此查询适用于
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
解决方法
到目前为止使用MIN / MAX聚合或ROW_NUMBER发布的解决方案可能效率最低(取决于数据分布),因为在每组选择一个之前,它们通常必须检查所有匹配的行.
使用AdventureWorks sample database来说明,以下查询都为每个ProductID从Transaction History表中选择一个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,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;
有关任何汇总的详细信息,请参阅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可能是最有效的.
