来自不同日期范围内的多个选择

Question

因此，我有以下查询

SELECT pn.no,
       pn.nome,
       SUM(qtt)
FROM pn,
     st
WHERE fdata > '2021-01-01'
  AND fdata < '2021-03-01'
  AND pn.ref = st.ref
  AND st.forref LIKE '%product%'
GROUP BY pn.no,
         pn.nome;

，使我返回01/01/2021和01/03/2021之间的结果。

我需要做的就是将其与今年同一时期进行比较。

如何一口气获得两个时期的结果？

Answer 1

您可以将2022年的外部加入到2021年的子查询上，

以便您获得所有可能的产品

SELECT 
    COALESCE(p1.no,p2.no),
    COALESCE (p1.nome,p2.nome),
    CASE
        WHEN COALESCE (sum_qt1,0) > COALESCE (sum_qt2,0) THEN 'more'
        WHEN COALESCE (sum_qt1,0) < COALESCE (sum_qt2,0) THEN 'less'
        ELSE 'equal'
    END
FROM
    (SELECT 
        pn.no, pn.nome, SUM(qtt) sum_qt2
    FROM
        pn JOIN  st ON pn.ref = st.ref
    WHERE
        fdata > '2022-01-01'
            AND fdata < '2022-03-01'            
            AND st.forref LIKE '%product%'
    GROUP BY pn.no , pn.nome) p1
        FULL OUTER  JOIN
    (SELECT 
        pn.no, pn.nome, SUM(qtt) sum_qt1
    FROM
        pn JOIN st ON pn.ref = st.ref
    WHERE
        fdata > '2021-01-01'
            AND fdata < '2021-03-01'
            AND st.forref LIKE '%product%'
    GROUP BY pn.no , pn.nome) p2 ON p1.no = p2.no