在group by子句中按每个组中仅选择3个具有uniq属性的首个连接项

I have got simple sql:

我有简单的sql:

SELECT foos.*, bars.* FROM foos 
  LEFT JOIN bars ON bars.foo_id = foos.id
  WHERE foos.id = 1;

+------------------------------------+
| foos.id | bars.id | bars.author_id |
+------------------------------------+
|    1    |    1    |       10       |
|    1    |    3    |       10       |
|    1    |    5    |       3        |
|    1    |    6    |       10       |
|    1    |    7    |       10       |
|    1    |    8    |       10       |
|    1    |    44   |       11       |
|    1    |    32   |       10       |
+------------------------------------+

Now I need to returned not all joined bars but only first three (slice) for each bars.author_id, so it could return something like this efficiently

现在我需要返回并非所有连接的条形图,但只返回每个bars.author_id的前三个(切片),因此它可以有效地返回这样的内容

+------------------------------------+
| foos.id | bars.id | bars.author_id |
+------------------------------------+
|    1    |    1    |       10       |
|    1    |    3    |       10       |
|    1    |    5    |       3        |
|    1    |    6    |       10       |
|    1    |    44   |       11       |
+------------------------------------+

3 个解决方案

#1

i have a good solution for this:

我有一个很好的解决方案:

For, selecting 1st 3 record within each group 
1) sorting the results asc/desc by applying `order by bars.id` within group_concat()
2) limiting the records by passing the 3rd parameter as the records to limit to SUBSTRING_INDEX(str,'match str','no of records to limit') 

SELECT foos.id, 
       SUBSTRING_INDEX(GROUP_CONCAT(bars.id
                       order by bars.id),',',3),
       bars.author_id

FROM foos LEFT JOIN bars ON bars.foo_id = foos.id

WHERE foos.id = 1
GROUP BY bars.author_id

The result would be :

结果将是:

+------------------------------------+
| foos.id | bars.id | bars.author_id |
+------------------------------------+
|    1    |    1,3,6    |       10   |
|    1    |    5        |       3    |
|    1    |    44       |       11   |
+------------------------------------+

Later, on application side you can explode it by ',' and use it.

之后,在应用程序端,您可以通过','将其爆炸并使用它。

#2

You could try:

你可以尝试:

SELECT f.*, b.* 
FROM foos f
LEFT JOIN (select b1.*
           from bars b1
           where 3 < (select count(*)
                      from bars bn 
                      where bn.foo_id = b1.foo_id and
                            bn.author_id = b1.author_id and
                            bn.id < b1.id)
           ) b
       ON b.foo_id = f.id
WHERE f.id = 1;

#3

SELECT 
      foos.*
    , bars.* 
FROM 
      foos 
  LEFT JOIN 
      bars 
    ON  bars.foo_id = foos.id
    AND bars.author_id <= COALESCE(
       ( SELECT b.author_id
         FROM bars b
         WHERE b.foo_id = foos.id
         ORDER BY b.author_id ASC
         LIMIT 0 OFFSET 2            --- one less than 3
       ), 2147483647              )
WHERE 
      foos.id = 1

An index on bars( foo_id, author_id ) will help if it's slow.

条形图上的索引(foo_id,author_id)会有所帮助,如果它很慢的话。

#1