日期:2014-05-18  浏览次数:20584 次

来个高手帮我解释下下面的视图 详细点 只剩10分了 谢谢了
CREATE view vw_ExpenseClaimAutoMilesDetail
AS
SELECT [FID],row_number() over (order by FExpenseClaimID) as lblID_AM,
  [FBeginOdometer],'' as FDate,[FEndOdometer],
  (FEndOdometer-FBeginOdometer) as TotalMiles,[FBusinessMilesDriven],
  (FEndOdometer-FBeginOdometer-FBusinessMilesDriven) as PersonalMilesDriven,
  (select top 1 FUserID from dbo.TB_RC_ExpenseClaim where FExpenseClaimID=a.FExpenseClaimID and FInactivateDate is null) 
  as FUserID
  ,'' as FUserName
  ,cast
  (
  (
case when (FEndOdometer-FBeginOdometer)=0 then 0 else Convert(decimal(18,2),(FEndOdometer-FBeginOdometer-FBusinessMilesDriven)*100/(FEndOdometer-FBeginOdometer)) end) AS varchar(500))+'%'
  as FPersonalPercent
  ,[FRemarks]
  ,[FExpenseClaimID]
  ,[FinactivateDate]
  FROM [dbo].[ExpenseClaimAutoMilesDetail] a where FInactivateDate is null
GO

------解决方案--------------------
SQL code

CREATE view vw_ExpenseClaimAutoMilesDetail
AS
SELECT [FID],
  row_number() over (order by FExpenseClaimID) as lblID_AM,--按FExpenseClaimID升序的排列序号,别名为lblID_AM
  [FBeginOdometer],
'' as FDate,--FDate列,值为''
[FEndOdometer],
  (FEndOdometer-FBeginOdometer) as TotalMiles,--FEndOdometer与FBeginOdometer的差值,列名TotalMiles
[FBusinessMilesDriven],
  (FEndOdometer-FBeginOdometer-FBusinessMilesDriven) as PersonalMilesDriven,--三个的差值,列名PersonalMilesDriven
  (select top 1 FUserID from dbo.TB_RC_ExpenseClaim where FExpenseClaimID=a.FExpenseClaimID and FInactivateDate is null)  
  as FUserID--子查询,从TB_RC_ExpenseClaim取第一条与当前记录的FExpenseClaimID相同的,且FInactivateDate为空的FUserID,并给出列名FUserID
  ,'' as FUserName--FUserName列,值''
  ,cast
  (
  (
case when (FEndOdometer-FBeginOdometer)=0 then 0 else Convert(decimal(18,2),(FEndOdometer-FBeginOdometer-FBusinessMilesDriven)*100/(FEndOdometer-FBeginOdometer)) end) AS varchar(500))+'%'
  as FPersonalPercent--当FEndOdometer-FBeginOdometer相同时,取0,不同时,取FEndOdometer-FBeginOdometer-FBusinessMilesDriven占FEndOdometer-FBeginOdometer的百分比,列名FPersonalPercent
  ,[FRemarks]
  ,[FExpenseClaimID]
  ,[FinactivateDate]
  FROM [dbo].[ExpenseClaimAutoMilesDetail] a where FInactivateDate is null
GO