你见过吗 https://github.com/fhulufhelo/Get-Last-Record-in-Each-MySQL-Group ? 对我有用

$sql = "SELECT c.id, c.name,  c.email, r.id, r.companyid, r.name,  r.email FROM companytable c LEFT JOIN ( SELECT * FROM revisiontable  WHERE id  IN ( SELECT MAX(id)  FROM revisiontable  GROUP BY companyid ))  r ON a.cid=b.r.id";

您可以通过计数进行分组,还可以得到组的最后一项,例如:

SELECT 
    user,
    COUNT(user) AS count,
    MAX(id) as last
FROM request 
GROUP BY user

从主键所在的表名中选择*(从表名按列名分组中选择max(主键)

如果您真正关心的是性能,那么可以在表中引入一个名为 IsLastInGroup 类型位的。

在最后一列上将其设置为true,并在每行insert/update/delete中维护它。写起来会慢一些,但读起来会有好处。这取决于您的用例,我建议您只有在阅读时才使用它。

所以你的问题是:

SELECT * FROM Messages WHERE IsLastInGroup = 1

这个怎么样:

SELECT DISTINCT ON (name) *
FROM messages
ORDER BY name, id DESC;

我也有类似的问题(关于postgresql的强硬)和一个1M记录表。这个解决方案需要1.7秒,而使用左连接的解决方案需要44秒。 在我的情况下,我不得不过滤你的 名称 与空值对应的字段,从而在0.2秒内获得更好的性能

select * from messages group by name desc

如果你想每个人的最后一排 Name ,则可以通过 名字 订货 Id 按降序排列。

查询

SELECT t1.Id, 
       t1.Name, 
       t1.Other_Columns
FROM 
(
     SELECT Id, 
            Name, 
            Other_Columns,
    (
        CASE Name WHEN @curA 
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curA := Name END 
    ) + 1 AS rn 
    FROM messages t, 
    (SELECT @curRow := 0, @curA := '') r 
    ORDER BY Name,Id DESC 
)t1
WHERE t1.rn = 1
ORDER BY t1.Id;

SQL Fiddle

根据你的问题,下面的问题可以解决。

SELECT M1.* 
FROM MESSAGES M1,
(
 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
 FROM MESSAGES
 GROUP BY 1
) M2
WHERE M1.Others_data = M2.Max_Others_data
ORDER BY Others_data;

我们可以用这个方法删除表中的重复项吗?结果集基本上是唯一记录的集合,所以如果我们可以删除不在结果集中的所有记录,我们将有效地没有重复项?我试过了,但mysql给出了一个1093错误。

DELETE FROM messages WHERE id NOT IN
 (SELECT m1.id  
 FROM messages m1 LEFT JOIN messages m2  
 ON (m1.name = m2.name AND m1.id < m2.id)  
 WHERE m2.id IS NULL)

是否有方法可以将输出保存到临时变量,然后从NOT IN(临时变量)中删除?@比尔,谢谢你提供了一个非常有用的解决方案。

编辑:我想我找到了解决办法:

DROP TABLE IF EXISTS UniqueIDs; 
CREATE Temporary table UniqueIDs (id Int(11)); 

INSERT INTO UniqueIDs 
    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON 
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields  
    AND T1.ID < T2.ID) 
    WHERE T2.ID IS NULL); 

DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);

我们将介绍如何使用mysql获取按记录分组的最后一条记录。例如,如果您有这个posts结果集。

id category_id post_title

1 1 Title 1

2 1 Title 2

3 1 Title 3

4 2 Title 4

5 2 Title 5

6 3 Title 6

我想能够得到每个类别的最后一篇文章,分别是标题3,标题5和标题6。要按类别获取文章,您将使用mysql按键盘分组。

select * from posts group by category_id

但是我们从这个查询得到的结果是。

身份证类别

1 1 Title 1

4 2 Title 4

6 3 Title 6

GROUPBY将始终返回结果集中组中的第一条记录。

SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id );

这将返回每个组中ID最高的帖子。

身份证类别

3 1 Title 3

5 2 Title 5

6 3标题6

Reference Click Here

你也可以从这里看风景。

http://sqlfiddle.com/#!9/ef42b/9

第一种解决方案

SELECT d1.ID,Name,City FROM Demo_User d1
INNER JOIN
(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);

第二种解决方案

SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;

嗨@vijay dev如果你的桌子 信息 包含 身份证件 这是自动递增主键,然后要根据主键获取最新记录,您的查询应如下所示:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId

试试这个:

SELECT jos_categories.title AS name,
       joined .catid,
       joined .title,
       joined .introtext
FROM   jos_categories
       INNER JOIN (SELECT *
                   FROM   (SELECT `title`,
                                  catid,
                                  `created`,
                                  introtext
                           FROM   `jos_content`
                           WHERE  `sectionid` = 6
                           ORDER  BY `id` DESC) AS yes
                   GROUP  BY `yes`.`catid` DESC
                   ORDER  BY `yes`.`created` DESC) AS joined
         ON( joined.catid = jos_categories.id )  

很明显,有很多不同的方法可以得到相同的结果,你的问题似乎是,在mysql中,在每个组中,什么是获得最后结果的有效方法。如果你正在处理大量的数据,并且假设你将innodb与最新版本的mysql(比如5.7.21和8.0.4-rc)一起使用,那么可能没有一种有效的方法来实现这一点。

我们有时需要对行数超过6000万的表执行此操作。

对于这些示例,我将使用只有大约150万行的数据,其中查询需要查找数据中所有组的结果。在我们的实际案例中,我们通常需要返回大约2000个组的数据(假设不需要检查很多数据)。

我将使用下表:

CREATE TABLE temperature(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  groupID INT UNSIGNED NOT NULL, 
  recordedTimestamp TIMESTAMP NOT NULL, 
  recordedValue INT NOT NULL,
  INDEX groupIndex(groupID, recordedTimestamp), 
  PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id)); 

温度表中有大约150万条随机记录和100个不同的组。 选定的_组由这100个组填充(在我们的情况下,对于所有组,这通常小于20%)。

由于此数据是随机的,这意味着多行可以具有相同的recordedtimestamp。我们需要的是按照group id的顺序获取所有选定组的列表,每个组都有最后一个recordedtimestamp,如果同一个组有多个这样的匹配行,那么这些行的最后一个匹配id。

如果假设mysql有一个last()函数,它在特殊的order by子句中返回最后一行的值,那么我们可以简单地执行以下操作:

SELECT 
  last(t1.id) AS id, 
  t1.groupID, 
  last(t1.recordedTimestamp) AS recordedTimestamp, 
  last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
ORDER BY t1.recordedTimestamp, t1.id
GROUP BY t1.groupID;

在这种情况下,它只需要检查几百行,因为它不使用任何正常的group by函数。这将在0秒内执行,因此效率很高。 注意,通常在mysql中,我们会在group by子句后面看到orderby子句,但是这个orderby子句用于确定last()函数的顺序,如果它在groupby之后,那么它将对组进行排序。如果不存在GROUPBY子句,则所有返回行中的最后一个值都将相同。

但是mysql没有这个功能,所以让我们看看它有什么功能的不同想法,并证明这些都不是有效的。

例1

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT t2.id
  FROM temperature t2 
  WHERE t2.groupID = g.id
  ORDER BY t2.recordedTimestamp DESC, t2.id DESC
  LIMIT 1
);

这项研究检查了3009254行,在5.7.21上花费了约0.859秒,在8.0.4-rc上花费了稍长的时间。

例2

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
INNER JOIN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
) t5 ON t5.id = t1.id;

研究了1505331行,在5.7.21和8.0.4-rc上分别花了约1.25秒和稍长的时间。

例3

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
WHERE t1.id IN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
)
ORDER BY t1.groupID;

