Mysql 选择重复行之一

发布于 2024-12-02 00:45:50 字数 337 浏览 0 评论 0原文

我正在尝试根据 mysql 表中的 Ticket_id 选择重复条目中的最新条目，我当前的情况是这样的。

SELECT  * ,
        COUNT(*) AS cnt ,
        ticket_id
FROM    temp_tickets
GROUP BY ticket_id
ORDER BY id DESC

它给出了一行被重复的次数，但我能够选择这些多行中的最新一个

假设我有 3 个 Ticket_id，它们被重复了 5 次，所以现在我想从所有这 3 个 id 中选择最新出现的一次。

让我知道我是否需要更具体。

谢谢

原文

I am trying to select the latest entry of the duplicate entries against the ticket_id in a mysql table , My current is something like this.

SELECT  * ,
        COUNT(*) AS cnt ,
        ticket_id
FROM    temp_tickets
GROUP BY ticket_id
ORDER BY id DESC

It gives the number of times a row is duplicated but i am able to select the latest one of those mulptiple rows

Let say i have 3 ticket_id's which got duplicated for 5 times so now i want to select the latest occurrence from all these 3 id's .

Lemme know if i have to be more specific.

Thanks

分享到QQ

分享到微博

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

发布评论

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

清泪尽 2024-12-09 00:45:50

这是一种方法（假设“最新”意味着“最大的ID”）

SELECT  temp_tickets.*
FROM    temp_tickets
        JOIN ( SELECT   MAX(id) AS id
               FROM     temp_tickets
               GROUP BY ticket_id
               HAVING   COUNT(*) > 1
             ) latest ON latest.id = temp_tickets.id

我怀疑可能会想出一个更有效的解决方案涉及用户变量，但我会把它留给其他人......

Here's one way (assuming "latest" means "greatest id")

SELECT  temp_tickets.*
FROM    temp_tickets
        JOIN ( SELECT   MAX(id) AS id
               FROM     temp_tickets
               GROUP BY ticket_id
               HAVING   COUNT(*) > 1
             ) latest ON latest.id = temp_tickets.id

I suspect it might be possible to come up with a more efficient solution involving user variables but I'll leave that to someone else...

回复收藏 0 原文

~没有更多了~