是否可以从group_concat mysql中删除1个记录或最大（ID）

发布于 2025-02-07 01:10:27 字数 354 浏览 5 评论 0原文

我必须删除/跳过group_concat mysql中的第一个记录或最大ID。这是查询

select email, group_concat(id order by id desc) as id 
from api_admin.external_user 
group by email 
having count(1) > 1;

原文

I have to remove/skip the 1st records or max id in group_concat MYSQL.
Here is query

select email, group_concat(id order by id desc) as id 
from api_admin.external_user 
group by email 
having count(1) > 1;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

朦胧时间 2025-02-14 01:10:28

看起来是子查询方法：

select email, group_concat(id order by id desc) as id 
from external_user 
where id not in (
  -- here filter out records with max id per email
  select max(id) as max_id from external_user group by email 
)
group by email;

Looks as sub-query approach:

select email, group_concat(id order by id desc) as id 
from external_user 
where id not in (
  -- here filter out records with max id per email
  select max(id) as max_id from external_user group by email 
)
group by email;

SQL online environment

回复收藏 0 原文

葵雨 2025-02-14 01:10:27

您的group_concat（）返回一个字符串，该字符串是至少2个ID的逗号分隔列表（因为 ake 条款中的条件）排序。
您可以使用函数）在第一个逗号之后获取返回的字符串的一部分：

SELECT email, 
       SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC), ',', -COUNT(*) + 1) AS ids 
FROM external_user 
GROUP BY email 
HAVING COUNT(*) > 1;

请参阅 demo 。

Your GROUP_CONCAT() returns a string which is a comma separated list of at least 2 ids (because of the condition in the HAVING clause) sorted descending.
You can use the function SUBSTRING_INDEX() to get the part of the returned string after the first comma:

SELECT email, 
       SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC), ',', -COUNT(*) + 1) AS ids 
FROM external_user 
GROUP BY email 
HAVING COUNT(*) > 1;

See the demo.

回复收藏 0 原文

~没有更多了~