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