如何从组中的mysql数据库中检索记录数? [英] How to retrieve number of records from mysql database in a group?

问题描述

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

I have a table with multiple records in it for full name and surname.

---------------------------
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万条记录.现在我想要做的是检索每个姓氏最多 9 个姓氏的最新 7 条记录.我有 500 个姓氏，但我只想要最新的 9 个姓氏.在我的应用程序中，最新"表示id 列的最大值."

And so so....more than 100k records. Now what i want to do is to retrieve latest 7 records for each surname up to 9 surnames. I have 500 surnames but i just want latest 9 surnames.. In my application "latest" means "largest value of id column."

这是我尝试创建的命令，但是当我执行它时.我没有收到服务器的任何响应.这是由于数据库大小而发生的，我的命令需要很多时间.它只是让我等待:

This is the command that i tried to make but when i execute it. i am not getting any response from server. this is happening because of database size and my command is taking a lot of time. its just keep me waiting:

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

有人可以建议我更好的方法来检索我的目标.

Can someone suggest me better way of retrieving my goal.

推荐答案

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

Let's build this up from the basics.

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

Your first step is to create a subquery to get the latest nine surnames (http://sqlfiddle.com/#!9/aee62e/19/0). By that I mean the surnames with the highest id values.

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

And, in MySQL, that was the easy part. Now you need to retrieve the seven highest ranked (largest id) rows for each selected surname. As a start, you could do this to get all records for the selected surnames, in descending order by 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)

This is correct, but contains too many rows. Next we need to get the ranking for each lastname's rows. A lower ranking means a higher id value. This is the nasty hack in MySQL. (Nasty because it mixes procedural operations on local variables with the inherently declarative nature of 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)

Finally we need to wrap that whole mess in an outer query to pick off the seven highest ranked rows for each lastname value. (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

我认为您的需求和此解决方案的复杂性的技术术语是毛球".它绝对将结构化置于结构化查询语言中.

I believe the technical term for the complexity of your requirement and this solution is "hairball." It definitely puts the structured in Structured Query Language.

这篇关于如何从组中的mysql数据库中检索记录数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！