I've got the following query, that looks up the TOP 5 Products matching the search. Each Product is associated with a Shop
我有以下查询,查找与搜索匹配的TOP 5产品。每个产品都与商店相关联
SELECT TOP 5 * FROM Products p, Shops s WHERE p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%'
SELECT TOP 5 * FROM Products p,Shops s WHERE p.ShopId = s.ShopId AND p.ProductName LIKE'%christmas%'
I need to extend this so that it returns me the TOP 5 Products in each Shop.
我需要对此进行扩展,以便在每个商店中返回前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).
任何人都可以让我知道如何修改查询来实现这一目标吗? - 即选择每个商店中匹配“%christmas%”的TOP 5产品(而不是显示所有商店中匹配“%chrismas%”的TOP 5产品的当前产品)。
4 个解决方案
#1
10
You're actually missing an ORDER BY to make the TOP meaningful, or any solution based on ROW_NUMBER which requires an ORDER BY.
你实际上错过了ORDER BY以使TOP有意义,或者任何基于ROW_NUMBER的解决方案需要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
#2
3
Try this:
select * from (
select *, rn = row_number() over (partition by s.ShopId order by p.ProductName)
from Products p, Shops s
where p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%'
) a where a.rn <= 5
#3
1
Try this
SELECT DISTINCT
A.Product_Group_code
,B.Sub_Product_Group_code
,A.Product_code
,A.Product_name
FROM dbo.A A
LEFT JOIN dbo.B B
ON A.Product_code = B.Product_code
WHERE B.Product_code IN
(
SELECT TOP 5 E.Product_code
FROM dbo.A D
LEFT JOIN dbo.B E
ON D.Product_code = E.Product_code
WHERE E.Sub_Product_Group_code = B.Sub_Product_Group_code
)
AND B.Sub_Product_Group_code IS NOT NULL
ORDER BY B.Sub_Product_Group_code,A.Product_name
#4
1
Here is a great solution I just found.
这是我刚刚找到的一个很好的解决方案。
Select the TOP n Rows For Each Group Arnie Rowland, March 13, 2008
选择2008年3月13日的每个组的前n行Arnie Rowland
There are Multiple Rows for Each Category, and there is a desire to SELECT ONLY the TOP two (2) Rows per Category by Price. For example, from the following data:
每个类别有多行,并且希望按价格选择每个类别的前两(2)行。例如,从以下数据:
RowID Category ID Description Price
1 Pot A1 Small Saucepan 21.50
2 Pot A2 1 Qt Saucepan 29.95
3 Pot A3 1.5 Qt Saucepan 33.95
4 Pot A4 Double Boiler 39.50
5 Pot A5 Stewpot 49.50
6 Pot A6 Pressure Cooker 79.95
7 Pan B1 8" Pie 6.95
8 Pan B2 8" Sq Cake 7.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
13 Tools T1 14" Spatula 9.95
The desired output is:
所需的输出是:
RowID Category ID Description Price
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
6 Pot A6 Pressure Cooker 79.95
5 Pot A5 Stewpot 49.50
13 Tools T1 14" Spatula 9.95
There are several methods to accomplish the desired output. This demonstration provides a Solution for SQL Server 2005 / SQL Server 2008 , and then a Solution for SQL Server 2000.
有几种方法可以实现所需的输出。此演示提供SQL Server 2005 / SQL Server 2008的解决方案,然后是SQL Server 2000的解决方案。
Create Sample Data for Both Solutions
为两个解决方案创建示例数据
-- Suppress data loading messages
SET NOCOUNT ON
-- Create Sample Data using a Table Varable
DECLARE @MyTable table
( RowID int IDENTITY,
Category varchar(5),
[ID] varchar(5),
[Description] varchar(25),
Price decimal(10,2)
)
-- Load Sample Data
INSERT INTO @MyTable VALUES ( 'Pot', 'A1', 'Small Saucepan', 21.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A2', '1 Qt Saucepan', 29.95 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A3', '1.5 Qt Saucepan', 33.95 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A4', 'Double Boiler', 39.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A5', 'Stewpot', 49.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A6', 'Pressure Cooker', 79.95 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B1', '8"" Pie', 6.95 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B2', '8"" Sq Cake', 7.50 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B3', 'Bundt Cake', 12.50 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B4', '9x12 Brownie', 7.95 )
INSERT INTO @MyTable VALUES ( 'Bowl', 'C1', 'Lg Mixing', 27.50 )
INSERT INTO @MyTable VALUES ( 'Bowl', 'C2', 'Sm Mixing', 17.50 )
INSERT INTO @MyTable VALUES ( 'Tools', 'T1', '14"" Spatula', 9.95 )
Return to Top
SQL Server 2005 / SQL Server 2008 Solution
SQL Server 2005 / SQL Server 2008解决方案
--Query to Retrieve Desired Data
SELECT
RowID,
Category,
[ID],
[Description],
Price
FROM (SELECT
ROW_NUMBER() OVER ( PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber',
RowID,
Category,
[ID],
[Description],
Price
FROM @MyTable
) dt
WHERE RowNumber <= 2
-- Results
RowID Category ID Description Price
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
6 Pot A6 Pressure Cooker 79.95
5 Pot A5 Stewpot 49.50
13 Tools T1 14" Spatula 9.95
Return to Top
SQL Server 2005 / SQL Server 2008 Solution using a CTE (Added by: Jacob Sebastian)
使用CTE的SQL Server 2005 / SQL Server 2008解决方案(添加者:Jacob Sebastian)
-- Define a CTE with the name "dt"
;WITH dt AS (
SELECT
ROW_NUMBER() OVER ( PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber',
RowID,
Category,
[ID],
[Description],
Price
FROM @MyTable
)
-- and select the data from the CTE
SELECT
RowID,
Category,
[ID],
[Description],
Price
FROM dt
WHERE RowNumber <= 2
-- Results
RowID Category ID Description Price
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
6 Pot A6 Pressure Cooker 79.95
5 Pot A5 Stewpot 49.50
13 Tools T1 14" Spatula 9.95
Return to Top
SQL 2000 Solution
SQL 2000解决方案
--Query to Retrieve Desired Data
SELECT DISTINCT
RowID,
Category,
[ID],
[Description],
Price
FROM @MyTable t1
WHERE RowID IN (SELECT TOP 2
RowID
FROM @MyTable t2
WHERE t2.Category = t1.Category
ORDER BY Price DESC
)
ORDER BY
Category,
Price DESC
-- Results
RowID Category ID Description Price
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
6 Pot A6 Pressure Cooker 79.95
5 Pot A5 Stewpot 49.50
13 Tools T1 14" Spatula 9.95
From: Select the TOP n Rows For Each Group
发件人:为每个组选择TOP n行
#1
10
You're actually missing an ORDER BY to make the TOP meaningful, or any solution based on ROW_NUMBER which requires an ORDER BY.
你实际上错过了ORDER BY以使TOP有意义,或者任何基于ROW_NUMBER的解决方案需要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
#2
3
Try this:
select * from (
select *, rn = row_number() over (partition by s.ShopId order by p.ProductName)
from Products p, Shops s
where p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%'
) a where a.rn <= 5
#3
1
Try this
SELECT DISTINCT
A.Product_Group_code
,B.Sub_Product_Group_code
,A.Product_code
,A.Product_name
FROM dbo.A A
LEFT JOIN dbo.B B
ON A.Product_code = B.Product_code
WHERE B.Product_code IN
(
SELECT TOP 5 E.Product_code
FROM dbo.A D
LEFT JOIN dbo.B E
ON D.Product_code = E.Product_code
WHERE E.Sub_Product_Group_code = B.Sub_Product_Group_code
)
AND B.Sub_Product_Group_code IS NOT NULL
ORDER BY B.Sub_Product_Group_code,A.Product_name
#4
1
Here is a great solution I just found.
这是我刚刚找到的一个很好的解决方案。
Select the TOP n Rows For Each Group Arnie Rowland, March 13, 2008
选择2008年3月13日的每个组的前n行Arnie Rowland
There are Multiple Rows for Each Category, and there is a desire to SELECT ONLY the TOP two (2) Rows per Category by Price. For example, from the following data:
每个类别有多行,并且希望按价格选择每个类别的前两(2)行。例如,从以下数据:
RowID Category ID Description Price
1 Pot A1 Small Saucepan 21.50
2 Pot A2 1 Qt Saucepan 29.95
3 Pot A3 1.5 Qt Saucepan 33.95
4 Pot A4 Double Boiler 39.50
5 Pot A5 Stewpot 49.50
6 Pot A6 Pressure Cooker 79.95
7 Pan B1 8" Pie 6.95
8 Pan B2 8" Sq Cake 7.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
13 Tools T1 14" Spatula 9.95
The desired output is:
所需的输出是:
RowID Category ID Description Price
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
6 Pot A6 Pressure Cooker 79.95
5 Pot A5 Stewpot 49.50
13 Tools T1 14" Spatula 9.95
There are several methods to accomplish the desired output. This demonstration provides a Solution for SQL Server 2005 / SQL Server 2008 , and then a Solution for SQL Server 2000.
有几种方法可以实现所需的输出。此演示提供SQL Server 2005 / SQL Server 2008的解决方案,然后是SQL Server 2000的解决方案。
Create Sample Data for Both Solutions
为两个解决方案创建示例数据
-- Suppress data loading messages
SET NOCOUNT ON
-- Create Sample Data using a Table Varable
DECLARE @MyTable table
( RowID int IDENTITY,
Category varchar(5),
[ID] varchar(5),
[Description] varchar(25),
Price decimal(10,2)
)
-- Load Sample Data
INSERT INTO @MyTable VALUES ( 'Pot', 'A1', 'Small Saucepan', 21.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A2', '1 Qt Saucepan', 29.95 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A3', '1.5 Qt Saucepan', 33.95 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A4', 'Double Boiler', 39.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A5', 'Stewpot', 49.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A6', 'Pressure Cooker', 79.95 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B1', '8"" Pie', 6.95 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B2', '8"" Sq Cake', 7.50 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B3', 'Bundt Cake', 12.50 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B4', '9x12 Brownie', 7.95 )
INSERT INTO @MyTable VALUES ( 'Bowl', 'C1', 'Lg Mixing', 27.50 )
INSERT INTO @MyTable VALUES ( 'Bowl', 'C2', 'Sm Mixing', 17.50 )
INSERT INTO @MyTable VALUES ( 'Tools', 'T1', '14"" Spatula', 9.95 )
Return to Top
SQL Server 2005 / SQL Server 2008 Solution
SQL Server 2005 / SQL Server 2008解决方案
--Query to Retrieve Desired Data
SELECT
RowID,
Category,
[ID],
[Description],
Price
FROM (SELECT
ROW_NUMBER() OVER ( PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber',
RowID,
Category,
[ID],
[Description],
Price
FROM @MyTable
) dt
WHERE RowNumber <= 2
-- Results
RowID Category ID Description Price
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
6 Pot A6 Pressure Cooker 79.95
5 Pot A5 Stewpot 49.50
13 Tools T1 14" Spatula 9.95
Return to Top
SQL Server 2005 / SQL Server 2008 Solution using a CTE (Added by: Jacob Sebastian)
使用CTE的SQL Server 2005 / SQL Server 2008解决方案(添加者:Jacob Sebastian)
-- Define a CTE with the name "dt"
;WITH dt AS (
SELECT
ROW_NUMBER() OVER ( PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber',
RowID,
Category,
[ID],
[Description],
Price
FROM @MyTable
)
-- and select the data from the CTE
SELECT
RowID,
Category,
[ID],
[Description],
Price
FROM dt
WHERE RowNumber <= 2
-- Results
RowID Category ID Description Price
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
6 Pot A6 Pressure Cooker 79.95
5 Pot A5 Stewpot 49.50
13 Tools T1 14" Spatula 9.95
Return to Top
SQL 2000 Solution
SQL 2000解决方案
--Query to Retrieve Desired Data
SELECT DISTINCT
RowID,
Category,
[ID],
[Description],
Price
FROM @MyTable t1
WHERE RowID IN (SELECT TOP 2
RowID
FROM @MyTable t2
WHERE t2.Category = t1.Category
ORDER BY Price DESC
)
ORDER BY
Category,
Price DESC
-- Results
RowID Category ID Description Price
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
6 Pot A6 Pressure Cooker 79.95
5 Pot A5 Stewpot 49.50
13 Tools T1 14" Spatula 9.95
From: Select the TOP n Rows For Each Group
发件人:为每个组选择TOP n行