SQL Syntax advanced application four: Using views to implement multi-table federated data details

In the previous chapters we talked about: if the data for a table is a union of multiple tables, and there is a merge of columns and columns to form a new column, the view is the best scenario.

Below I share two real SQL statement cases

 Usewot_inventoryGOIF EXISTS(SELECT 1  fromSys.viewsWHEREName= 'Invoicesearchlistview')    DROP VIEWInvoicesearchlistview;GOCREATE VIEWInvoicesearchlistview as SELECTRow_number () Over(ORDER  byI.createdatetimeDESC) asId, I.invoiceid invoiceid,i.invoicecode,i.[Status], Case  whenLs.[Status]  is NULL  Then  About ELSELs.[Status] END  astracestate_auto,i.tracestate tracestate_own,i.saleschannelid,i.saleschannelname,i.warehouseid,i. Warehousename,sh. Shipperid,sh. Shippername,sh. Shipperno,sh. Monthlyaccount,i.logisticcode,i.receivable,i.agencyfund,sketch= (  STUFF(   (SELECT  Case  whenId. Number > 0  Then ', ' +Pro. ProductName+Pro. Spec+ '(' + CONVERT(NVARCHAR(Ten), ID. Number)+Pro. Unit+ ')' ELSE "' END        fromDbo. Products ProINNER JOINDbo. Invoicedetail ID onId. ProductId=Pro. ProductIdWHEREId. Invoiceid=I.invoiceid forXML PATH ("')    ),1,1,"') ), I.salesgroupid,i.salesgroupname,i.salesuserid,i.salesusername,ls. Syncdate,ls. Lasttracedesc,i.lastdescusername,i.lastdescdate,i.lastdesc,i.createdatetime,i.agreeddate,i.printcheckdate,i. Outwarehousedate,i.tracestatedate,i.contrastdate,i.completedate,i.orderid,i.orderno,i.printtype,i.printnumber, I.customerid,i.customername,i.customerphone,i.country,i.province,i.city,i.insurrevalue,i.fragileinsurrevalue,i . Freight,i.isreceived,i.iscomplete fromWot_Inventory.dbo.Invoice i Left JOINWot_Inventory.dbo.Logistics ls onLs. Invoiceid=I.invoiceid Left JOINWot_Sales.dbo.Shipper SH onI.shipperid=Sh. ShipperIDWHEREI.[ State] <> -1 GO

Merge with multiple columns

 Usewot_inventoryGOIF EXISTS(SELECT 1  fromSys.viewsWHEREName= 'Logisticsfollowview')    DROP VIEWLogisticsfollowview;GOCREATE VIEWLogisticsfollowview as SELECTRow_number () Over(ORDER  byI.outwarehousedateDESC) asId, I.invoiceid, I.isoutwarehouse, i.outwarehousedate, Sh. ShipperID, Sh. Shipperno, Sh. Shippername, I.logisticcode, ls. Lasttracedate, ls. Lasttracedesc, ls.[Status], i.tracestate, ls. Syncdate, I.lastfollowdate, I.lastfollowdesc, I.lastfollowusername, I.saleschannelname, Sh.M       Onthlyaccount, I.receiver, I.country, I.province, i.city, I.orderno, I.invoicecode, I.tracestatedate, I.tracestateuser, I.salesgroupid, I.salesuserid, I.salesusername, i.or Derid, I.customername, I.customerphone, I.printcheckdate fromWot_Inventory.dbo.Invoice i Left JOINWot_Inventory.dbo.Logistics ls onLs. Invoiceid=I.invoiceid Left JOINWot_Sales.dbo.Shipper SH onI.shipperid=Sh. ShipperIDWHEREI.[ State] <> -1  andI.logisticcode is  not NULL  andI.[Status] >= 4  andI.isaddresscheck= 1  andI.isprintcheck= 1  andI.tracestate not inch(6,3,4)

Add a condition to a view without a multiple-column merge

PS: Welcome to scan the QR code below or click on the link, join QQ group

SQL Syntax advanced application four: Using views to implement multi-table federated data details