MYSQL-按ID排序(按DESC顺序)，按X分组 [英] MYSQL - Order By Id In DESC Order, Group By X

问题描述

在过去的4个小时中，我一直专注于解决这个问题，在一个坚果壳中，我想按IDSC的顺序按ID对这个表进行 排序，按ads_post_id分组(按DESC顺序在id上)，返回的限制为6行.

For the past 4 hours I've been laser focused on this one problem, in a nut shell, I want to order this table by id in DESC order, grouped by ads_post_id (in DESC order based on id), with a LIMIT of 6 rows returned.

数据库示例，

id   | ads_post_id
---------------------------------------------------------------------------
22   | 983314845117571
23   | 983314845117571
24   | 983314845117571         
104  | 983314845117571
250  | 983314845117571
253  | 983314845117571 
767  | 983314845117571          
---------------------------------------------------------------------------

我当前的查询

SELECT * FROM fb_ads GROUP BY ads_post_id ORDER BY id DESC LIMIT 6

然而，所有返回的都是

id   | ads_post_id
---------------------------------------------------------------------------
22   | 983314845117571   
---------------------------------------------------------------------------

它应该返回，

id    | ads_post_id
---------------------------------------------------------------------------
767   | 983314845117571   
---------------------------------------------------------------------------

很显然，它是按ASC顺序分组的，然后按ID依DESC顺序排序吗?

So clearly it's been grouped in ASC order and then ordered by ID in DESC order right?

因此，这使我陷入了研究的困境，大多数人似乎将其用作解决方法，但是由于性能下降而并非可取，每次用户进入下一个查询时都需要重新调用此查询.页面，

So this has led me down a rabbit hole with research, most people seemed to use this as a work around, but it's not preferable because of the performance hit, this query needs to be recalled every time a user goes onto a next page,

SELECT * FROM 
(
select * from fb_ads order by id desc
) as fb_ads
group by ads_post_id
order by id DESC LIMIT 6

但是，它仍然对我不起作用，只是返回了

HOWEVER, it still didn't work for me, this only returned,

   ---------------------------------------------------------------------------
    id   | ads_post_id
    ---------------------------------------------------------------------------
    22   | 983314845117571   
    ---------------------------------------------------------------------------

请注意:这是我的数据库示例，为简化回答，实际上会有成千上万的ads_post_id，据我目前所知，MYSQL的MAX()函数将不起作用，因为它仅返回一行.

PLEASE NOTE: This is a sample of my database for simplicity of answering, in practice there will be thousands of ads_post_id, so as far as I know at this time MYSQL's MAX() function won't work because it only returns one row.

我不是MYSQL的专家，但我足够了解，我觉得这需要我专业知识范围之外的解决方案.

I'm not an expert in MYSQL, but I know enough to get around, I feel this needs a solution outside my scope of expertise.

一些帮助会走很长一段路，谢谢.

Some help would go a very long way, thank you.

推荐答案

由于MySQL的功能，您误解了GROUP BY在SQL中的工作方式.在标准SQL中，SELECT语句中的每个非聚合列都必须位于GROUP BY子句中(其值100％依赖于GROUP BY子句中已存在列的列是一个例外，尽管很少有SQL支持这种豁免)

You have misunderstood how GROUP BY works in SQL, due to a feature of MySQL. In standard SQL every non aggregate column in the SELECT statement MUST be in the GROUP BY clause (there is an exception for columns whose values are 100% dependent on a column already in the GROUP BY clause, although few flavours of SQL support this exemption).

MySQL默认情况下不强制执行此操作，但是未定义用于那些列的行值.尽管您可能会得到想要的，但您可能也没有.即使您这样做，将来也有可能改变.

MySQL does not enforce this by default, but which rows values are used for those columns is not defined. While you might get the one you want, you also might not. And even if you do there is a chance that it will change in the future.

排序通常独立于GROUP BY，尽管如果未指定ORDER子句，则将根据执行GROUPing所需的内容对结果进行排序(即，如果它有助于以一种顺序对行进行排序)进行GROUP BY，那么除非您使用ORDER BY子句明确告知它，否则MySQL以后不会再对记录重新排序.

