Table, the same user_id various channels to enter many times, the requirement is when user_id only through A channel into the keep the user_id through A record for the first time, other cases, excluding through A channel into the records,
Table:
User_id channel dt
1 A 2020/6/28
1 A 2020/6/29
2 A 2020/6/30
2 V 2020/7/1
3 A 2020/7/2
3 S 2020/7/3
3 V 2020/7/4

The final effect
User_id channel dt
1 A 2020/6/28
2 V 2020/7/1
3 S 2020/7/3
3 V 2020/7/4

CodePudding user response:

Select * from (
Select t1. User_id user_id, 'A' channel, min (t1) dt) from t dt t1
Where A t1. Channel='A' and not the exists (
Select 1 t t2 where the from t2. User_id=t1. User_id and t2. The channel!
='A')Group by t1. User_id
Union all
The select t3. * from t t3
Where t3. The channel!
='A'T4)
The order by t4. Dt asc

CodePudding user response:

reference 1st floor evanweng response:

select * from (
Select t1. User_id user_id, 'A' channel, min (t1) dt) from t dt t1
Where A t1. Channel='A' and not the exists (
Select 1 t t2 where the from t2. User_id=t1. User_id and t2. The channel!
='A')Group by t1. User_id
Union all
The select t3. * from t t3
Where t3. The channel!
='A'T4)
The order by t4. Dt asc

test available