之前章节我们讲到:如果某个表的数据是多个表的联合,并且存在列与列的合并组成新列,用视图是最好的方案。
下面我分享两个个真实的SQL语句案例
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
USE Wot_Inventory GO IF EXISTS (SELECT 1 FROM sys.views WHERE Name = 'InvoiceSearchListView') DROP VIEW InvoiceSearchListView; GO CREATE VIEW InvoiceSearchListView AS SELECT ROW_NUMBER()OVER(ORDER BY i.CreateDatetime DESC) AS Id, i.InvoiceId InvoiceId,i.InvoiceCode,i.[Status], CASE WHEN ls.[Status] IS NULL THEN 99 ELSE ls.[Status] END AS TraceState_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 WHEN id.Number > 0 THEN ', ' + pro.ProductName + pro.Spec + '(' + CONVERT(NVARCHAR(10),id.Number) + pro.Unit + ')' ELSE '' END FROM dbo.Products pro INNER JOIN dbo.InvoiceDetail id ON id.ProductId = pro.ProductId WHERE id.InvoiceId = i.InvoiceId FOR XML 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 FROM Wot_Inventory.dbo.Invoice i LEFT JOIN Wot_Inventory.dbo.Logistics ls ON ls.InvoiceId = i.InvoiceId LEFT JOIN Wot_Sales.dbo.Shipper sh ON i.ShipperId = sh.ShipperId WHERE i.[State] <> -1 GO
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
USE Wot_Inventory GO IF EXISTS (SELECT 1 FROM sys.views WHERE Name = 'LogisticsFollowView') DROP VIEW LogisticsFollowView; GO CREATE VIEW LogisticsFollowView AS SELECT ROW_NUMBER()OVER(ORDER BY i.OutWarehouseDate DESC) AS Id, 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.MonthlyAccount, i.Receiver, i.Country, i.Province, i.City, i.OrderNo, i.InvoiceCode, i.TraceStateDate, i.TraceStateUser, i.SalesGroupId, i.SalesUserId, i.SalesUserName, i.OrderId, i.CustomerName, i.CustomerPhone, i.PrintCheckDate FROM Wot_Inventory.dbo.Invoice i LEFT JOIN Wot_Inventory.dbo.Logistics ls ON ls.InvoiceId = i.InvoiceId LEFT JOIN Wot_Sales.dbo.Shipper sh ON i.ShipperId = sh.ShipperId WHERE i.[State] <> -1 AND i.LogisticCode IS NOT NULL AND i.[Status] >= 4 AND i.IsAddressCheck = 1 AND i.IsPrintCheck = 1 AND i.TraceState NOT IN(6,3,4)
PS:欢迎扫描下方二维码或点击链接,加入QQ群