如果不使用临时表，如何编写此sql查询？

f1tvaqid 于 2021-08-01 发布在 Java

关注(0)|答案(1)|浏览(404)

我很难在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

sql sql-server-2014

来源：https://stackoverflow.com/questions/62662832/how-would-you-write-this-sql-query-without-the-use-of-a-temp-table

1条答案

按热度按时间

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

展开查看全部

赞(0）回复(0）举报 2021-08-01

我来回答

如果不使用临时表，如何编写此sql查询？

1条答案

相关问题

热门标签

最新问答