视图的使用

一丶例1

 1 CREATE VIEW v_billonline
 2 as 
 3 
 4 select rb.Area,rb.ProjectId,rb.ProjectName,
 5 rb.FloorNumber,rb.FormatsName,rb.TwoFormatsName,
 6 rb.BunkNo,rb.BrandAbbreviation,s.BusinessManager,
 7 s.StoreManager,s.StoreManagerPhone,
 8 rb.BillYear,rb.BillMonth,rb.ThisReceivable as RentReceivable,
 9 rb.BillStatus as RentBillStatus,rb.F_ID as RentBilld, 
10 pb.ThisReceivable as PropertReceivable,pb.BillStatus as PropertBillStatus,
11 pb.F_ID as  PropertBillId
12 from he_finance_rentbill rb                                  -- 1
13 LEFT JOIN he_finance_propertybill pb  
14 on rb.PlanId=pb.PlanId  and rb.BillYear = pb.BillYear and rb.BillMonth= pb.BillMonth        -- 2
15 LEFT JOIN he_merchants_store s on rb.PlanId=s.PlanId;

View Code

 二

 1 with he_operating_LicenceManageInfoS as(
 2 
 3 select 'DP1' dp ,'z1' as LicenseType,'2018-1-1' as LicenseValidityDate
 4 union all select 'DP1','z2','2018-2-1'
 5 union all select 'DP1','z3','2018-3-1'
 6 union all select 'DP1','z4','2018-4-1'
 7 union all select 'DP2','z1','2018-1-1' 
 8 union all select 'DP2','z4','2018-4-1'
 9 union all select 'DP3','z1','2018-1-1' 
10 union all select 'DP3','z2','2018-4-1'
11 union all select 'DP3','z3','2018-1-1' 
12 union all select 'DP3','z4','2018-4-1'
13 union all select 'DP3','z5','2018-1-1' 
14 union all select 'DP3','z5','2018-4-1'
15 )
16 
17 -- select distinct LicenseType from he_operating_LicenceManageInfoS;
18 
19 select StoreNo,
20 max(case when LicenseType='z1' then LicenseValidityDate else '' end)  as 'z1',
21 max(case when LicenseType='z2' then LicenseValidityDate else '' end)  as 'z2',
22 max(case when LicenseType='z3' then LicenseValidityDate else '' end)  as 'z3',
23 max(case when LicenseType='z4' then LicenseValidityDate else '' end)  as 'z4',
24 max(case when LicenseType='z5' then LicenseValidityDate else '' end)  as 'z5'
25 from he_operating_LicenceManageInfo 
26 group by StoreNo
27 
28 
29  
30 INSERT into he_operating_LicenceManageInfo (F_ID,StoreNo,LicenseType)
31 SELECT * from (
32 select newGuid() AS F_ID, t.StoreNo,t.LicenseType  from (
33   select StoreNo,LicenceName as LicenseType  from he_merchants_store
34     left join 
35     (select licencename from  he_operating_formatsandlicencerelational) s
36     on 1=1
37     where storeno !=''
38     order by storeno,LicenseType
39 )t 
40 left join he_operating_LicenceManageInfo as he on t.StoreNo = he.StoreNo
41 where he.StoreNo is NULL ) tt
42 
43 SELECT * FROM he_operating_LicenceManageInfo

 

转载于:https://www.cnblogs.com/chenze-Index/p/9959752.html