sql - SQL如何从上个月的group by中获取一个值以在当前group by中使用？

Question

您如何在同一行获得今年的潜在客户数量和去年的潜在客户数量。我需要的查询更复杂，但这是第一步。

输出

Year,Count,Last Year Count
2016,55,88
2017,77,55
2018,83,77

SELECT L1.Year, count(L1.LeadId), count(Lead_Last_Year.Lead_count) from Leads
???
Group by Year

谢谢你

标签： sql

Answer 1

您可以尝试左加入分组子查询。

SELECT x.year,
       x.count,
       y.count last_year_count
       FROM (SELECT l1.year,
                    count(l1.leadid) count
                    FROM leads l1
                    GROUP BY l1.year) x
            LEFT JOIN (SELECT l1.year,
                              count(l1.leadid) count
                              FROM leads l1
                              GROUP BY l1.year) y
                      ON y.year = x.year - 1;

如果您的 DBMS 支持 CTE，您还可以使用 CTE 而不是重复子查询。

WITH cte
AS
(
SELECT l1.year,
       count(l1.leadid) count
       FROM leads l1
       GROUP BY l1.year
)
SELECT x.year,
       x.count,
       y.count last_year_count
       FROM cte x
            LEFT JOIN cte y
                      ON y.year = x.year - 1;