mysql - SQL select last before by date 和 group by

Question

使用 MySQL，我想选择最后一次提交为 NULL 而前一次不是的所有提交(行)，按 user_id 分组。

如果我考虑以下示例表，那么答案将是行:2、5 和 7。

| ID | submission | date_submission | user_id |
|----|------------|-----------------|---------|
| 1  | lorem      | 2019-01-01      | 1       |
| 2  | ipsum      | 2019-01-02      | 1       |
| 3  | NULL       | 2019-01-03      | 1       |
| 4  | amet       | 2019-01-05      | 2       |
| 5  | NULL       | 2019-01-06      | 2       |
| 6  | sit        | 2019-01-04      | 1       |
| 7  | sed        | 2019-01-08      | 3       |
| 8  | elit       | 2019-01-07      | 4       |
| 9  | NULL       | 2019-01-09      | 3       |

MRE:

CREATE TABLE submissions (
  id int NOT NULL,
  submission varchar(45) NULL,
  date_submitted date NOT NULL,
  user_id int DEFAULT NULL
 );

insert into submissions (1, "lorem", 2019-01-01, 1);
insert into submissions (2, "ipsum", 2019-01-02, 1);
insert into submissions (3, NULL, 2019-01-03, 1);
insert into submissions (4, "amet", 2019-01-05, 2);
insert into submissions (5, NULL, 2019-01-06, 2);
insert into submissions (6, "sit", 2019-01-04, 1);
insert into submissions (7, "sed", 2019-01-08, 3);
insert into submissions (8, "elit", 2019-01-07, 4);
insert into submissions (9, NULL, 2019-01-09, 3);

Answer 1

首先获取每个用户的最后一个提交日期为null的日期，然后连接到表中以获取之前日期的行。
通过使用 ROW_NUMBER() 获取这些先前日期中的最后一个(如果它不是 null):

select t.id, t.submission, t.date_submitted, t.user_id
from (
  select s.*,
    row_number() over (partition by s.user_id order by s.date_submitted desc) rn
  from submissions s inner join (
    select user_id,
      max(case when submission is null then date_submitted end) maxnulldate
    from submissions
    group by user_id
  ) g on g.user_id = s.user_id and g.maxnulldate > s.date_submitted  
) t
where t.rn = 1 and t.date_submitted is not null

参见 demo .
结果:

| id  | submission | date_submitted | user_id |
| --- | ---------- | -------------- | ------- |
| 2   | ipsum      | 2019-01-02     | 1       |
| 4   | amet       | 2019-01-05     | 2       |
| 7   | sed        | 2019-01-08     | 3       |

我猜您指的是预期结果中的第 4 行而不是第 5 行，对吧？