问SQL组by，让null值保持不变

EN

SELECT 
id,
CASE
    WHEN
        SUM(CASE WHEN date_in IS NULL THEN 1 ELSE 0 END) > 0
    THEN 'out'
    ELSE 'in'
END
FROM
  table_name
GROUP BY id;

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID INT NOT NULL
,Date_out DATE NOT NULL
,Date_in DATE NULL
,PRIMARY KEY(id,date_out)
);

INSERT INTO my_table VALUES
(1  ,'2015-01-01',NULL),
(2  ,'2015-01-01','2015-05-13'),
(2  ,'2014-07-16','2015-01-01'),
(4  ,'2015-07-28',NULL),
(4  ,'2014-07-16','2015-07-28');

SELECT x.id on_loan 
  FROM my_table x 
  JOIN  
     ( SELECT id, MAX(date_out) max_date_out FROM my_table GROUP BY id) y 
    ON y.id = x.id 
   AND y.max_date_out = x.date_out 
 WHERE date_in IS NULL;
+---------+
| on_loan |
+---------+
|       1 |
|       4 |
+---------+

SELECT x.id in_stock 
  FROM my_table x 
  JOIN (SELECT id, MAX(date_out) max_date_out FROM my_table GROUP BY id) y 
    ON y.id = x.id 
   AND y.max_date_out = x.date_out 
  WHERE date_in IS NOT NULL;
+----------+
| in_stock |
+----------+
|        2 |
+----------+

SELECT GROUP_CONCAT(CASE WHEN date_in IS NULL THEN x.id END) on_loan
     , GROUP_CONCAT(CASE WHEN date_in IS NOT NULL THEN x.id END) in_stock 
  FROM my_table x 
  JOIN 
     ( SELECT id, MAX(date_out) max_date_out FROM my_table GROUP BY id) y 
    ON y.id = x.id AND y.max_date_out = x.date_out;
+---------+----------+
| on_loan | in_stock |
+---------+----------+
| 1,4     | 2        |
+---------+----------+