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;