如何合并3个查询？

Mohit Shrivastava 发表于 Dev

莫希特·什里瓦斯塔瓦（Mohit Shrivastava）

我有2张桌子，我正在尝试提取特定于材料的记录。所以我的查询如下

单日查询

SELECT tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin, COUNT( product_serial_no ) AS  `S_FTD` 
FROM register_product_claim
INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
WHERE retailer_code =  '9017'
AND register_product_claim.dt =  '2016-08-04'
GROUP BY tm.asin
ORDER BY register_product_claim.dt DESC 
LIMIT 0 , 30

明智的月

SELECT tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin, COUNT( product_serial_no ) AS  `S_MTD` 
FROM register_product_claim
INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
WHERE retailer_code =  '9017'
AND MONTH( register_product_claim.dt ) =  '08'
GROUP BY tm.asin
ORDER BY register_product_claim.dt DESC 
LIMIT 0 , 30

Qtr Wise

SELECT tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin, COUNT( product_serial_no ) AS  `S_QTD` 
FROM register_product_claim
INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
WHERE retailer_code =  '9017'
AND (
MONTH( register_product_claim.dt ) =  '08'
OR MONTH( register_product_claim.dt ) =  '07'
OR MONTH( register_product_claim.dt ) =  '06'
)
GROUP BY tm.asin
ORDER BY register_product_claim.dt DESC 
LIMIT 0 , 30

这一切都很好。

现在，我正在尝试合并所有查询。但是我无法合并它们。联盟，加入，但没有运气。

最后一个尝试是

SELECT *
FROM 
    (SELECT 
        tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin,
        COUNT(product_serial_no) as S_FTD
    FROM
        register_product_claim
    INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
    WHERE
        retailer_code = '9017'
            AND register_product_claim.dt = '2016-08-04'
    GROUP BY tm.asin) as s1  
   Inner Join
    (SELECT 

        COUNT(product_serial_no) as S_MTD
    FROM
        register_product_claim
    INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
    WHERE
        retailer_code = '9017'
            AND MONTH(register_product_claim.dt) = '08'
    GROUP BY tm.asin) as s2
   Inner Join
    (SELECT 

        COUNT(product_serial_no) as S_QTD
    FROM
        register_product_claim
    INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
    WHERE
        retailer_code = '9017'
            AND (MONTH(register_product_claim.dt) = '08'
            OR MONTH(register_product_claim.dt) = '07'
            OR MONTH(register_product_claim.dt) = '06')
    GROUP BY tm.asin) as s3

我的预期输出是

model_number | item_description |  mrp | asin | S_FTD | S_MTD | S_QTD

S_FTD是一天，S_MTD是每月，S_QTD是每季度结果。

萨加斯

您可以使用条件聚合来做到这一点，将其调整为您的代码：

SELECT t.model_number,t.item_description,t.mrp,t.asin,
       MAX(CASE WHEN t.new_col = 'D' then t.S_TD END) as S_FTD,
       MAX(CASE WHEN t.new_col = 'M' then t.S_TD END) as S_MTD,
       MAX(CASE WHEN t.new_col = 'Q' then t.S_TD END) as S_QTD
FROM (
     SELECT ss.*,'D' as new_col FROM (Daily Query) ss
     UNION ALL
     SELECT ss1.*,'M' FROM (Monthly Query) ss1
     UNION ALL
     SELECT ss2.*,'Q' FROM (Quarter Query) ss2
     ) t
GROUP BY t.model_number,t.item_description,t.mrp,t.asin

注意：您必须S_FTD在日常查询中S_TD为该列添加别名，才能正常工作。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。