这项研究检查了3009685行,在5.7.21上花费了约1.95秒,在8.0.4-rc上花费了稍长的时间。

例4

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT max(t2.id)
  FROM temperature t2 
  WHERE t2.groupID = g.id AND t2.recordedTimestamp = (
      SELECT max(t3.recordedTimestamp)
      FROM temperature t3 
      WHERE t3.groupID = g.id
    )
);

这项研究共检查了6137810行,在5.7.21上花费了约2.2秒,在8.0.4-rc上花费了稍长的时间。

例5

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
  SELECT 
    t2.id, 
    t2.groupID, 
    t2.recordedTimestamp, 
    t2.recordedValue, 
    row_number() OVER (
      PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, t2.id DESC
    ) AS rowNumber
  FROM selected_group g 
  INNER JOIN temperature t2 ON t2.groupID = g.id
) t1 WHERE t1.rowNumber = 1;

检查了6017808行,在8.0.4-rc上花费了约4.2秒。

例6

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM (
  SELECT 
    last_value(t2.id) OVER w AS id, 
    t2.groupID, 
    last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp, 
    last_value(t2.recordedValue) OVER w AS recordedValue
  FROM selected_group g
  INNER JOIN temperature t2 ON t2.groupID = g.id
  WINDOW w AS (
    PARTITION BY t2.groupID 
    ORDER BY t2.recordedTimestamp, t2.id 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
) t1
GROUP BY t1.groupID;

检查了6017908行,在8.0.4-rc上花费了约17.5秒。

例7

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
LEFT JOIN temperature t2 
  ON t2.groupID = g.id 
  AND (
    t2.recordedTimestamp > t1.recordedTimestamp 
    OR (t2.recordedTimestamp = t1.recordedTimestamp AND t2.id > t1.id)
  )
WHERE t2.id IS NULL
ORDER BY t1.groupID;

这一次要花很长时间所以我不得不杀了它。

SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;

下面是另一种获取最后相关记录的方法 GROUP_CONCAT 由和命令 SUBSTRING_INDEX 从列表中选择一条记录

SELECT 
  `Id`,
  `Name`,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      `Other_Columns` 
      ORDER BY `Id` DESC 
      SEPARATOR '||'
    ),
    '||',
    1
  ) Other_Columns 
FROM
  messages 
GROUP BY `Name` 

上面的查询将把所有 Other_Columns 都是一样的 Name 分组和使用 ORDER BY id DESC 将加入所有 其他_列 以降序排列在特定组中,在我的示例中使用了提供的分隔符 || 使用 子串索引 在这个名单上会选出第一个

Fiddle Demo

这里有两个建议。首先,如果mysql支持row_number(),那么很简单:

WITH Ranked AS (
  SELECT Id, Name, OtherColumns,
    ROW_NUMBER() OVER (
      PARTITION BY Name
      ORDER BY Id DESC
    ) AS rk
  FROM messages
)
  SELECT Id, Name, OtherColumns
  FROM messages
  WHERE rk = 1;

我猜你说的“最后一个”是指身份顺序上的最后一个。如果没有,请相应地更改row_number()窗口的ORDER BY子句。如果row_number()不可用,这是另一个解决方案:

其次,如果没有,这通常是一个很好的方法:

SELECT
  Id, Name, OtherColumns
FROM messages
WHERE NOT EXISTS (
  SELECT * FROM messages as M2
  WHERE M2.Name = messages.Name
  AND M2.Id > messages.Id
)

换言之,选择没有相同名称的后续id消息的消息。

一种速度相当快的方法如下。

SELECT * 
FROM messages a
WHERE Id = (SELECT MAX(Id) FROM messages WHERE a.Name = Name)

结果

Id  Name    Other_Columns
3   A   A_data_3
5   B   B_data_2
6   C   C_data_1

以下是我的解决方案:

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;

我还没有测试过大型数据库,但我认为这可能比加入表更快:

SELECT *, Max(Id) FROM messages GROUP BY Name