SQL Server-每个FK SELECT TOP 5行 [英] SQL Server - SELECT TOP 5 rows for each FK

问题描述

我有以下查询，该查询查找与搜索匹配的TOP 5 Products .每个商品与一个 Shop

I've got the following query, that looks up the TOP 5 Products matching the search. Each Product is associated with a Shop

选择前5位*从产品 p，商店的WHERE p.ShopId = s.ShopId和p.ProductName喜欢 '％christmas％'

SELECT TOP 5 * FROM Products p, Shops s WHERE p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%'

我需要扩展它，以便它返回给我每个 商店中的前5个产品.

I need to extend this so that it returns me the TOP 5 Products in each Shop.

有人可以让我知道如何修改查询以实现这一目标吗? -即在每个商店中选择与％christmas％"匹配的TOP 5产品(而不是当前显示所有 all 商店与％chrismas％"匹配的TOP 5产品) ).

Could anyone let me know how the query could be modified to achieve this? - i.e. choose the TOP 5 products matching "%christmas%" in each shop (rather than the current which shows the TOP 5 products matching "%chrismas%" across all shops).

推荐答案

您实际上是缺少ORDER BY才能使TOP有意义，或者您缺少任何基于ROW_NUMBER的解决方案(需要ORDER BY).

You're actually missing an ORDER BY to make the TOP meaningful, or any solution based on ROW_NUMBER which requires an ORDER BY.

SELECT
    *
FROM
    Shops s 
CROSS APPLY (
    SELECT TOP 5
        *
    FROM
        Products p
    WHERE
        p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%'
    ORDER BY --added on edit
        ???
) X

这篇关于SQL Server-每个FK SELECT TOP 5行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！