如果不使用临时表,如何编写此sql查询?

f1tvaqid  于 2021-08-01  发布在  Java
关注(0)|答案(1)|浏览(296)

我很难在powerbi中运行这个查询,因为它使用了一个临时表。没有临时表我怎么写这个?

SELECT  Convert(varchar,TT1.POST_DATE,110) as 'POST_DATE',
        COUNT (DISTINCT TT1.TRAN_DATE) as 'NO_DAYS_ENTERED',
        HP4.EMPLOYEE_CODE,
        HP4.EMPLOYEE_NAME,
        HP4.EMPLOYEE_NAME + ' (' + LEFT(HB6.OFFC_DESC,3) + ')' as 'EMPLOYEE_NAME_2',
        TT1.OFFC,
        HB6.OFFC_DESC,
        HP4.GRAD_YEAR,
        TB1.RANK_CODE,
        TR1.RANK_DESC

into #tempG1

FROM    TAT_TIME TT1
        LEFT JOIN HBM_PERSNL HP4 ON TT1.TK_EMPL_UNO = HP4.EMPL_UNO 
        INNER JOIN TBM_PERSNL TB1 on HP4.EMPL_UNO = TB1.EMPL_UNO
        INNER JOIN TBL_RANK TR1 on TB1.RANK_CODE = TR1.RANK_CODE
        INNER JOIN HBL_OFFICE HB6 on HP4.OFFC = HB6.OFFC_CODE

WHERE   TB1.RANK_CODE IN ('4')
    AND HP4.INACTIVE = 'N'
    AND TT1.POST_DATE >= DATEADD(month, -3, GETDATE())

GROUP BY TT1.POST_DATE, HP4.EMPLOYEE_CODE, HP4.GRAD_YEAR, HP4.EMPLOYEE_NAME, TT1.OFFC, TB1.RANK_CODE, TR1.RANK_DESC, HB6.OFFC_DESC

select G1.EMPLOYEE_NAME,
        G1.EMPLOYEE_NAME_2,
       datediff(day, min(G1.POST_DATE), max(G1.POST_DATE)) * 1.0 / nullif(count(*) - 1, 0) as 'AVG_FREQ'
from #tempG1 G1
group by G1.EMPLOYEE_NAME, G1.EMPLOYEE_NAME_2

drop table #tempG1
lh80um4z

lh80um4z1#

您需要在主sql中将#tempg1查询作为内联视图引用。试试这个-

select G1.EMPLOYEE_NAME,
        G1.EMPLOYEE_NAME_2,
       datediff(day, min(G1.POST_DATE), max(G1.POST_DATE)) * 1.0 / nullif(count(*) - 1, 0) as 'AVG_FREQ'
from (SELECT  Convert(varchar,TT1.POST_DATE,110) as 'POST_DATE',
        COUNT (DISTINCT TT1.TRAN_DATE) as 'NO_DAYS_ENTERED',
        HP4.EMPLOYEE_CODE,
        HP4.EMPLOYEE_NAME,
        HP4.EMPLOYEE_NAME + ' (' + LEFT(HB6.OFFC_DESC,3) + ')' as 'EMPLOYEE_NAME_2',
        TT1.OFFC,
        HB6.OFFC_DESC,
        HP4.GRAD_YEAR,
        TB1.RANK_CODE,
        TR1.RANK_DESC

FROM    TAT_TIME TT1
        LEFT JOIN HBM_PERSNL HP4 ON TT1.TK_EMPL_UNO = HP4.EMPL_UNO 
        INNER JOIN TBM_PERSNL TB1 on HP4.EMPL_UNO = TB1.EMPL_UNO
        INNER JOIN TBL_RANK TR1 on TB1.RANK_CODE = TR1.RANK_CODE
        INNER JOIN HBL_OFFICE HB6 on HP4.OFFC = HB6.OFFC_CODE

WHERE   TB1.RANK_CODE IN ('4')
    AND HP4.INACTIVE = 'N'
    AND TT1.POST_DATE >= DATEADD(month, -3, GETDATE())

GROUP BY TT1.POST_DATE, HP4.EMPLOYEE_CODE, HP4.GRAD_YEAR, HP4.EMPLOYEE_NAME, TT1.OFFC, TB1.RANK_CODE, TR1.RANK_DESC, HB6.OFFC_DESC
) G1
group by G1.EMPLOYEE_NAME, G1.EMPLOYEE_NAME_2

相关问题