The ordering is independent of the GROUP BY normally, although if you do not specify an ORDER clause then the results will be ordered based on what was required to perform the GROUPing (ie, if it helps to order the rows in one order to do the GROUP BY then MySQL will not bother to reorder the records afterwards unless you specifically tell it to with an ORDER BY clause).

因此，根据您当前的数据，按ads_post_id分组，返回的id的值可能是22、23、24、104、250、253或767.MySQL选择使用的是未定义.

So with your current data, grouping by ads_post_id the value of id that is returned could be 22, 23, 24, 104, 250, 253 or 767. Which one MySQL choses to use is not defined.

使用您当前的数据进行修复，这很简单，因为您只需获取MAX id:-

With your current data fixing this is trivial as you can just get the MAX id:-

SELECT ads_post_id, MAX(id) 
FROM fb_ads 
GROUP BY ads_post_id 
LIMIT 6

MAX将为每个GROUPED值返回1行.

MAX will return 1 row for each GROUPed value.

通常的问题是人们想要该行的另一列.例如，假设示例数据中的每一行也都有一个IP地址，并且您想要的那一行等于ads_post_id的最高ID:-

The normal problem is that people want another column for that row. For example say that each of the rows in your sample data also had an IP address, and you wanted the one that equated to the highest id for the ads_post_id:-

id   | ads_post_id         ip_address
---------------------------------------------------------------------------
22   | 983314845117571     192.168.0.0
23   | 983314845117571     192.168.0.5
24   | 983314845117571     192.168.0.7    
104  | 983314845117571     192.168.0.0
250  | 983314845117571     192.168.0.4
253  | 983314845117571     192.168.0.6
767  | 983314845117571     192.168.0.1     
---------------------------------------------------------------------------

在这种情况下，您不能只使用MAX.例如，如果您尝试过:-

In this case you cannot just use MAX. For example if you tried:-

SELECT ads_post_id, MAX(id), MAX(ip_address) 
FROM fb_ads 
GROUP BY ads_post_id 
LIMIT 6

您将获得以下数据

id   | ads_post_id         ip_address
---------------------------------------------------------------------------
767  | 983314845117571     192.168.0.7     
---------------------------------------------------------------------------

如果您在大多数SQL版本中尝试了以下操作，则会收到错误消息.在使用默认设置的MySQL中，您会得到结果，但是返回的IP地址未定义(实际上是随机的).

If you tried the following in most flavours of SQL you would get an error. In MySQL with the default settings you would get a result, but which IP address is returned is not defined (and in effect random).

SELECT ads_post_id, MAX(id), ip_address 
FROM fb_ads 
GROUP BY ads_post_id 
LIMIT 6

解决方案是在子查询中获取每个ads_post_id的最大ID，然后将其返回表以获取其余值:-

The solutions to this are either to get the max id for each ads_post_id in a sub query and then joining that back to the table to get the rest of the values:-

SELECT a.ads_post_id,
        a.id,
        a.ip_address
FROM fb_ads a
INNER JOIN
(
    SELECT ads_post_id, MAX(id) AS max_id 
    FROM fb_ads 
    GROUP BY ads_post_id 
) sub0
ON a.ads_post_id = sub0.ads_post_id
AND a.id = sub0.max_id

一种替代方法是(ab)使用GROUP_CONCAT聚合函数. GROUP_CONCAT将把所有串联在一起的值带回到1个字段中，每个字段都由分隔(默认情况下).您可以添加ORDER BY子句以强制将它们串联在一起.您可以使用SUBSTRING_INDEX将所有内容返回到第一个逗号.

An alternative is to (ab)use the GROUP_CONCAT aggregate function. GROUP_CONCAT will bring back all the values concatenated together into 1 field, each separated by a , (by default). You can add an ORDER BY clause to force the order they are concatenated into. The you can use SUBSTRING_INDEX to return everything up to the first comma.

这对于简单数据可能很有用，但对于文本数据或最大为NULL的字段会造成问题.

This can be useful for simple data, but becomes problematic with text data or fields that max be NULL.

SELECT a.ads_post_id,
        SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC), ',', 1),
        SUBSTRING_INDEX(GROUP_CONCAT(ip_address ORDER BY id DESC), ',', 1)
FROM fb_ads 
GROUP BY ads_post_id 

这篇关于MYSQL-按ID排序(按DESC顺序)，按X分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！