The merchant POJ - 3728 lca+ dp思想

本文深入探讨了LCA(最近公共祖先)算法的具体实现细节,包括节点间的最大值查询等高级应用。提供了完整的C++代码示例,并通过具体案例进行了解释说明。

参考别人的用在线的lca不知道为什么错。。
https://vjudge.net/solution/2554998



#include<algorithm>
#include<vector>
 #include<cstring>
#include<string>
#include<iomanip>
#include<cstdio>
#include<stack>
#include<iostream>
#include<map>
#include<queue>
#include<cmath>
using namespace std;
#define sf scanf
#define pf printf
#define mem(a,b) memset(a,b,sizeof(a));
#define rep(i,a,b) for(int i=(a);i<=(b);++i)
#define mod 9901
#define N 100020
#define ULL unsigned long long
#define LL long long
#define inf 0x3f3f3f3f
#define mxn 25020
#define mxe 1002000
#define make_pair

//2017年08月16日08:14:03
int n,q;
int up[22][N],down[22][N],mx[22][N],mi[22][N],w[N],dep[N],fst[N],vv[N<<1],nxt[N<<1];
int tot,fa[22][N];
void init(){ mem(fst,-1);tot=0; }
void add(int u,int v){
    vv[tot]=v;nxt[tot]=fst[u];fst[u]=tot++;
}
void dfs(int u,int p){
    fa[0][u]=p;
    if(p!=-1){
        mx[0][u]=max(w[u],w[p]);
        mi[0][u]=min(w[u],w[p]);
        up[0][u]=max(0,w[u]-w[p]);
        down[0][u]=max(0,w[p]-w[u]);
    }
    else{
        up[0][u]=down[0][u]=-1;
        mx[0][u]=-1;
        mi[0][u]=inf;
    }
    for(int i=fst[u];~i;i=nxt[i]){
        int v=vv[i];
        if(v!=p){
            dep[v]=dep[u]+1;
            dfs(v,u);
        }
    }
}
void init_lca(){
    dep[1]=1;
    dfs(1,-1);
    for(int k=0;k<=20;++k){
        for(int i=1;i<=n;++i){
            if(fa[k][i]==-1)fa[k+1][i]=-1;
            else{
                fa[k+1][i]=fa[k][fa[k][i]];
                if(fa[k+1][i]!=-1){
                    mx[k+1][i]=max(mx[k][i],mx[k][fa[k][i]]);
                    mi[k+1][i]=min(mi[k][i],mi[k][fa[k][i]]);
                    //up[k+1][i]=max(max(up[k][i],up[k][fa[k][i]]),mx[k][fa[k][i]]-mi[k][i]);
                    //down[k+1][i]=max(max(down[k][i],down[k][fa[k][i]]),mx[k][i]-mi[k][fa[k][i]]);
                }
            }
        }
    }
}

int lca(int u,int v){
    if(dep[u]>dep[v]){swap(u,v);}
    for(int k=0;k<20;++k){
        if((dep[v]-dep[u])>>k&1)
            v=fa[k][v];
    }
    if(u==v)return u;
    for(int k=19;k>=0;--k){
        if(fa[k][u]!=fa[k][v])
            u=fa[k][u],v=fa[k][v];
    }
    return fa[0][u];
}

int qmax1(int u,int p){
     int ret=0,M=0;
     if(u==p)return 0;
     for(int k=19;k>=0;--k){
         if(fa[k][u]!=-1&&dep[fa[k][u]]>=dep[p]){
            ret=max(ret,up[k][u]);
             ret=max(ret,M-mi[k][u]);
             M=max(M,mx[k][u]);
             u=fa[k][u];
         }
     }
     pf("res: %d\n",ret);
     return ret;
}
int qmax2(int u,int p){
    if(u==p)return 0;
    pf("%d %d\n",u,p);
    int ret=0,M=inf;
    for(int k=19;k>=0;--k){
        if(fa[k][u]!=-1&&dep[fa[k][u]]>=dep[p]){
            pf("k: %d\n",k);
            pf("k:%d  u:%d    down[k][u]: %d\n",k,u,down[k][u]);
            ret=max(ret,down[k][u]);
            ret=max(ret,mx[k][u]-M);
            M=min(M,mi[k][u]);
            u=fa[k][u];
        }
    }
     pf("res: %d\n",ret);
    return ret;
}
int cas=0;
int  solve(int u,int v){
    int p=lca(u,v);
    pf("CAs %d :%d\n",++cas,p);
    if(p==u){return qmax2(v,u);}
    else if(p==v){return qmax1(u,v);}
    int ans=0;
    ans=max(ans,qmax1(u,p));
    ans=max(ans,qmax2(v,p));
    pf("ans: %d\n",ans);
    int MX=0,MI=inf;
    for(int k=19;k>=0;--k){
        if(fa[k][u]!=-1&&dep[fa[k][u]]>=dep[p]){
            MX=max(MX,mx[k][u]);
            u=fa[k][u];
        }
        if(fa[k][v]!=-1&&dep[fa[k][v]]>=dep[p]){
            MI=min(MI,mi[k][v]);
            v=fa[k][v];
        }
    }
    ans=max(ans,MX-MI);
    return ans;
}
int main(){
    freopen("in.txt","r",stdin);
    while(~sf("%d",&n)){
        init();
        rep(i,1,n){sf("%d",&w[i]);}
        rep(i,1,n-1){int u,v;sf("%d%d",&u,&v);add(u,v),add(v,u);}
        init_lca();
        sf("%d",&q);
        while(q--){
            int u,v;sf("%d%d",&u,&v);
            pf("%d\n",solve(u,v));
        }
        pf("test\n");
        for(int i=1;i<=n;++i){
            for(int k=0;k<=2;++k){
                pf("%d ",down[k][i]);
            }puts("");
        }
    }
}

