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