mysql - 巨大的多重连接，需要将结果限制为144

故事是一个自由职业者程序员消失了，而未完成的工作留给了前者-包括一些MySQL查询。
我可以管理简单的东西，但是这超出了我的能力。
该查询返回商店中所有产品的排序方式

products_date_added

而应该只提取144个最新结果。
我尝试在其末尾添加限制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

我将不胜感激。

最佳答案

根据您的评论，这可能是您想要的。我只是将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

关于mysql - 巨大的多重连接，需要将结果限制为144，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23660370/