这个地方还看到别人用并查集写,挺好的。。
参考http://blog.youkuaiyun.com/sdj222555/article/details/43003179

[root@yfw ~]# cd /www/wwwroot/szrengjing.com [root@yfw szrengjing.com]# mysql -u szrengjing_com -p szrengjing_com Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 681367 Server version: 5.7.42-log Source distribution Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> -- 1. 查客服分配规则表(ecs_chat_allot_rule)- 全站客服分配规则大概率在这里 mysql> SELECT * FROM ecs_chat_allot_rule WHERE kefu_account = 'kf_rjsc' OR rule_value LIKE '%kf_rjsc%'; ERROR 1054 (42S22): Unknown column 'kefu_account' in 'where clause' mysql> mysql> -- 2. 查客服基础信息表(ecs_chat_customer) mysql> SELECT * FROM ecs_chat_customer WHERE customer_account = 'kf_rjsc' OR customer_name LIKE '%kf_rjsc%'; ERROR 1054 (42S22): Unknown column 'customer_account' in 'where clause' mysql> mysql> -- 3. 查客服状态表(ecs_chat_customer_status) mysql> SELECT * FROM ecs_chat_customer_status WHERE customer_account = 'kf_rjsc'; ERROR 1054 (42S22): Unknown column 'customer_account' in 'where clause' mysql> mysql> -- 4. 查聊天会话表(ecs_chat_session)- 看该客服是否被全站会话关联 mysql> SELECT COUNT(*) AS total_session, shop_id FROM ecs_chat_session WHERE kefu_account = 'kf_rjsc' GROUP BY shop_id; ERROR 1054 (42S22): Unknown column 'shop_id' in 'field list' mysql> -- 1. 查主站店铺配置中是否把kf_rjsc设为默认客服 mysql> SELECT * FROM ecs_shop_config -> WHERE (config_key LIKE '%kefu%' OR config_key LIKE '%customer%' OR config_key LIKE '%service%') -> AND config_value = 'kf_rjsc'; ERROR 1054 (42S22): Unknown column 'config_key' in 'where clause' mysql> mysql> -- 2. 查供应商(店铺)专属配置中是否批量配置了kf_rjsc mysql> SELECT * FROM ecs_supplier_shop_config -> WHERE (config_key LIKE '%kefu%' OR config_key LIKE '%customer%') -> AND config_value = 'kf_rjsc'; ERROR 1054 (42S22): Unknown column 'config_key' in 'where clause' mysql> mysql> -- 3. 查店铺等级/供应商表 - 看是否把kf_rjsc设为全店铺默认 mysql> SELECT * FROM ecs_shop_grade WHERE grade_config LIKE '%kf_rjsc%'; ERROR 1054 (42S22): Unknown column 'grade_config' in 'where clause' mysql> SELECT * FROM ecs_supplier WHERE supplier_config LIKE '%kf_rjsc%'; ERROR 1054 (42S22): Unknown column 'supplier_config' in 'where clause' mysql> -- 1. 查管理员配置表 mysql> SELECT * FROM ecs_admin_user WHERE user_name = 'kf_rjsc' OR password LIKE '%kf_rjsc%' OR remarks LIKE '%kf_rjsc%'; ERROR 1054 (42S22): Unknown column 'remarks' in 'where clause' mysql> mysql> -- 2. 查系统配置表(兜底) mysql> SELECT * FROM ecs_ecsmart_shop_config -> WHERE (config_key LIKE '%default_kefu%' OR config_key LIKE '%global_service%') -> AND config_value = 'kf_rjsc'; ERROR 1054 (42S22): Unknown column 'config_key' in 'where clause' mysql> -- ========== 1. 客服聊天系列表 字段结构 ========== mysql> -- 客服分配规则表 mysql> DESC ecs_chat_allot_rule; +-------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------+------+-----+---------+----------------+ | rule_id | int(11) | NO | PRI | NULL | auto_increment | | supp_id | int(11) | NO | MUL | NULL | | | cus_id | int(11) | NO | | NULL | | | priority | int(11) | NO | | 1 | | | max_session | int(11) | NO | | 50 | | | is_enable | tinyint(4) | NO | | 1 | | +-------------+------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> -- 客服基础信息表 mysql> DESC ecs_chat_customer; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | cus_id | mediumint(7) | NO | PRI | NULL | auto_increment | | user_id | mediumint(7) | NO | | NULL | | | of_username | varchar(120) | NO | | NULL | | | cus_name | varchar(120) | NO | | NULL | | | supp_id | mediumint(7) | NO | | -1 | | | cus_type | int(10) | NO | | 0 | | | session_count | int(10) | YES | | 0 | | | talk_time | int(10) | YES | | 0 | | | cus_degree | int(10) | YES | | 0 | | | online_status | tinyint(1) | YES | | 0 | | | cus_enable | int(10) | YES | | 1 | | | add_time | int(10) | YES | | NULL | | +---------------+--------------+------+-----+---------+----------------+ 12 rows in set (0.00 sec) mysql> -- 客服状态表 mysql> DESC ecs_chat_customer_status; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | cus_id | int(11) | NO | PRI | NULL | auto_increment | | cus_name | varchar(64) | NO | | NULL | | | supp_id | int(11) | NO | MUL | 0 | | | is_super | tinyint(1) | YES | MUL | 0 | | | kefu_type | tinyint(1) | NO | | 1 | | | is_admin | tinyint(1) | NO | | 0 | | | status | tinyint(1) | YES | MUL | 1 | | | busy | tinyint(1) | YES | | 0 | | | last_active | int(11) | NO | | 0 | | | create_time | int(11) | NO | | 0 | | | cus_account | varchar(32) | NO | UNI | NULL | | | cus_pwd | varchar(32) | NO | | NULL | | | data_scope | tinyint(4) | NO | | 1 | | +-------------+-------------+------+-----+---------+----------------+ 13 rows in set (0.00 sec) mysql> -- 聊天会话表 mysql> DESC ecs_chat_session; +------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+-------+ | session_id | varchar(64) | NO | PRI | NULL | | | user_id | int(11) | NO | MUL | NULL | | | user_mobile | varchar(20) | NO | | | | | cus_id | int(11) | NO | MUL | NULL | | | supp_id | int(11) | NO | MUL | NULL | | | start_time | int(10) | YES | | 0 | | | last_msg_time | int(11) | NO | | NULL | | | end_time | int(10) | YES | | 0 | | | status | tinyint(1) | YES | | 1 | | | evaluate_score | tinyint(1) | YES | | 0 | | | evaluate_content | text | YES | | NULL | | | evaluate_time | int(11) | YES | | 0 | | | tenant_id | int(11) | NO | | NULL | | +------------------+-------------+------+-----+---------+-------+ 13 rows in set (0.00 sec) mysql> mysql> -- ========== 2. 店铺配置系列表 字段结构 ========== mysql> -- 主站店铺配置表 mysql> DESC ecs_shop_config; +-------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | parent_id | smallint(5) unsigned | NO | MUL | 0 | | | code | varchar(30) | NO | UNI | | | | type | varchar(10) | NO | | | | | store_range | varchar(255) | NO | | | | | store_dir | varchar(255) | NO | | | | | value | text | NO | | NULL | | | sort_order | tinyint(3) unsigned | NO | | 1 | | +-------------+----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) mysql> -- 供应商店铺配置表 mysql> DESC ecs_supplier_shop_config; +-------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | 0 | | | parent_id | smallint(5) unsigned | NO | MUL | 0 | | | code | varchar(30) | NO | | | | | type | varchar(10) | NO | | | | | store_range | varchar(255) | NO | | | | | store_dir | varchar(255) | NO | | | | | value | text | NO | | NULL | | | sort_order | tinyint(3) unsigned | NO | | 1 | | | supplier_id | int(10) unsigned | NO | PRI | 0 | | +-------------+----------------------+------+-----+---------+-------+ 9 rows in set (0.00 sec) mysql> mysql> -- ========== 3. 店铺/供应商基础表 字段结构 ========== mysql> -- 店铺等级表 mysql> DESC ecs_shop_grade; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | grade_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | user_id | int(10) unsigned | NO | | 0 | | | user_name | varchar(60) | NO | | NULL | | | add_time | int(10) unsigned | NO | | 0 | | | comment_rank | tinyint(1) unsigned | NO | | 0 | | | server | tinyint(1) unsigned | NO | | 0 | | | send | tinyint(1) unsigned | NO | | 0 | | | shipping | tinyint(1) unsigned | NO | | 0 | | | order_id | mediumint(8) unsigned | NO | | 0 | | | order_sn | varchar(20) | NO | | NULL | | | is_comment | tinyint(1) unsigned | NO | | 1 | | +--------------+-----------------------+------+-----+---------+----------------+ 11 rows in set (0.00 sec) mysql> -- 供应商表 mysql> DESC ecs_supplier; +-----------------------------------------+------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------------------+------------------------+------+-----+---------+----------------+ | supplier_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment | | user_id | mediumint(8) unsigned | NO | MUL | 0 | | | supplier_name | varchar(255) | NO | | NULL | | | rank_id | tinyint(3) unsigned | NO | | 0 | | | type_id | tinyint(3) unsigned | NO | | 0 | | | company_name | varchar(255) | NO | | NULL | | | country | smallint(5) unsigned | NO | | NULL | | | province | smallint(5) unsigned | NO | | NULL | | | city | smallint(5) unsigned | NO | | NULL | | | district | smallint(5) unsigned | NO | | NULL | | | address | varchar(255) | NO | | | | | tel | varchar(50) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | | guimo | varchar(255) | NO | | NULL | | | company_type | varchar(50) | NO | | NULL | | | bank | varchar(255) | NO | | NULL | | | zhizhao | varchar(255) | NO | | NULL | | | contact | varchar(255) | NO | | NULL | | | id_card | varchar(20) | NO | | NULL | | | contact_back | varchar(255) | NO | | NULL | | | contact_shop | varchar(255) | NO | | NULL | | | contact_yunying | varchar(255) | NO | | NULL | | | contact_shouhou | varchar(255) | NO | | NULL | | | contact_caiwu | varchar(255) | NO | | NULL | | | contact_jishu | varchar(255) | NO | | NULL | | | system_fee | decimal(10,2) unsigned | NO | | 0.00 | | | supplier_bond | decimal(10,2) unsigned | NO | | 0.00 | | | supplier_rebate | tinyint(3) unsigned | NO | | 0 | | | supplier_rebate_paytime | tinyint(1) unsigned | NO | | 0 | | | supplier_remark | varchar(255) | NO | | | | | nav_list | text | NO | | NULL | | | status | tinyint(1) | NO | | 0 | | | add_time | int(10) | NO | | 0 | | | applynum | smallint(1) unsigned | NO | | 0 | | | contacts_name | varchar(100) | NO | | | | | contacts_phone | varchar(50) | NO | | | | | business_licence_number | varchar(100) | NO | | | | | business_sphere | text | NO | | NULL | | | organization_code | varchar(100) | NO | | NULL | | | organization_code_electronic | varchar(255) | NO | | NULL | | | general_taxpayer | varchar(255) | NO | | NULL | | | bank_account_name | varchar(100) | NO | | NULL | | | bank_account_number | varchar(100) | NO | | NULL | | | bank_name | varchar(100) | NO | | NULL | | | bank_code | varchar(100) | NO | | NULL | | | settlement_bank_account_name | varchar(100) | NO | | NULL | | | settlement_bank_account_number | varchar(100) | NO | | NULL | | | settlement_bank_name | varchar(100) | NO | | NULL | | | settlement_bank_code | varchar(100) | NO | | NULL | | | tax_registration_certificate | varchar(100) | NO | | NULL | | | taxpayer_id | varchar(100) | NO | | NULL | | | bank_licence_electronic | varchar(255) | NO | | NULL | | | tax_registration_certificate_electronic | varchar(255) | NO | | NULL | | | supplier_money | decimal(10,2) | NO | | 0.00 | | | handheld_idcard | varchar(255) | NO | | NULL | | | idcard_front | varchar(255) | NO | | NULL | | | idcard_reverse | varchar(255) | NO | | NULL | | | id_card_no | varchar(20) | NO | | NULL | | | is_check | tinyint(1) | YES | | 1 | | +-----------------------------------------+------------------------+------+-----+---------+----------------+ 59 rows in set (0.00 sec) mysql> -- 管理员表 mysql> DESC ecs_admin_user; +--------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+---------+----------------+ | user_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | user_name | varchar(60) | NO | MUL | | | | email | varchar(60) | NO | | | | | password | varchar(32) | NO | | | | | ec_salt | varchar(10) | YES | | NULL | | | add_time | int(11) | NO | | 0 | | | last_login | int(11) | NO | | 0 | | | last_ip | varchar(15) | NO | | | | | action_list | text | NO | | NULL | | | nav_list | text | NO | | NULL | | | lang_type | varchar(50) | NO | | | | | agency_id | smallint(5) unsigned | NO | MUL | NULL | | | suppliers_id | smallint(5) unsigned | YES | | 0 | | | todolist | longtext | YES | | NULL | | | role_id | smallint(5) | YES | | NULL | | +--------------+----------------------+------+-----+---------+----------------+ 15 rows in set (0.00 sec) mysql> -- 系统配置表 mysql> DESC ecs_ecsmart_shop_config; +-------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | parent_id | smallint(5) unsigned | NO | MUL | 0 | | | code | varchar(30) | NO | UNI | | | | type | varchar(10) | NO | | | | | store_range | varchar(255) | NO | | | | | store_dir | varchar(255) | NO | | | | | value | text | NO | | NULL | | | sort_order | tinyint(3) unsigned | NO | | 1 | | +-------------+----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) mysql> -- 1. 查kf_rjsc在客服状态表中的核心信息(包含客服ID、所属店铺、是否超级客服) mysql> SELECT * FROM ecs_chat_customer_status WHERE cus_account = 'kf_rjsc'; +--------+--------------------+---------+----------+-----------+----------+--------+------+-------------+-------------+-------------+----------------------------------+------------+ | cus_id | cus_name | supp_id | is_super | kefu_type | is_admin | status | busy | last_active | create_time | cus_account | cus_pwd | data_scope | +--------+--------------------+---------+----------+-----------+----------+--------+------+-------------+-------------+-------------+----------------------------------+------------+ | 19 | 人进店铺客服 | 24 | 0 | 1 | 0 | 0 | 0 | 1765960428 | 0 | kf_rjsc | 25d55ad283aa400af464c76d713c07ad | 1 | +--------+--------------------+---------+----------+-----------+----------+--------+------+-------------+-------------+-------------+----------------------------------+------------+ 1 row in set (0.00 sec) mysql> mysql> -- 2. 查kf_rjsc在客服基础表中的关联信息(确所属供应商/店铺) mysql> SELECT * FROM ecs_chat_customer WHERE of_username = 'kf_rjsc' OR cus_name LIKE '%kf_rjsc%'; Empty set (0.00 sec) mysql> mysql> -- 关键字段说明: mysql> -- supp_id = 供应商/店铺ID(-1代表全站,0代表主站,>0代表具体店铺) mysql> -- is_super = 是否超级客服(1=是,0=否) mysql> -- data_scope = 数据范围(1=全站,2=仅所属店铺) mysql> -- 1. 把data_scope从1(全站)改为2(仅所属店铺supp_id=24) mysql> UPDATE ecs_chat_customer_status -> SET data_scope = 2 -> WHERE cus_account = 'kf_rjsc' AND cus_id = 19; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> -- 2. 清理该客服的全站分配规则(若有) mysql> DELETE FROM ecs_chat_allot_rule -> WHERE cus_id = 19 AND supp_id != 24; -- 仅保留supp_id=24的分配规则 Query OK, 0 rows affected (0.02 sec) mysql> mysql> -- 3. 确保仅关联店铺24(若无分配规则则新增) mysql> INSERT INTO ecs_chat_allot_rule (supp_id, cus_id, priority, max_session, is_enable) -> VALUES (24, 19, 1, 50, 1) -> ON DUPLICATE KEY UPDATE supp_id = 24; Query OK, 1 row affected (0.04 sec) mysql> -- 1. 确认权限已重置 mysql> SELECT cus_account, supp_id, is_super, data_scope -> FROM ecs_chat_customer_status -> WHERE cus_id = 19; +-------------+---------+----------+------------+ | cus_account | supp_id | is_super | data_scope | +-------------+---------+----------+------------+ | kf_rjsc | 24 | 0 | 2 | +-------------+---------+----------+------------+ 1 row in set (0.00 sec) mysql> -- 预期结果:data_scope=2,is_super=0,supp_id=24 mysql> mysql> -- 2. 确认分配规则仅关联店铺24 mysql> SELECT supp_id, cus_id, is_enable -> FROM ecs_chat_allot_rule -> WHERE cus_id = 19; +---------+--------+-----------+ | supp_id | cus_id | is_enable | +---------+--------+-----------+ | 24 | 19 | 1 | | 24 | 19 | 1 | +---------+--------+-----------+ 2 rows in set (0.00 sec) mysql> -- 预期结果:仅一条记录,supp_id=24,is_enable=1 mysql> mysql> -- 3. 确认历史会话仅店铺24关联该客服 mysql> SELECT COUNT(*) AS session_count, supp_id -> FROM ecs_chat_session -> WHERE cus_id = 19 -> GROUP BY supp_id; +---------------+---------+ | session_count | supp_id | +---------------+---------+ | 22 | 24 | +---------------+---------+ 1 row in set (0.00 sec) mysql> -- 预期结果:仅supp_id=24有会话,其他店铺无 mysql> -- 去重ecs_chat_allot_rule中cus_id=19、supp_id=24的重复记录 mysql> DELETE FROM ecs_chat_allot_rule -> WHERE rule_id NOT IN ( -> SELECT MIN(rule_id) -> FROM ecs_chat_allot_rule -> WHERE cus_id=19 AND supp_id=24 -> GROUP BY supp_id, cus_id -> ); ERROR 1093 (HY000): You can't specify target table 'ecs_chat_allot_rule' for update in FROM clause mysql> mysql> -- 去重后再次验证 mysql> SELECT supp_id, cus_id, is_enable -> FROM ecs_chat_allot_rule -> WHERE cus_id = 19; +---------+--------+-----------+ | supp_id | cus_id | is_enable | +---------+--------+-----------+ | 24 | 19 | 1 | | 24 | 19 | 1 | +---------+--------+-----------+ 2 rows in set (0.00 sec) mysql> -- 预期结果:仅1条记录,supp_id=24,is_enable=1 mysql> -- 检查系统配置中是否有硬编码kf_rjsc的全局配置 mysql> SELECT * FROM ecs_shop_config -> WHERE code LIKE '%kefu%' OR code LIKE '%customer%' -> AND value LIKE '%kf_rjsc%'; Empty set (0.00 sec) mysql> mysql> SELECT * FROM ecs_ecsmart_shop_config -> WHERE code LIKE '%kefu%' OR code LIKE '%customer%' -> AND value LIKE '%kf_rjsc%'; Empty set (0.00 sec) mysql> mysql> -- 若查到则清空(避免前端读取配置时仍显示) mysql> UPDATE ecs_shop_config SET value = '' WHERE value = 'kf_rjsc' AND code LIKE '%kefu%'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> UPDATE ecs_ecsmart_shop_config SET value = '' WHERE value = 'kf_rjsc' AND code LIKE '%kefu%'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> -- 正确的去重语句(规避1093报错) mysql> DELETE FROM ecs_chat_allot_rule -> WHERE rule_id NOT IN ( -> SELECT min_rule_id FROM ( -> SELECT MIN(rule_id) AS min_rule_id -> FROM ecs_chat_allot_rule -> WHERE cus_id=19 AND supp_id=24 -> GROUP BY supp_id, cus_id -> ) AS temp_table -> ); Query OK, 5 rows affected (0.01 sec) mysql> mysql> -- 去重后验证 mysql> SELECT supp_id, cus_id, is_enable -> FROM ecs_chat_allot_rule -> WHERE cus_id = 19; +---------+--------+-----------+ | supp_id | cus_id | is_enable | +---------+--------+-----------+ | 24 | 19 | 1 | +---------+--------+-----------+ 1 row in set (0.04 sec) mysql> -- 预期结果:仅1条记录,supp_id=24,is_enable=1 mysql> -- 禁用kf_rjsc(cus_id=19),前端加载不到该客服 mysql> UPDATE ecs_chat_customer_status -> SET status = 0, cus_enable = 0, busy = 1 -> WHERE cus_id = 19; ERROR 1054 (42S22): Unknown column 'cus_enable' in 'field list' mysql> mysql> -- 验证:查询该客服状态应为禁用 mysql> SELECT cus_account, status, cus_enable, busy -> FROM ecs_chat_customer_status -> WHERE cus_id = 19; ERROR 1054 (42S22): Unknown column 'cus_enable' in 'field list' mysql> -- 预期结果:status=0, cus_enable=0, busy=1 mysql> -- 仅修改存在的字段:status=0(离线)、busy=1(忙碌),前端默认不显示离线/忙碌客服 mysql> UPDATE ecs_chat_customer_status -> SET status = 0, busy = 1 -> WHERE cus_id = 19; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> -- 验证禁用结果 mysql> SELECT cus_account, supp_id, status, busy, data_scope -> FROM ecs_chat_customer_status -> WHERE cus_id = 19; +-------------+---------+--------+------+------------+ | cus_account | supp_id | status | busy | data_scope | +-------------+---------+--------+------+------------+ | kf_rjsc | 24 | 0 | 1 | 2 | +-------------+---------+--------+------+------------+ 1 row in set (0.01 sec) mysql> -- 预期结果:status=0,busy=1,data_scope=2,supp_id=24 mysql> UPDATE ecs_chat_customer_status SET status = 0, busy = 1 WHERE cus_id = 19; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> -- 禁用kf_rjsc在主页显示(status=0离线) mysql> UPDATE ecs_chat_customer_status SET status = 0, busy = 1 WHERE cus_id = 19; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> -- 24号店铺需要显示时,执行: mysql> -- UPDATE ecs_chat_customer_status SET status = 1, busy = 0 WHERE cus_id = 19; mysql> UPDATE ecs_chat_customer_status SET status = 1, busy = 0 WHERE cus_id = 19; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> -- 1. 给ecs_chat_customer_status新增商户名称/客服类型字段(便于管理) mysql> ALTER TABLE ecs_chat_customer_status -> ADD COLUMN merchant_name VARCHAR(50) COMMENT '商户名称' AFTER supp_id, -> ADD COLUMN kefu_type TINYINT DEFAULT 1 COMMENT '客服类型:1=普通客服,2=店长,3=售后' AFTER data_scope, -> ADD COLUMN online_time VARCHAR(20) DEFAULT '09:00-22:00' COMMENT '在线时段' AFTER kefu_type; ERROR 1060 (42S21): Duplicate column name 'kefu_type' mysql> mysql> -- 2. 批量更新24号店铺客服的商户名称(示例) mysql> UPDATE ecs_chat_customer_status SET merchant_name = '人进商城' WHERE supp_id = 24; ERROR 1054 (42S22): Unknown column 'merchant_name' in 'field list' mysql> mysql> -- 3. 给每个商户配置专属客服分组(可选) mysql> CREATE TABLE ecs_chat_kefu_group ( -> group_id INT PRIMARY KEY AUTO_INCREMENT, -> supp_id INT NOT NULL COMMENT '商户ID', -> group_name VARCHAR(30) NOT NULL COMMENT '分组名称(售前/售后)', -> create_time INT NOT NULL COMMENT '创建时间' -> ); Query OK, 0 rows affected (0.18 sec) mysql> -- 1. 给客服状态表补充商户名称/在线时段(解决"看不到商户名称"问题) mysql> ALTER TABLE ecs_chat_customer_status -> ADD COLUMN merchant_name VARCHAR(50) COMMENT '商户名称' AFTER supp_id, -> ADD COLUMN online_time VARCHAR(20) DEFAULT '09:00-22:00' COMMENT '在线时段' AFTER kefu_type; Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> mysql> -- 2. 批量更新已有商户的客服名称(示例:24号商户=人进商城) mysql> UPDATE ecs_chat_customer_status SET merchant_name = '人进商城' WHERE supp_id = 24; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> mysql> -- 3. 给客服分配规则表补充"最后更新时间"(便于排查分配规则变更) mysql> ALTER TABLE ecs_chat_allot_rule -> ADD COLUMN update_time INT NOT NULL DEFAULT 0 COMMENT '最后更新时间' AFTER is_enable; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> -- 1. 客服分组表(已创建,补充索引优化查询) mysql> ALTER TABLE ecs_chat_kefu_group -> ADD INDEX idx_supp_id (supp_id); -- 按商户查询分组时提速 Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> -- 2. 给客服状态表关联分组ID(支持按分组分配) mysql> ALTER TABLE ecs_chat_customer_status -> ADD COLUMN group_id INT DEFAULT 0 COMMENT '所属分组ID(关联ecs_chat_kefu_group)' AFTER kefu_type; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> mysql> -- 3. 初始化24号商户的分组(示例:售前/售后) mysql> INSERT INTO ecs_chat_kefu_group (supp_id, group_name, create_time) -> VALUES (24, '售前客服', UNIX_TIMESTAMP()), (24, '售后客服', UNIX_TIMESTAMP()); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> -- 4. 给kf_rjsc绑定24号商户的"售前客服"分组(假设group_id=1) mysql> UPDATE ecs_chat_customer_status SET group_id = 1 WHERE cus_id = 19; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> -- 1. 商户客服配置表(支持每个商户自定义规则) mysql> CREATE TABLE ecs_supplier_kefu_config ( -> config_id INT PRIMARY KEY AUTO_INCREMENT, -> supp_id INT NOT NULL COMMENT '商户ID', -> default_cus_id INT DEFAULT 0 COMMENT '默认客服ID', -> max_session_per_kefu INT DEFAULT 50 COMMENT '单客服最大会话数', -> auto_allot TINYINT DEFAULT 1 COMMENT '是否自动分配(1=是,0=否)', -> create_time INT NOT NULL COMMENT '创建时间', -> update_time INT NOT NULL COMMENT '更新时间', -> INDEX idx_supp_id (supp_id) -> ) COMMENT '商户客服自定义配置表'; Query OK, 0 rows affected (0.05 sec) mysql> mysql> -- 2. 初始化24号商户的客服配置 mysql> INSERT INTO ecs_supplier_kefu_config (supp_id, default_cus_id, max_session_per_kefu, auto_allot, create_time, update_time) -> VALUES (24, 19, 50, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()); Query OK, 1 row affected (0.02 sec) mysql>
最新发布
12-19
mysql> EXPLAIN SELECT -> id, flow_order_no, winning_losing, currency_type, bet_money, -> order_status, created_at, game_code, game_name, member_id, -> member_account, merchant_id, merchant_code, merchant_name, bet_at -> FROM -> anchor_bet_record -> FORCE INDEX (idx_optimized_query) -> WHERE -> merchant_code = '5273' -> AND member_account = 'xk139880305' -> AND game_code = 'bjl' -> AND created_at > '2025-09-28 09:32:32.953' -> AND is_deleted = 0 -> ORDER BY -> created_at ASC -> LIMIT 10; +------------------------------------+-----------+-----------+------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------+-----------+-----------+------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_7 | 10.00 | root | | anchor_game.anchor_bet_record.id, anchor_game.anchor_bet_record.flow_order_no, anchor_game.anchor_bet_record.winning_losing, anchor_game.anchor_bet_record.currency_type, anchor_game.anchor_bet_record.bet_money, anchor_game.anchor_bet_record.order_status, anchor_game.anchor_bet_record.created_at, anchor_game.anchor_bet_record.game_code, anchor_game.anchor_bet_record.game_name, anchor_game.anchor_bet_record.member_id, anchor_game.anchor_bet_record.member_account, anchor_game.anchor_bet_record.merchant_id, anchor_game.anchor_bet_record.merchant_code, anchor_game.anchor_bet_record.merchant_name, anchor_game.anchor_bet_record.bet_at | | └─TopN_9 | 10.00 | root | | anchor_game.anchor_bet_record.created_at, offset:0, count:10 | | └─IndexLookUp_20 | 10.00 | root | | | | ├─IndexRangeScan_13(Build) | 473110.00 | cop[tikv] | table:anchor_bet_record, index:idx_merchant_bet(merchant_code, bet_at) | range:["5273","5273"], keep order:false | | └─TopN_19(Probe) | 10.00 | cop[tikv] | | anchor_game.anchor_bet_record.created_at, offset:0, count:10 | | └─Selection_15 | 910.05 | cop[tikv] | | eq(anchor_game.anchor_bet_record.game_code, "bjl"), eq(anchor_game.anchor_bet_record.is_deleted, 0), eq(anchor_game.anchor_bet_record.member_account, "xk139880305"), gt(anchor_game.anchor_bet_record.created_at, 2025-09-28 09:32:32.953000) | | └─TableRowIDScan_14 | 473110.00 | cop[tikv] | table:anchor_bet_record | keep order:false | +------------------------------------+-----------+-----------+------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.01 sec) mysql> mysql> mysql> //////////////////////////////////////////// ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 1 near "////////////////////////////////////////////" mysql> mysql> EXPLAIN SELECT -> id, flow_order_no, winning_losing, currency_type, bet_money, -> order_status, created_at, game_code, game_name, member_id, -> member_account, merchant_id, merchant_code, merchant_name, bet_at -> FROM -> anchor_bet_record -> -> WHERE -> merchant_code = '5273' -> AND member_account = 'xk139880305' -> AND game_code = 'bjl' -> AND created_at > '2025-09-28 09:32:32.953' -> AND is_deleted = 0 -> ORDER BY -> created_at ASC -> LIMIT 10; +------------------------------------+-----------+-----------+------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------+-----------+-----------+------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_7 | 10.00 | root | | anchor_game.anchor_bet_record.id, anchor_game.anchor_bet_record.flow_order_no, anchor_game.anchor_bet_record.winning_losing, anchor_game.anchor_bet_record.currency_type, anchor_game.anchor_bet_record.bet_money, anchor_game.anchor_bet_record.order_status, anchor_game.anchor_bet_record.created_at, anchor_game.anchor_bet_record.game_code, anchor_game.anchor_bet_record.game_name, anchor_game.anchor_bet_record.member_id, anchor_game.anchor_bet_record.member_account, anchor_game.anchor_bet_record.merchant_id, anchor_game.anchor_bet_record.merchant_code, anchor_game.anchor_bet_record.merchant_name, anchor_game.anchor_bet_record.bet_at | | └─TopN_9 | 10.00 | root | | anchor_game.anchor_bet_record.created_at, offset:0, count:10 | | └─IndexLookUp_20 | 10.00 | root | | | | ├─IndexRangeScan_13(Build) | 473110.00 | cop[tikv] | table:anchor_bet_record, index:idx_merchant_bet(merchant_code, bet_at) | range:["5273","5273"], keep order:false | | └─TopN_19(Probe) | 10.00 | cop[tikv] | | anchor_game.anchor_bet_record.created_at, offset:0, count:10 | | └─Selection_15 | 910.05 | cop[tikv] | | eq(anchor_game.anchor_bet_record.game_code, "bjl"), eq(anchor_game.anchor_bet_record.is_deleted, 0), eq(anchor_game.anchor_bet_record.member_account, "xk139880305"), gt(anchor_game.anchor_bet_record.created_at, 2025-09-28 09:32:32.953000) | | └─TableRowIDScan_14 | 473110.00 | cop[tikv] | table:anchor_bet_record | keep order:false | +------------------------------------+-----------+-----------+------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> 分析下 怎么样
10-01
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值