Oracle 多类型下不同类型组合的数据量统计2

需要统计type1=1且score<4，同时type1=2且score>=4的数据量。

略有点复杂，在Oracle 多类型下不同类型组合的数据量统计1操作的基础上，添加筛选条件即可。

1. 限定数据范围
   查找type1=1 AND score<4的id值。

       SELECT id 
         FROM (SELECT id, MAX(score) maxScore 
                 FROM demo 
                 WHERE type1 = 1 
                 GROUP BY id) 
         WHERE maxScore < 4 
   

2. 拼接sql语句

       SELECT COUNT(*) 
        FROM (SELECT id, MAX(ty21) mty21, MAX(ty22) mty22, MAX(ty23) mty23 
               FROM (SELECT t.id, t.type2, 
                            CASE WHEN t.type2 = 1 AND t.score >= 4 THEN 1 else 0 END ty21, 
                            CASE WHEN t.type2 = 2 AND t.score >= 4 THEN 1 else 0 END ty22, 
                            CASE WHEN t.type2 = 3 AND t.score >= 4 THEN 1 else 0 END ty23 
                       FROM demo t 
                       WHERE t.type1 = 2 
                          AND id IN (SELECT id 
                                      FROM (SELECT id, MAX(score) maxScore 
                                             FROM demo 
                                             WHERE type1 = 1 
                                             GROUP BY id) 
                                      WHERE maxScore < 4)
                       ) 
               GROUP BY id) 
        WHERE mty21 > 0 OR mty22 > 0 OR mty23 > 0;