mysql - 如何从mysql数据库中检索一组记录的数量？

Question

我有一个表，其中包含全名和姓氏的多条记录。

---------------------------
id | name       | lastname
---------------------------
 1 | A smith    | smith
 2 | B smith    | smith
 3 | c smith    | smith
 4 | A josh     | josh
 5 | B josh     | josh
 6 | C josh     | josh
 7 | D josh     | josh
 8 | A white    | white
 9 | D white    | white
 10| z white    | white

如此如此....超过10万条记录。现在我想做的是检索每个姓氏的最新 7 条记录，最多 9 个姓氏。我有 500 个姓氏，但我只想要最新的 9 个姓氏。在我的应用程序中，“最新”意味着“id 列的最大值。”

这是我尝试执行的命令，但当我执行它时。我没有收到服务器的任何响应。发生这种情况是因为数据库大小，并且我的命令花费了很多时间。它只是让我等待:

SELECT * FROM `queue` s WHERE ( SELECT COUNT(*) FROM `queue` f WHERE f.lastname = s.lastname AND f.id >= s.id LIMIT 0 , 7) <=7

有人可以建议我更好的方法来实现我的目标吗？

Answer 1

让我们从基础开始构建它。

您的第一步是创建一个子查询来获取最新的九个姓氏 ( http://sqlfiddle.com/#!9/aee62e/19/0 )。我指的是具有最高 id 值的姓氏。

                        SELECT lastname, MAX(id) namerank
                          FROM t
                         GROUP BY lastname
                         ORDER BY MAX(id) DESC
                         LIMIT 9

而且，在 MySQL 中，这是最简单的部分。现在，您需要检索每个选定姓氏的排名最高(最大 id)的七个行。首先，您可以执行此操作来获取所选姓氏的所有记录，按 ID 降序排列。 (http://sqlfiddle.com/#!9/aee62e/18/0)。

              SELECT t.*, namerank
                FROM t
                JOIN (          
                        SELECT lastname, MAX(id) namerank
                          FROM t
                         GROUP BY lastname
                         ORDER BY MAX(id) DESC
                         LIMIT 9
                      ) h ON t.lastname = h.lastname
                ORDER BY t.lastname, t.id DESC

这是正确的，但包含太多行。接下来，我们需要获取每个 lastname 行的排名。排名越低意味着 id 值越高。这是 MySQL 中令人讨厌的 hack。 (很讨厌，因为它将局部变量的过程操作与 SQL 固有的声明性性质混合在一起。)( http://sqlfiddle.com/#!9/aee62e/17/0 )

     SELECT IF(detail.lastname = @prev_lastname, @rank := @rank+1, @rank :=1)  rank, 
            namerank,
            @prev_lastname := detail.lastname lastname,
            id,
            name
       FROM (
              SELECT t.*, namerank
                FROM t
                JOIN (          
                        SELECT lastname, MAX(id) namerank
                          FROM t
                         GROUP BY lastname
                         ORDER BY MAX(id) DESC
                         LIMIT 9
                      ) h ON t.lastname = h.lastname
                ORDER BY t.lastname, t.id DESC
            ) detail
       JOIN (SELECT @rank := 0, @prev_lastname := '') initializer

最后，我们需要将整个困惑包裹在外部查询中，以挑选出每个 lastname 值的排名最高的七个行。 (http://sqlfiddle.com/#!9/aee62e/16/0)

SELECT *
  FROM (

         SELECT IF(detail.lastname = @prev_lastname, @rank := @rank+1, @rank :=1)  rank, 
                namerank,
                @prev_lastname := detail.lastname lastname,
                id,
                name
           FROM (
                  SELECT t.*, namerank
                    FROM t
                    JOIN (          
                            SELECT lastname, MAX(id) namerank
                              FROM t
                             GROUP BY lastname
                             ORDER BY MAX(id) DESC
                             LIMIT 9
                          ) h ON t.lastname = h.lastname
                    ORDER BY t.lastname, t.id DESC
                ) detail
           JOIN (SELECT @rank := 0, @prev_lastname := '') initializer
         ) ranked
 WHERE rank <= 7
ORDER BY namerank DESC, rank

我认为描述您的需求和此解决方案的复杂性的技术术语是“毛团”。它确实将结构化放入结构化查询语言中。