mysql - 可怕的多重连接需要将结果限制为 144

Question

这个故事是一个自由职业程序员失踪了，给前端留下了一份未完成的工作 - 包括一些 MySQL 查询。我可以处理简单的事情，但这件事超出了我的能力范围。查询返回商店中按

排序的所有产品

products_date_added

虽然它应该只提取 144 个最新结果。我尝试在其末尾添加 limit 144，但显然它不起作用。

查询如下:

select  p.products_id,   
        p.products_status,
        p.products_quantity,
        if(length(pd1.products_name),pd1.products_name, pd.products_name) as products_name,
        if(p.products_image_lrg is null, p.products_image, p.products_image_lrg) as products_image,
        p.products_model,
        p.products_price,
        pd.products_description_short,
        p.products_tax_class_id,
        p.products_date_added,
        p.products_master,
        p.products_master_status,
        p.products_listing_status,
        m.manufacturers_name 
from products_description pd,
     products p
left join products_description pd1 on pd1.products_id = p.products_id
    and pd1.language_id='1'
    and pd1.affiliate_id = '0'
left join products_prices pp on p.products_id = pp.products_id
    and pp.groups_id = '0'
    and pp.currencies_id = '0'
left join manufacturers m on (p.manufacturers_id = m.manufacturers_id)
where p.products_status > 0
    and (p.products_master='0' or p.products_master is null or p.products_master='')
    and p.products_master_status!='1'
    and pd.affiliate_id = 0
    and p.products_id = pd.products_id
    and if(pp.products_group_price is null,
    pp.products_group_price != -1 )
    and pd.affiliate_id = 0
    and pd.language_id = '1'
order by p.products_date_added DESC, products_name

如果有任何帮助，我将不胜感激。

Answer 1

根据您的评论，这可能就是您想要的。我只是将 products 和 products_description 之间的连接移动到派生表(子查询)中，并添加 LIMIT:

select  p.products_id,   
        p.products_status,
        p.products_quantity,
        if(length(pd1.products_name),pd1.products_name, p.products_name) as products_name,
        if(p.products_image_lrg is null, p.products_image, p.products_image_lrg) as products_image,
        p.products_model,
        p.products_price,
        p.products_description_short,
        p.products_tax_class_id,
        p.products_date_added,
        p.products_master,
        p.products_master_status,
        p.products_listing_status,
        m.manufacturers_name 
from 
    (
      select * -- you might have to list the actual columns to avoid a "duplicate name" error
      from products_description pd
      join products p on p.products_id = pd.products_id
      where p.products_status > 0
        and (p.products_master='0' or p.products_master is null
              or p.products_master='')
        and p.products_master_status!='1'
        and pd.affiliate_id = 0     
        and if(pp.products_group_price is null,
                pp.products_group_price != -1 )
        and pd.affiliate_id = 0
        and pd.language_id = '1'
      order by p.products_date_added DESC, products_name
      LIMIT 144
    ) p
left join products_description pd1 on pd1.products_id = p.products_id
    and pd1.language_id='1'
    and pd1.affiliate_id = '0'
left join products_prices pp on p.products_id = pp.products_id
    and pp.groups_id = '0'
    and pp.currencies_id = '0'
left join manufacturers m on (p.manufacturers_id = m.manufacturers_id)
order by p.products_date_added DESC, products_name