在多个查询和子查询中更正连接语法

I have two queries that end up having the same format. Each has a Month, a year, and some relevant data per month/year. The schema looks like this:

我有两个查询最终具有相同的格式。每个月/每年都有一个月,一年和一些相关数据。架构如下所示:

subs    Month   Year
8150    1      2015
11060   1      2016
5       2      2014
6962    2      2015
8736    2      2016

Cans    months  years
2984    1       2015
2724    1       2016
13      2       2014
2563    2       2015
1901    2       2016

The first query syntax looks like this:

第一个查询语法如下所示:

SELECT 
    COUNT(personID) AS subs_per_month,
    MONTH(Date_1) AS month_1,
    YEAR(Date_1) AS year_1
FROM
    (SELECT 
        personID, MIN(date) AS Date_1
    FROM
        orders
    WHERE
        isSubscription = 1
    GROUP BY personID
    ORDER BY Date_1) AS my_sub_q
GROUP BY month_1 , year_1

The second query:

第二个查询:

SELECT 
    COUNT(ID), MONTH(date) AS months, YEAR(date) AS years
FROM
    orders
WHERE
    status = 4 AND isSubscription = 1
GROUP BY months , years
ORDER BY months, years

The end goal is to write a simple join so that the final dataset looks like this:

最终目标是编写一个简单的连接,以便最终的数据集如下所示:

subs    cans months years
8150    2984    1   2015
11060   2724    1   2016
5         13    2   2014
6962    2563    2   2015
8736    1901    2   2016

I'm a little overwhelmed with how to do this correctly, and after a lot of trial and all error, I thought I'd ask for help. What's confusing is where the JOIN goes, and how that looks relative to the rest of the syntax.

我对如何正确地做到这一点感到有些不知所措,经过大量试验和所有错误后,我想我会寻求帮助。令人困惑的是JOIN的位置,以及相对于其余语法的外观。

3 个解决方案

#1

Without giving consideration to simplifying your queries you can use your two queries as inline views and simply select from both (I aliased Q1 and Q2 for your queries and named fields the same within each for simplicity.

在不考虑简化查询的情况下,您可以将两个查询用作内联视图,并且只需从两个选项中进行选择(为了简单起见,我将查询和命名字段别名为Q1和Q2。

Select Q1.cnt as Subs, Q2.cnt as Cans, Q1.months, Q1.years
from (SELECT 
    COUNT(personID) AS Cnt,
    MONTH(Date_1) as Months,
    YEAR(Date_1) AS years
    FROM  (SELECT personID, MIN(date) AS Date_1
           FROM orders
           WHERE isSubscription = 1
           GROUP BY personID) AS my_sub_q
    GROUP BY month_1 , year_1) Q1
INNER JOIN (SELECT COUNT(ID) cnt, MONTH(date) AS months, YEAR(date) AS years
            FROM orders
            WHERE status = 4 
              AND isSubscription = 1
            GROUP BY months, years) Q2

  ON Q1.Months = Q2.Months
 and Q1.Years = Q2.years
Order by Q1.years, Q2.months

3 个解决方案

#1

更多相关文章

随机推荐