题目地址

https://leetcode.cn/problems/leetflex-banned-accounts/description/

代码

with t1 as(
    select account_id,ip_address,
    login as tick,
    "login" as mytype
    from LogInfo
    union all
    select account_id,ip_address,
    logout as tick,
    "logout" as mytype
    from LogInfo
)
,
t2 as(
    SELECT 
        t.*,

        # ----只需要改动这里的逻辑，其他不要动-----------
        @prev_online_ips := @online_ips, 
        @search_for := CONCAT(',', t.ip_address) as search_for,
        @first_occurrence := LOCATE(@search_for, @online_ips) as first_occurrence,


        @online_ips := (case when account_id=@prev_account_id then
            (CASE 
            WHEN t.mytype = 'login' THEN CONCAT_WS(',', @online_ips, t.ip_address)
            WHEN t.mytype = 'logout' THEN 

            (CASE
                WHEN @first_occurrence > 0 THEN -- 找到第一个匹配的ip，移除它
                    CONCAT(
                        SUBSTRING(@online_ips, 1, @first_occurrence - 1), 
                        SUBSTRING(@online_ips, @first_occurrence + LENGTH(@search_for))
                    )
                ELSE
                    @online_ips
            END)

            ELSE @online_ips END)
        else (CASE 
            WHEN t.mytype = 'login' THEN CONCAT_WS(',', '', t.ip_address)
            WHEN t.mytype = 'logout' THEN 

            (CASE
                WHEN @first_occurrence > 0 THEN -- 找到第一个匹配的ip，移除它
                    CONCAT(
                        SUBSTRING(@online_ips, 1, @first_occurrence - 1), 
                        SUBSTRING(@online_ips, @first_occurrence + LENGTH(@search_for))
                    )
                ELSE
                    @online_ips
            END)

            ELSE @online_ips END)
        end) AS dummy -- This is a dummy column to update the @online_ips variable

        ,

        @online_count := 
        (case when account_id=@prev_account_id then 
            (CASE 
            WHEN t.mytype = 'login'  THEN  
                (case when FIND_IN_SET(t.ip_address, @online_ips) > 0 and FIND_IN_SET(t.ip_address, @prev_online_ips) = 0
                    then @online_count+1
                    else @online_count
                end)
            WHEN t.mytype = 'logout' THEN
                (case when FIND_IN_SET(t.ip_address, @online_ips) = 0 and FIND_IN_SET(t.ip_address, @prev_online_ips) > 0  
                  then @online_count-1
                    else @online_count
                end)
            ELSE @online_count END)
         else (CASE 
            WHEN t.mytype = 'login'  THEN  1
            WHEN t.mytype = 'logout' THEN 
                (case when FIND_IN_SET(t.ip_address, @online_ips) = 0 and FIND_IN_SET(t.ip_address, @prev_online_ips) > 0  
                    then @online_count-1
                        else @online_count
                end)
            ELSE @online_count END)
        end )
        
        AS online_ip_count

        , @prev_account_id := account_id

        # ----只需要改动这里的逻辑，其他不要动-----------
    FROM 
        (SELECT @online_ips := '', @online_ip_count := 0, @prev_account_id := null) vars,
        (SELECT * FROM t1 ORDER BY account_id asc, tick ASC) t
)


select 
distinct account_id 
from t2
where online_ip_count>=2
order by account_id asc, tick ASC