如何在单选查询中进行多个选择查询

Question

我必须编写一个查询，我需要获取上周，上个月和所有人的记录。对于这个问题，我写了 3 个不同的查询（上周、上个月和所有查询）

每周信息：-

SELECT bu.brand_name AS 'Brand_Name',COUNT(s.unique) AS '# Item Sold',SUM(s.price) AS 'Total_Price'
FROM item_details s
LEFT JOIN sales_order o ON s.fk_sales_order = o.id_sales_order 
LEFT JOIN customer_info AS c ON o.fk_customer_id = c.id_customer
LEFT JOIN simple_details cc ON s.unique = cc.unique
LEFT JOIN config_details  cf ON cc.fk_config_id = cf.config_id 
LEFT JOIN brand_details  cb ON cf.fk_brand_id = cb.brand_id 
LEFT JOIN category_details  ctc ON cf.fk_category_id = ctc.category_id 
LEFT JOIN gender_details  g ON cf.fk_gender_id = g.gender_id
LEFT JOIN buyers AS bu  ON bu.brand_name =  cb.name AND bu.category_name = ctc.name AND bu.gender = g.name
WHERE  bu.buyers = 'xyz'  AND DATE_FORMAT(o.created_date,'%Y-%m-%d') >= @weekstartdate AND  DATE_FORMAT(o.created_date,'%Y-%m-%d') <= @weekenddate
GROUP BY bu.brand_name

每月信息：-

SELECT bu.brand_name AS 'Brand_Name',COUNT(s.unique) AS '# Item Sold',SUM(s.price) AS 'Total_Price'
FROM item_details s
LEFT JOIN sales_order o ON s.fk_sales_order = o.id_sales_order 
LEFT JOIN customer_info AS c ON o.fk_customer_id = c.id_customer
LEFT JOIN simple_details cc ON s.unique = cc.unique
LEFT JOIN config_details  cf ON cc.fk_config_id = cf.config_id 
LEFT JOIN brand_details  cb ON cf.fk_brand_id = cb.brand_id 
LEFT JOIN category_details  ctc ON cf.fk_category_id = ctc.category_id 
LEFT JOIN gender_details  g ON cf.fk_gender_id = g.gender_id
LEFT JOIN buyers AS bu  ON bu.brand_name =  cb.name AND bu.category_name = ctc.name AND bu.gender = g.name
WHERE  bu.buyers = 'xyz'  AND DATE_FORMAT(o.created_date,'%Y-%m-%d') >= @monthstartdate AND  DATE_FORMAT(o.created_date,'%Y-%m-%d') <= @monthenddate
GROUP BY bu.brand_name

对于所有记录：-

SELECT bu.brand_name AS 'Brand_Name',COUNT(s.unique) AS '# Item Sold',SUM(s.price) AS 'Total_Price'
FROM item_details s
LEFT JOIN sales_order o ON s.fk_sales_order = o.id_sales_order 
LEFT JOIN customer_info AS c ON o.fk_customer_id = c.id_customer
LEFT JOIN simple_details cc ON s.unique = cc.unique
LEFT JOIN config_details  cf ON cc.fk_config_id = cf.config_id 
LEFT JOIN brand_details  cb ON cf.fk_brand_id = cb.brand_id 
LEFT JOIN category_details  ctc ON cf.fk_category_id = ctc.category_id 
LEFT JOIN gender_details  g ON cf.fk_gender_id = g.gender_id
LEFT JOIN buyers AS bu  ON bu.brand_name =  cb.name AND bu.category_name = ctc.name AND bu.gender = g.name
WHERE  bu.buyers = 'xyz'  
GROUP BY bu.brand_name

这些工作正常（提供当前输出）。但问题是，我必须将这三个查询合并为一个查询。其中输出应为品牌名称， item_sold（周）， total_price（周）， item_sold（月）， total_price（月）， item_sold（全部）， total_price（全部）如何编写此查询？

Answer 1

如果不深入研究您的代码，显而易见的解决方案是

SELECT
    all.brand_name
    pw.items_sold items_sold_week
    pw.total_price total_price_week
    pm.items_sold items_sold_month
    pm.total_price total_price_month
    all.items_sold items_sold_all
    all.total_price total_price_all
FROM
    (your all-time select) all
    JOIN (your per-month select) pm ON all.brand_name = pm.brand_name
    JOIN (your per-week select) pw ON all.brand_name = pw.brand_name

尽管您可能应该重新考虑整个方法，并确保是否真的希望在数据库层中使用这种逻辑，或者最好在应用程序中使用这种逻辑。

Answer 2

您可以使用

case将聚合限制为行的子集：

select  bu.brand_name
,       count(case when date_format(o.created_date,'%Y-%m-%d') >= @weekstartdate
             and date_format(o.created_date,'%Y-%m-%d') <= @weekenddate 
             then 1 end) as '# Item Sold Week'
,       sum(case when date_format(o.created_date,'%Y-%m-%d') >= @weekstartdate
             and date_format(o.created_date,'%Y-%m-%d') <= @weekenddate 
             then s.price end) as 'Total_Price Week'
,       count(case when date_format(o.created_date,'%Y-%m-%d') >= @monthstartdate
             and date_format(o.created_date,'%Y-%m-%d') <= @monthstartdate
             then 1 end) as '# Item Sold Month'
,       ...

Answer 3

如果所有三个选择都使用结果中的相同字段，则可以将它们联合起来：

SELECT * 
   FROM  (SELECT 1) AS a
   UNION (SELECT 2) AS b
   UNION (SELECT 3) AS c

如果您需要告诉彼此的周/月/所有记录 - 只需添加包含"周"或"周一"的常量字段

Answer 4

您可以在查询之间使用 UNION.关键字将它们捆绑在一起。但是列类型和顺序在所有查询中必须相同。您可以为每个集合添加一个标识符