一張表，同一user_id多次不同渠道進入，要求是當user_id 唯一通過A渠道進入時保留該user_id首次通過A時的記錄，其他情況下，剔除通過A渠道進入的記錄。
表：
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
最后效果
user_id channel dt
1 A 2020/6/28
2 V 2020/7/1
3 S 2020/7/3
3 V 2020/7/4
uj5u.com熱心網友回復：
--創建測驗資料
create table nota (user_id int, channel varchar(10), dt date)
insert into nota
values(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')
--查詢最終資料
with nota_a as --- 唯一通過A渠道進入時保留該user_id首次通過A時的記錄
(
select top 1 * from nota where user_id in (
select user_id from nota group by user_id having count(distinct channel)=1)and channel='A' order by dt
)
,nota_na as ---- 除掉上一種情況的，都洗掉渠道A
(
select * from nota where user_id not in (select user_id from nota_a) and channel<>'A'
)
,last_info as ----以上兩種情況合并
(select * from nota_a union all select * from nota_na)
--根據查詢出來的表更新到原表中
merge into nota as t
using last_info as s
on s.user_id=t.user_id and s.channel=t.channel and s.dt=t.dt
when not matched by source
then delete;
uj5u.com熱心網友回復：
create table test(user_id int ,channel varchar(100),dt datetime)
insert into test(user_id,channel,dt)
select 1,'A','2020/6/28'
union select 1 ,'A', '2020/6/29'
union select 2, 'A','2020/6/30'
union select 2, 'V','2020/7/1'
union select 3,'A','2020/7/2'
union select 3, 'S','2020/7/3'
union select 3,'V','2020/7/4'
select a.user_id,a.channel,a.dt from (
select ROW_NUMBER() over(partition by user_id order by user_id,channel,dt) rownum,* from test a
)a
where rownum=1 and user_id not in (select user_id from test a where channel<>'A' group by user_id )
union
select * from test a
where user_id in (select user_id from test a where channel<>'A' group by user_id )
and a.channel<>'A'
uj5u.com熱心網友回復：
SELECT *
FROM TABLE A
WHERE NOT EXISTS
(SELECT 1 FROM TABLE WHERE USER_ID=A.USER_ID AND CHANEL<>'A')
AND NOT EXISTS (SELECT 1 FROM TABLE WHERE USER_ID=A.USER_ID AND DT<A.DT )
UNION ALL
SELECT *
FROM TABLE A
WHERE EXISTS
(SELECT 1 FROM TABLE WHERE USER_ID=A.USER_ID AND CHANEL<>'A')
AND CHANEL<>'A'
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/21094.html
標籤：疑難問題