SQL 视图

视图相当于一张集合表
注意：视图不可以传入参数，但是可以在where 后面写条件

 

 1 if exists(select * from sys.objects where name='View_RibaoOA_ItemMasterCost')
 2     drop view View_RibaoOA_ItemMasterCost
 3 go
 4 create view View_RibaoOA_ItemMasterCost as
 5 select item.Code,
 6 item.name ,
 7 account.Year as accountyear,
 8 account.Month as accountmonth,
 9 cost.ElementTypeCost_LaborCurrentCost,
10 cost.ElementTypeCost_LaborPriorCost,
11 cost.ElementTypeCost_MachineCurrentCost,
12 cost.ElementTypeCost_MachinePriorCost,
13 cost.ElementTypeCost_MaterialCurrentCost,
14 cost.ElementTypeCost_MaterialPriorCost,
15 cost.ElementTypeCost_OverheadCurrentCost,
16 cost.ElementTypeCost_OverheadPriorCost,
17 cost.ElementTypeCost_SubcontractCurrentCost,
18 cost.ElementTypeCost_SubcontractPriorCost,
19 cost.ElementTypeCost_LaborCurrentCost
20 + cost.ElementTypeCost_LaborPriorCost
21 + cost.ElementTypeCost_MachineCurrentCost
22 + cost.ElementTypeCost_MachinePriorCost
23 + cost.ElementTypeCost_MaterialCurrentCost
24 + cost.ElementTypeCost_MaterialPriorCost
25 + cost.ElementTypeCost_OverheadCurrentCost
26 + cost.ElementTypeCost_OverheadPriorCost
27 + cost.ElementTypeCost_SubcontractCurrentCost
28 + cost.ElementTypeCost_SubcontractPriorCost as  totalcost
29 from IC_ItemCost as cost
30 left join CBO_ItemMaster as item on item.id = cost.ItemMaster 
31 left join CBO_CostType_Trl as costtype on costtype.id = cost.CostType
32 left join Base_SOBAccountingPeriod as sob on sob.id = cost.SOBPeriod
33      left join Base_AccountingPeriod as account on account.id = sob.AccountPeriod
34 --调用
35 --select * from View_RibaoOA_ItemMasterCost
36 --where  
37 --code in ('2030808289')
38 --and 
39 --accountYear in ('2019')
40 --and 
41 --accountMonth in ('3')
Demo

 

posted @ 2019-09-06 15:40  Alex_Mercer  阅读(88)  评论(0)    收藏  举报