需求:查询出type 类型是1合2的所有用户信息,但是type=2的用户必须是 sex=1
已知语句 select * from op_al_dd_retain_v where type in (1,2)
SELECT * FROM op_al_dd_retain_v
WHERE type IN ( 1, 2 ) AND ( CASE WHEN
type = 1 AND sex != 1 THEN 0 ELSE 1 END ) = 1
以上查询结果会显示 type满足属于(1,2) 并且 当type=1时sex满足也等于1
以下是生产使用场景
USE c_v_o;
INSERT OVERWRITE TABLE od_me PARTITION (dt='${calc_date}')
SELECT
a_id,
c_id,
IF(LENGTH(start_t)>0,UNIX_TIMESTAMP(start_t)*1000,0) AS start_time,
COALESCE(first_online,0) AS first_online,
COALESCE(last_online,0) AS last_online,
number_o
FROM op_meta_least
WHERE c_t=1
AND CASE WHEN v_type IN (1,3) THEN 1 WHEN video_type!=5 AND episode_type NOT IN (3,4) THEN 1 ELSE 0 END=1
AND (
(UNIX_TIMESTAMP('${calc_date}','yyyy-MM-dd')-7700)*1000 < last_online
OR
(UNIX_TIMESTAMP('${calc_date}','yyyy-MM-dd')-700) < IF(LENGTH(start_time)>0,UNIX_TIMESTAMP(start_time),0)
)
AND (UNIX_TIMESTAMP('${calc_date}','yyyy-MM-dd')+800)*1000 > first_online