Ignore null records in sql

Question

The following example creates a table that retrieves the first two valuation values ​​using userId and passId. How can I select only records in which each record in the new table contains at least two ratings (i.e. ignore records where score2 is null)?

Example

the code:

 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;

This is what I get now:

   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

This is what I want:

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

Answer 1

Just add this around your request.

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

Answer 2

You do not have an order indicating which account is included in score_1and score_2so I just use min()and max(). You can make the logic as follows:

select s.userid, s.passageid,
       max(score) as score_1, max(score) as score_2
from simon s
group by s.userid, s.passageid
having count(*) >= 2;

10/2. , group_concat() . SQL . , .

, :

Create table simon (
    simonId int auto_increment primary key,
    userId int,
    passageId int,
    score int
);

simonId .