今天写了一个视图,用T-SQL形式创建,做个记录
if exists(select * from sysobjects where name='V_YEA_BUDGETBALANCE')
drop view V_YEA_BUDGETBALANCEgo
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_SEDedu