mysql - 忽略sql中的空条目

Question

下面的示例构建了一个表，该表按 userId 和 passageId 提取前两个分数值。如何只选择新表中每条记录至少包含两个分数的记录(即忽略 score2 为空的记录)？

Example

代码:

 drop table if exists simon;
 drop table if exists simon2;
 Create table simon (userId int, passageId int, score int);
 Create table simon2 (userId int, passageId int, score1 int,score2 int);    

 INSERT INTO simon (userId , passageId , score )
 VALUES
 (10, 1, 2),
 (10, 1, 3),
 (10, 2, 1),
 (10, 2, 1),
 (10, 2, 5),
 (11, 1, 1),
 (11, 2, 2),
 (11, 2, 3),
 (11, 3, 4);

 insert into simon2(userId,passageId,score1,score2)
 select t.userId, t.passageId,
 substring_index(t.scores,',',1) as score1,
 (case when length(t.scores) > 1 then substring_index(t.scores,',',-1) 
  else null
  end
 ) as score2
 from 
 (select userId,passageId,
 substring_index (group_concat(score separator ','),',',2) as scores
 from simon
 group by userId,passageId) t;

 select *from simon2;

这是我现在得到的:

   userId   passageId   score1  score2
1   10      1           2       3
2   10      2           1       1
3   11      1           1       NULL
4   11      2           2       3
5   11      3           4       NULL

这就是我想要的:

   userId   passageId   score1  score2
1   10      1           2       3
2   10      2           1       1
4   11      2           2       3

Answer 1

只需在您的查询周围添加这个

Select * from ( ...... ) x where score2 is not null