amazCart CMS出现SQL错误1066：表/别名'products'不唯一求助

问题根源 #

你的SQL报错核心原因是重复关联products表却没给每个关联实例指定唯一别名，数据库无法区分不同的products关联，导致别名冲突。看原SQL里连续写了两次inner join products，后续关联category_product时又引用products.id，数据库根本不知道你指的是哪一个products实例。

另外原SQL还有两个次要问题：

• 存在冗余关联：第一个products关联已经加了status=1的条件，后面的exists子查询又重复判断，完全可以合并到关联条件里
• LIKE语法错误：LIKE %xxx%需要把匹配内容用单引号包裹，写成LIKE '%xxx%'，否则会触发语法错误

修复后的SQL #

select distinct `seller_products`.* 
from `seller_products` 
inner join `products` as `p` on `p`.`id` = `seller_products`.`product_id` and `p`.`status` = 1
inner join `category_product` on `category_product`.`product_id` = `p`.`id`
inner join `categories` on `categories`.`id` = `category_product`.`category_id` and categories.id in ('4868')
where (
    (`seller_products`.`user_id` = 1 and `seller_products`.`status` = 1)
    or exists (
        select * from `users` 
        where `seller_products`.`user_id` = `users`.`id` 
        and (
            (exists (
                select * from `seller_accounts` 
                where `users`.`id` = `seller_accounts`.`user_id` 
                and (`holiday_mode` = 0 or `holiday_date` != '2022-11-10' or (`holiday_date_start` > '2022-11-10' and `holiday_date_end` < '2022-11-10' or `holiday_date_start` > '2022-11-10' or `holiday_date_end` < '2022-11-10'))
            ) and exists (
                select * from `seller_subcriptions` 
                where `users`.`id` = `seller_subcriptions`.`seller_id` 
                and `expiry_date` > '2022-11-10' 
                and exists (
                    select * from `users` 
                    where `seller_subcriptions`.`seller_id` = `users`.`id` 
                    and exists (
                        select * from `seller_accounts` 
                        where `users`.`id` = `seller_accounts`.`user_id` 
                        and `seller_commission_id` = 3
                    )
                )
            ) and `is_active` = 1)
            or (exists (
                select * from `seller_accounts` 
                where `users`.`id` = `seller_accounts`.`user_id` 
                and (`seller_commission_id` != 3 and `holiday_mode` = 0 or `holiday_date` != '2022-11-10' or (`holiday_date_start` > '2022-11-10' and `holiday_date_end` < '2022-11-10' or `holiday_date_start` > '2022-11-10' or `holiday_date_end` < '2022-11-10'))
            ) and `is_active` = 1)
        )
    )
    and `seller_products`.`status` = 1
)
and (
    exists (
        select * from `products` 
        where `seller_products`.`product_id` = `products`.`id` 
        and (`products`.`id` in (6147) 
            or `products`.`product_name` LIKE '%walizka-american-tourister-linex-66-cm-deep-navy%' 
            or `products`.`description` LIKE '%walizka-american-tourister-linex-66-cm-deep-navy%' 
            or `products`.`specification` LIKE '%walizka-american-tourister-linex-66-cm-deep-navy%')
    ) 
    or `seller_products`.`product_name` LIKE '%walizka-american-tourister-linex-66-cm-deep-navy%'
)

关键修复点 #

1. 给唯一的products关联加上别名p，所有后续引用都用p代替products，避免别名冲突
2. 移除了重复的inner join products语句，合并了重复的条件判断
3. 给所有日期值和LIKE匹配内容加上了单引号，修正语法错误
4. 调整了括号层级，让逻辑更清晰，避免逻辑判断歧义

内容的提问来源于stack exchange，提问作者Alexandru Muhai