SQL查询，每组

Question

我有这个表中选择最近5

CREATE TABLE `codes` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
`language_id` int(11) unsigned NOT NULL, 
`title` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
`time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 

LANGUAGE_ID是指记录在不在。 什么语言，我想什么做的是检索5个最近的列表（ORDER BY time_posted DESC LIMIT 5）在每个language_id中记录。我可以在PHP中用一系列不同的SQL查询在循环中做到这一点，但我觉得有一个更简单的方法。

我得拿一本关于SQL的书，哈哈。

谢谢。

Answer 1

下面是如何解决这个“每组顶部N”类型的查询在MySQL：

SELECT c1.* 
FROM codes c1 
LEFT OUTER JOIN codes c2 
    ON (c1.language_id = c2.language_id AND c1.time_posted < c2.time_posted) 
GROUP BY c1.id 
HAVING COUNT(*) < 5; 

参见“How do I select multiple items from each group in a mysql query?”

Answer 2

这里是一个很好的解决方案我刚刚发现。

选择TOP N行的每​​个组 阿尼·罗兰，2008年3月13日

有每个类别多行，并有一个愿望， 选择每个类别的只有前两（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 

所需的输出是：

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 

有几种方法来完成所期望的输出。 此演示提供的SQL Server 2005/SQL Server 2008中， ，然后SQL Server中的解决方案2000

两种解决方案

创建示例数据

-- Suppress data loading messages 
SET NOCOUNT ON 

-- Create Sample Data using a Table Variable 
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中溶液的溶液

--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服务器使用CTE 2008解决方案（添加人：雅各塞巴斯蒂安）

-- 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解决方案

--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 

来源：Select the TOP n Rows For Each Group