sql server with as只能查询一次

WITH
     Emp AS
        (SELECT acc.*
        FROM GXSpreadDB.dbo.gxs_account acc
        LEFT JOIN RYAccountsDB.dbo.AccountsInfo Account
            ON acc.userid=Account.userid 
        WHERE parentuserid=7123 
               AND Account.AgentID=0
        UNION ALL
        SELECT d.* FROM Emp ,GXSpreadDB.dbo.gxs_account d 
        WHERE d.parentuserid =Emp.userid ),
    DaiLi_RelationalTree AS
    (
        select * from GXSpreadDB.dbo.gxs_account where gameid=208356
    )
    --select * from DaiLi_RelationalTree
    select * from Emp,DaiLi_RelationalTree

以上代码是没问题的。

 

这个代码就会报对象不存在。可能要插入到临时表才能重复查询吧

  WITH
     Emp AS
        (SELECT acc.*
        FROM GXSpreadDB.dbo.gxs_account acc
        LEFT JOIN RYAccountsDB.dbo.AccountsInfo Account
            ON acc.userid=Account.userid 
        WHERE parentuserid=7123 
               AND Account.AgentID=0
        UNION ALL
        SELECT d.* FROM Emp ,GXSpreadDB.dbo.gxs_account d 
        WHERE d.parentuserid =Emp.userid ),
    DaiLi_RelationalTree AS
    (
        select * from GXSpreadDB.dbo.gxs_account where gameid=208356
    )
    select * from DaiLi_RelationalTree
    select * from Emp,DaiLi_RelationalTree