如何将这两个select语句连接到相关列Microsoft 2008上

Question

目标是将这些数据连接到一个按year_month分组的表中。

第一个查询：

select  tlyear_month,count(user_name) AS agents_lagents_value
 from
    (SELECT distinct 
            USER_NAME
            ,concat(cryear,'-',crmonth) as tlyear_month
        FROM STATUS_REPORT_TABLE_BASIC
        WHERE GROUP_NAME LIKE '%Agent%'
    GROUP BY CRYEAR, CRMONTH,user_name,group_name) sub1
    group by tlyear_month

第二个查询：

SELECT 
            concat(cryear,'-',crmonth) tsaff_YEAR_MONTH, 
        CASE -- hardcoded between 2017-01 and 2017-12
            WHEN CRYEAR = 2017  AND CRMONTH = 1 THEN 49
            WHEN CRYEAR = 2017  AND CRMONTH = 2 THEN 47
            WHEN CRYEAR = 2017  AND CRMONTH = 3 THEN 46
            WHEN CRYEAR = 2017  AND CRMONTH = 4 THEN 46
            WHEN CRYEAR = 2017  AND CRMONTH = 5 THEN 47
            WHEN CRYEAR = 2017  AND CRMONTH = 6 THEN 49
            WHEN CRYEAR = 2017  AND CRMONTH = 7 THEN 53
            WHEN CRYEAR = 2017  AND CRMONTH = 8 THEN 54
            WHEN CRYEAR = 2017  AND CRMONTH = 9 THEN 54
            WHEN CRYEAR = 2017 AND CRMONTH = 10 THEN 54
            WHEN CRYEAR = 2017 AND CRMONTH = 11 THEN 51
            WHEN CRYEAR = 2017 AND CRMONTH = 12 THEN 54
            ELSE COUNT(distinctified.USER_NAME)
        END  A_COUNT

    FROM
        (SELECT DISTINCT
            CRYEAR
            ,CRMONTH
            ,USER_NAME
            ,GROUP_NAME
        FROM STATUS_REPORT_TABLE_BASIC
        WHERE GROUP_NAME LIKE '%Agent%' )  AS distinctified
    GROUP BY CRYEAR, CRMONTH

第一个结果(不是所有行，这两个查询都包含来自同一个daterange的数据)

 2017-1 36
    2017-10 47
    2017-11 50
    2017-12 53
    2017-2  35
    2017-3  36
    2017-4  33
    2017-5  34
    2017-6  34
    2017-7  40

第二个结果(不是所有行，这两个查询都包含来自同一个daterange的数据)

2017-1  49
2018-1  55
2017-2  47
2018-2  52
2017-3  46
2018-3  51
2017-4  46
2018-4  49
2017-5  47
2018-5  49

我对sql有点陌生，我已经没有任何想法了。任何帮助都将不胜感激。

谢谢

Answer 1

在两个查询之间使用联接

     select a.tlyear_month, agents_lagents_value,cnt from 
(select  tlyear_month,count(user_name) AS agents_lagents_value
         from
            (SELECT distinct 
                    USER_NAME
                    ,concat(cryear,'-',crmonth) as tlyear_month
                FROM STATUS_REPORT_TABLE_BASIC
                WHERE GROUP_NAME LIKE '%Agent%'
            GROUP BY CRYEAR, CRMONTH,user_name,group_name) sub1
            group by tlyear_month)a
inner join
(

        SELECT 
                    concat(cryear,'-',crmonth) as t2year_month, 
                CASE -- hardcoded between 2017-01 and 2017-12
                    WHEN CRYEAR = 2017  AND CRMONTH = 1 THEN 49
                    WHEN CRYEAR = 2017  AND CRMONTH = 2 THEN 47
                    WHEN CRYEAR = 2017  AND CRMONTH = 3 THEN 46
                    WHEN CRYEAR = 2017  AND CRMONTH = 4 THEN 46
                    WHEN CRYEAR = 2017  AND CRMONTH = 5 THEN 47
                    WHEN CRYEAR = 2017  AND CRMONTH = 6 THEN 49
                    WHEN CRYEAR = 2017  AND CRMONTH = 7 THEN 53
                    WHEN CRYEAR = 2017  AND CRMONTH = 8 THEN 54
                    WHEN CRYEAR = 2017  AND CRMONTH = 9 THEN 54
                    WHEN CRYEAR = 2017 AND CRMONTH = 10 THEN 54
                    WHEN CRYEAR = 2017 AND CRMONTH = 11 THEN 51
                    WHEN CRYEAR = 2017 AND CRMONTH = 12 THEN 54
                    ELSE COUNT(distinctified.USER_NAME)
                END  as cnt

            FROM
                (SELECT DISTINCT
                    CRYEAR
                    ,CRMONTH
                    ,USER_NAME
                    ,GROUP_NAME
                FROM STATUS_REPORT_TABLE_BASIC
                WHERE GROUP_NAME LIKE '%Agent%' )  AS distinctified
            GROUP BY CRYEAR, CRMONTH)b on a.tlyear_month=b.concat(cryear,'-',crmonth)