如何限制/偏移每个唯一 ID 返回几行的查询？

Question

我的问题是我需要对此查询中的数据进行分页：

function search($search_term, $limit, $offset)
{
    $id = $this->auth->get_user_id();

    $query = $this->db->query("
    SELECT user_id,
           first_name,
           cars_name,
           cars_id
    FROM   user_profiles
           LEFT JOIN cars
             ON cars.id_fk = user_id
    WHERE  user_id NOT LIKE '$id'
           AND activated = 1
           AND banned = 0
           AND first_name LIKE '%$search_term%'
    ORDER BY first_name ASC
    ");

    $search_data = array();
    foreach ($query->result() as $row) {

        $search_data[$row->user_id]['name'] = $row->first_name;
        $search_data[$row->user_id]['cars'][$row->cars_id] = array(
                'cars_name' => $row->cars_name);
    }
    return $search_data;
}

示例数据表/查询响应将是：

1   JOE   HONDA    123
1   JOE   TOYOTA   124
2   MAC   VW       125
2   MAC   HONDA    126
2   MAC   TESLA    127
3   STU   SUBARU   128
3   STU   KIA      129

-----------
Page 1
-----------
1   JOE   HONDA   123
          TOYOTA  124

2   MAC   VW      125
          HONDA   126

------------
Page 2
------------
3    STU    SUBARU  128
            KIA     129

如果我在 MySQL 查询末尾输入限制和偏移量，

...
LIMIT $limit
OFFSET $offset;
");

则限制和偏移量将应用于总计 行数，而不是按用户分组的行数。

我尝试过使用 GROUP BY 但无法使其工作。

我的目标是按上述方式进行查询，但通过计算用户数的行数（而不是所有行）来限制和偏移查询。

有什么想法吗？

Answer 1

解决方案实际上是使用 GROUP BY 子句：

SELECT SQL_CALC_FOUND_ROWS
       user_id,
       first_name,
       cars_name,
       cars_id
FROM   user_profiles
       LEFT JOIN cars
         ON cars.id_fk = user_id
WHERE  user_id NOT LIKE '$id'
       AND activated = 1
       AND banned = 0
       AND first_name LIKE '%$search_term%'
GROUP BY user_id
ORDER BY first_name ASC
LIMIT 100

顺序很重要。首先是 GROUP BY，然后是 ORDER BY，然后是 OFFSET/LIMIT。

注意到上面的SQL_CALC_FOUND_ROWS了吗？查询执行后，如果您想获取总行数（包括由于 LIMIT 子句而未返回的行数），只需使用：

SELECT FOUND_ROWS() AS `count`

并获取 count 栏目。

但是，正如您所说，行将折叠，并且您将丢失一些 cars_name 和 cars_id 值。

另一个解决方案是使用 GROUP_CONCAT，然后在 PHP 中拆分它：

SELECT 
       user_id,
       first_name,
       GROUP_CONCAT(cars_name SEPARATOR ','),
       GROUP_CONCAT(cars_id SEPARATOR ','),
FROM   user_profiles
       LEFT JOIN cars
         ON cars.id_fk = user_id
WHERE  user_id NOT LIKE '$id'
       AND activated = 1
       AND banned = 0
       AND first_name LIKE '%$search_term%'
ORDER BY first_name ASC
LIMIT 100

这会给你类似的东西：

1   JOE   HONDA,TOYOTA     123,124
2   MAC   VW,HONDA,TESLA   125,126,127
3   STU   SUBARU,KIA       128,129

Answer 2

如果您想获得这样的列表，

Page 1
----------------------
1   JOE   HONDA    123
1   JOE   TOYOTA   124

Page 2
----------------------
2   MAC   VW       125
2   MAC   HONDA    126
2   MAC   TESLA    127

Page 3
----------------------
3   STU   SUBARU   128
3   STU   KIA      129

请忘记限制，请执行以下操作：

A - 首先检索用户 id 列表并将其插入临时表中

CREATE TEMPORARY TABLE `test`.`temp_user_ids` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = MEMORY

B - 接下来将相关 user_id 插入表中。

INSERT INTO temp_user_ids
  SELECT null, user_id
FROM   user_profiles
       LEFT JOIN cars
         ON cars.id_fk = user_id
WHERE  user_id NOT LIKE '$id'
       AND activated = 1
       AND banned = 0
       AND first_name LIKE '%$search_term%'
ORDER BY user_id DESC  /*insert in reverse order !*/

最低的user_id是temptable中的last_insert_id，而temp_table
项目按顺序排列。

C - 将 SQL @var @current_id 设置为 temp_table 中的last_insert_id。

SELECT @current_id:= LAST_INSERT_ID() 

D - 接下来仅使用您想要的 user_id 从表中选择相关行。

SELECT count(*) as row_count,
       up.user_id,
       first_name,
       group_concat(cars_name) as car_names,
       group_concat(cars_id) as car_ids,
FROM   user_profiles up
       LEFT JOIN cars
         ON cars.id_fk = up.user_id
       INNER JOIN temp_user_ids t 
         ON (t.user_id = up.user_id)
WHERE  t.id = @current_id
GROUP BY up.user_id
ORDER BY cars.id

E - 现在降低 @current_id

SELECT @current_id:= @current_id - 1;

F - 并重复步骤 D 和 E，直到没有更多行为止。

第一个字段 row_count 告诉您字段中聚合的行数
car_names 和 car_ids。您可以使用 php 的 explode 来分隔这些字段。

Answer 3

我看不到一种方法可以在一个查询中执行此操作。我的解决方案是使用具有相同参数的分组查询来获取唯一 ID 的计数：

SELECT COUNT(1) AS uid_count
FROM   user_profiles
       LEFT JOIN cars
         ON cars.id_fk = user_id
GROUP BY user_profiles.user_id
WHERE  user_id NOT LIKE '$id'
       AND activated = 1
       AND banned = 0
       AND first_name LIKE '%$search_term%'

然后获取 uid_countmysql_num_rows 并使用它来计算上述的分页变量询问。