T-SQL sqlServer 创建视图

来源：互联网发布：mac版枪火兄弟连2 编辑：程序博客网时间：2024/06/08 06:39

今天写了一个视图，用T-SQL形式创建，做个记录

if exists(select * from sysobjects where name='V_YEA_BUDGETBALANCE')

drop view V_YEA_BUDGETBALANCE
go
create view V_YEA_BUDGETBALANCE
as
SELECT v.*,CONVERT(float,t6.SSFee-v.F_YEA_FEESUM)AS F_YEA_APPLYFEE FROM
( SELECT   t1.F_YEA_CUSTID, t2.F_YEA_YEAR, CONVERT(float, SUM(t1.F_YEA_BUDGETAMOUNT)) AS F_YEA_BUDGETAMOUNT,
                CONVERT(float, ISNULL(t4.F_YEA_FEESUM, 0)) AS F_YEA_FEESUM, CONVERT(float, SUM(t1.F_YEA_BUDGETAMOUNT)
                - ISNULL(t4.F_YEA_FEESUM, 0)) AS F_YEA_BUDBALANCE
FROM      dbo.YEA_T_ER_CustBudget AS t2 INNER JOIN
                dbo.YEA_T_ER_CustBudgetEntry AS t1 ON t1.FID = t2.FID LEFT OUTER JOIN
                    (SELECT   F_YEA_CUSTID, SUM(F_YEA_FEESUM) AS F_YEA_FEESUM, YEAR(F_YEA_DATE) AS F_YEA_DATE
                     FROM      dbo.YEA_T_ER_SEDeduction AS t3
                     WHERE   (FDOCUMENTSTATUS = 'C')
                     GROUP BY F_YEA_CUSTID, YEAR(F_YEA_DATE)) AS t4 ON t4.F_YEA_CUSTID = t1.F_YEA_CUSTID
GROUP BY t1.F_YEA_CUSTID, t2.F_YEA_YEAR, t4.F_YEA_FEESUM
)AS v LEFT JOIN
   (select t5.F_YEA_CUSTID,t5.F_YEA_BUDYEAR,SUM(t5.F_YEA_FEESUM)AS SSFee
   from YEA_T_ER_SSApplication AS t5 where t5.FDOCUMENTSTATUS in('A','B','C','D')
   group by t5.F_YEA_CUSTID,t5.F_YEA_BUDYEAR)AS t6 ON v.F_YEA_CUSTID=t6.F_YEA_CUSTID

1 0