如何在mysql中跳过表中的行

2vuwiymt 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(296)

我有一个表，其中有以下数据，我想跳过第3行，只想获取打开状态只有一次，我使用下面的查询，但它也跳过工作5。

SELECT t.*
FROM emailEvent t
  JOIN 
    ( SELECT MIN(id) AS minid
      FROM emailEvent WHERE email_id = 3
      GROUP BY status
    ) AS grp
    ON grp.minid = t.id
WHERE (t.email_id = 3)

我提出了一个解决方案，但不确定是否有其他最好的解决方案

SELECT t.*
FROM emailEvent t
WHERE t.status != "Open" and t.email_id = 3
UNION
(
SELECT et.*
FROM emailEvent et
WHERE et.status = "Open" and et.email_id = 3
ORDER BY et.createdAt DESC LIMIT 1
)

我希望结果像这样

sql mysql group-by subquery Distinct

来源：https://stackoverflow.com/questions/63278981/how-to-skip-rows-from-table-in-mysql

2条答案

按热度按时间

f3temu5u1#

SELECT *
FROM
 (
   SELECT t.*, 
      min(id) over (partition by status) as min_id
   FROM emailEvent t
   WHERE (t.email_id = 3)   -- only email 3
 ) AS dt
WHERE id = min_id           -- only for 'Open' status
   OR status <> 'Open'

对于不支持窗口聚合的旧版本：

select * 
from emailEvent
where email_id = 3
and 
  (
   e.status <> 'Open'
   or id in (select min(id)  -- only for 'Open' status
          from emailEvent 
          where status = 'Open'
          and email_id = 3)
  )

赞(0）回复(0）举报 2021-07-24

mu0hgdu02#

与 NOT EXISTS :

select e.* from emailEvent e
where e.status <> 'Open'
or not exists (select 1 from emailEvent where status = e.status and id < e.id)

赞(0）回复(0）举报 2021-07-24

我来回答

如何在mysql中跳过表中的行

2条答案

相关问题

热门标签

最新问答