mysql - 如何联接表和2选择在一起

我有以下两个查询，如何将它们合并在一起？

查询1：

SELECT product_name, count(product_name) as count_product_name
FROM ps_order_detail
WHERE id_shop = 1
group by product_name
order by count_product_name DESC
LIMIT 5

查询2：

SELECT count(*) as count, concat(decade, '-', decade + 9) as year
FROM (SELECT floor(year(`birthday`) / 10) * 10 as decade
      FROM ps_customer) C
GROUP BY decade;

第一个查询是获取订购最多的前5个产品名称。

第二个查询是获取客户生日年份并将其每十年分组一次。

我想知道订购前5种产品的年龄段。

结果应该是

product name, years, count
productA 1990-2009  100
ProductA 2000-2019  20
ProductB 1980-1999  20
ProductB 1990-2009  25
ProductB 2000-2019  20

...

我确实有第三张桌子可以连接。

create table ps_orders(
  id_customer,
id_order
);

create table ps_customer(
id_customer, birthday
);
create table ps_order_detail(
id_order, product_name
);

我不确定如何将它们组合在一起，我可以一一输入产品名称来获得年份。

SELECT count(*) as count, concat(decade, '-', decade + 9) as year
FROM (SELECT floor(year(birthday) / 10) * 10 as decade
      FROM ps_customer as CU,
           ps_orders as O,
           ps_order_detail as OD
      WHERE CU.id_customer = O.id_customer
        AND O.id_order = OD.id_order
        AND OD.product_name = 'product Name A'
    ) C
GROUP BY decade;

最佳答案

使用第二个查询，但是不要指定特定的产品，而是将其放入分组中。然后将其与返回前5个产品的子查询进行限制。

SELECT product_name, concat(decade, '-', decade + 9) AS year, count(*) AS count
FROM (SELECT p.product_name, floor(year(birthday) / 10) * 10 as decade
      FROM (SELECT product_name, count(*) as count_product_name
            FROM ps_order_detail
            WHERE id_shop = 1
            group by product_name
            order by count_product_name DESC
            LIMIT 5) AS p
      JOIN ps_order_detail AS od ON od.product_name = p.product_name
      JOIN ps_orders AS o on o.id_order = od.id_order
      JOIN ps_customer AS c ON c.id_customer = o.id_customer
      WHERE od.id_shop = 1) AS x
GROUP BY product_name, decade