任务描述

1.问题分析


在ERP日志表里，慢sql top 2,  70秒--600秒，测试8分钟查不处理；


select count(0) from RB_OrderInfo nolock --  46116303
select count(0) from RB_OrderList nolock -- 59512660

2.解决方案

将大量的运行在数据库运算，转移到应用程序计算 


1.先从数据库查询出基础数据；

2.在应用程序过滤


优化后的sql;

SELECT *
FROM   (SELECT Row_number()
                 OVER (
                   ORDER BY PayTime DESC) AS [PageIndex],
               *
        FROM   (SELECT oi.ID,
                       oi.OrderCode,
                       oi.ClientOrderCode,
                       oi.MainOrderCode,
                       oi.MergeOrderCode,
                       oi.SalesRecordNumber,
                       oi.OrderSourceID,
                       oi.ClientUserAccount,
                       oi.OrderStatus,
                       oi.OrderState,
                       oi.IsFirstDeliver,
                       ( CASE
                           WHEN oi.IsPriority > 0 THEN oi.IsPriority
                           ELSE Isnull(os.PriorityNum, 0)
                         END )                                                                                   AS IsPriority,
                       oi.PackagedIDs,
                       oi.IsASingleInTheEnd,
                       oi.PayerId,
                       oi.Email,
                       oi.SellerOrderId,
                       oi.IsPay,
                       oi.CommentType,
                       oi.TransactionId,
                       oi.PaymentType,
                       Isnull(oi.RefundPrice, 0)                                                                 AS RefundPrice,
                       Isnull(oi.ThrowingRate, 0)                                                                AS ThrowingRate,
                       oi.TotalPrice,
                       oi.ExchangeRMB,
                       oi.ExchangeUSD,
                       oi.TransportPay,
                       oi.OtherPayPrice,
                       oi.ProductTotalPrice,
                       oi.PromotionDiscountAmount,
                       oi.CouponCode,
                       oi.CouponMinusPrice,
                       oi.DiscountMinusPrice,
                       oi.AddPayPrice,
                       oi.AddOrderReasonType,
                       oi.ShareType,
                       oi.ShareCustomerID,
                       oi.IsAccount,
                       oi.AccountTime,
                       oi.ShareTotalPrice,
                       oi.SynchronousTime,
                       oi.IsSynchronous,
                       oi.IsTogeInvoice,
                       Isnull(oi.Encrypted, 0)                                                                   AS Encrypted,
                       oi.FirstName,
                       oi.LastName,
                       oi.Address,
                       oi.District,
                       oi.CabinetNO,
                       oi.City,
                       oi.Province,
                       oi.Country,
                       oi.PostCode,
                       oi.Telephone,
                       oi.ShippingService,
                       oi.DownLoadUpdateTime,
                       oi.PromotionType,
                       oi.TrackNumberTemp,
                       oi.TrackNumbers,
                       oi.UpdatePlatformTime,
                       oi.MarkDeliveryState,
                       oi.EMPSDeliveryState,
                       oi.LogisticsOrderNo,
                       oi.LogisticsOrderNo1,
                       oi.ShipTime,
                       oi.OrderDescription,
                       oi.ISsynchOverseas,
                       oi.ProductNumber,
                       oi.ProductVolume,
                       oi.ProductWeight,
                       oi.PayTime,
                       oi.AddTime,
                       oi.UpdateTime,
                       oi.LatestShipDate,
                       oi.ServiceAdminName,
                       oi.SKUNumber,
                       oi.LatestDeliveryDate,
                       oi.VipDiscount,
                       Isnull(oi.IsTaxFree, 0)                                                                   IsTaxFree,
                       oi.Currency,
                       os.ServiceAdminName                                                                       AS ServiceOSAdminName,
                       Isnull(oi.IsFBATransit, 0)                                                                IsFBATransit,
                       oi.WareHouseID,
                       wh.WareHouseName,
                       os.OrderSourceName,
                       os.OrderSourceType,
                       os.OrderSourceCountry,
                       e.Country_cn,
                       t.TransportName,
                       t.PMSTransport,
                       tapi.Transport_API_Class,
                       wh.WareHouseType,
                       oi.Token,
                       os.SaleAdminName,
                       os.VATNumber,
                       os.VATRate,
                       oi.SaveTrackNumbersTime,
                       t.TrackingNumberValidDays,
                       Isnull(t.TrackNumbersIsNaturalDay, 1)                                                     AS TrackNumbersIsNaturalDay,
                       Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,
                       bt.BusinessTeamName,
                       t.ID                                                                                      AS TransportID,
                       e.ID                                                                                      AS CountryID,
                       oi.Oaid,
                       api.CertIDAccessKeyId,
                       Isnull(cexpand.BillPdfUrl, '')                                                            BillPdfUrl,
                       att1.IsAttachMail                                                                         AS IsAttachMail
                FROM   RB_OrderInfo oi WITH(nolock)
                       LEFT JOIN RB_OrderSource os WITH(nolock)
                              ON oi.CustomerID = os.CustomerID
                                 AND oi.OrderSourceID = os.ID
                       LEFT JOIN E_Country e WITH(nolock)
                              ON oi.Country = e.Shorthand
                       LEFT JOIN RB_Transport t WITH(nolock)
                              ON oi.CustomerID = t.CustomerID
                                 AND oi.TransportID = t.ID
                       LEFT JOIN RB_Transport_API tapi WITH(nolock)
                              ON t.Transport_API_ID = tapi.ID
                                 AND t.CustomerID = tapi.CustomerID
                       LEFT JOIN RB_WareHouse wh WITH(nolock)
                              ON oi.CustomerID = wh.CustomerID
                                 AND oi.WareHouseID = wh.ID
                       LEFT JOIN RB_Admin rba WITH(nolock)
                              ON rba.CustomerID = os.CustomerID
                                 AND rba.ID = os.SaleAdminID
                       LEFT JOIN RB_BusinessTeam bt WITH(nolock)
                              ON bt.CustomerID = os.CustomerID
                                 AND bt.ID = rba.BusinessTeamID
                       LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)
                              ON os.CustomerID = api.CustomerID
                                 AND os.ID = api.OrderSourceID
                       LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand
                              ON oi.CustomerID = cexpand.CustomerID
                                 AND oi.OrderCode = cexpand.OrderCode
                       OUTER apply (SELECT Count(1) IsAttachMail
                                    FROM   RB_OrderInfo_Attach att(nolock)
                                    WHERE  att.CustomerID = oi.CustomerID
                                           AND att.OrderCode = oi.OrderCode) att1
                WHERE  oi.CustomerID = 1
                       AND oi.IsStockOrder = 0
                       AND oi.IsPay = 1
                       AND oi.PayTime >= '2023/4/1 0:00:00'
                       AND oi.PayTime <= '2023/4/13 23:59:59'
                       --AND EXISTS (SELECT 1
                       --            FROM   RB_OrderList ol WITH(nolock)
                       --                   INNER JOIN RB_Product_Admin pa WITH(nolock)
                       --                           ON ol.CustomerID = pa.CustomerID
                       --                              AND ol.SKU = pa.SKU
                       --            WHERE  ol.CustomerID = 1
                       --                   AND ol.OrderCode = oi.OrderCode
                       --                   AND pa.DevelopAdminID = 6323)
  ) AS TempTable) AS PagerTable
WHERE  PagerTable.[PageIndex] BETWEEN 1 AND 500



3.sql



SELECT *
FROM   (SELECT Row_number()
                 OVER (
                   ORDER BY PayTime DESC) AS [PageIndex],
               *
        FROM   (SELECT oi.ID,
                       oi.OrderCode,
                       oi.ClientOrderCode,
                       oi.MainOrderCode,
                       oi.MergeOrderCode,
                       oi.SalesRecordNumber,
                       oi.OrderSourceID,
                       oi.ClientUserAccount,
                       oi.OrderStatus,
                       oi.OrderState,
                       oi.IsFirstDeliver,
                       ( CASE
                           WHEN oi.IsPriority > 0 THEN oi.IsPriority
                           ELSE Isnull(os.PriorityNum, 0)
                         END )                                                                                   AS IsPriority,
                       oi.PackagedIDs,
                       oi.IsASingleInTheEnd,
                       oi.PayerId,
                       oi.Email,
                       oi.SellerOrderId,
                       oi.IsPay,
                       oi.CommentType,
                       oi.TransactionId,
                       oi.PaymentType,
                       Isnull(oi.RefundPrice, 0)                                                                 AS RefundPrice,
                       Isnull(oi.ThrowingRate, 0)                                                                AS ThrowingRate,
                       oi.TotalPrice,
                       oi.ExchangeRMB,
                       oi.ExchangeUSD,
                       oi.TransportPay,
                       oi.OtherPayPrice,
                       oi.ProductTotalPrice,
                       oi.PromotionDiscountAmount,
                       oi.CouponCode,
                       oi.CouponMinusPrice,
                       oi.DiscountMinusPrice,
                       oi.AddPayPrice,
                       oi.AddOrderReasonType,
                       oi.ShareType,
                       oi.ShareCustomerID,
                       oi.IsAccount,
                       oi.AccountTime,
                       oi.ShareTotalPrice,
                       oi.SynchronousTime,
                       oi.IsSynchronous,
                       oi.IsTogeInvoice,
                       Isnull(oi.Encrypted, 0)                                                                   AS Encrypted,
                       oi.FirstName,
                       oi.LastName,
                       oi.Address,
                       oi.District,
                       oi.CabinetNO,
                       oi.City,
                       oi.Province,
                       oi.Country,
                       oi.PostCode,
                       oi.Telephone,
                       oi.ShippingService,
                       oi.DownLoadUpdateTime,
                       oi.PromotionType,
                       oi.TrackNumberTemp,
                       oi.TrackNumbers,
                       oi.UpdatePlatformTime,
                       oi.MarkDeliveryState,
                       oi.EMPSDeliveryState,
                       oi.LogisticsOrderNo,
                       oi.LogisticsOrderNo1,
                       oi.ShipTime,
                       oi.OrderDescription,
                       oi.ISsynchOverseas,
                       oi.ProductNumber,
                       oi.ProductVolume,
                       oi.ProductWeight,
                       oi.PayTime,
                       oi.AddTime,
                       oi.UpdateTime,
                       oi.LatestShipDate,
                       oi.ServiceAdminName,
                       oi.SKUNumber,
                       oi.LatestDeliveryDate,
                       oi.VipDiscount,
                       Isnull(oi.IsTaxFree, 0)                                                                   IsTaxFree,
                       oi.Currency,
                       os.ServiceAdminName                                                                       AS ServiceOSAdminName,
                       Isnull(oi.IsFBATransit, 0)                                                                IsFBATransit,
                       oi.WareHouseID,
                       wh.WareHouseName,
                       os.OrderSourceName,
                       os.OrderSourceType,
                       os.OrderSourceCountry,
                       e.Country_cn,
                       t.TransportName,
                       t.PMSTransport,
                       tapi.Transport_API_Class,
                       wh.WareHouseType,
                       oi.Token,
                       os.SaleAdminName,
                       os.VATNumber,
                       os.VATRate,
                       oi.SaveTrackNumbersTime,
                       t.TrackingNumberValidDays,
                       Isnull(t.TrackNumbersIsNaturalDay, 1)                                                     AS TrackNumbersIsNaturalDay,
                       Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,
                       bt.BusinessTeamName,
                       t.ID                                                                                      AS TransportID,
                       e.ID                                                                                      AS CountryID,
                       oi.Oaid,
                       api.CertIDAccessKeyId,
                       Isnull(cexpand.BillPdfUrl, '')                                                            BillPdfUrl,
                       att1.IsAttachMail                                                                         AS IsAttachMail
                FROM   RB_OrderInfo oi WITH(nolock)
                       LEFT JOIN RB_OrderSource os WITH(nolock)
                              ON oi.CustomerID = os.CustomerID
                                 AND oi.OrderSourceID = os.ID
                       LEFT JOIN E_Country e WITH(nolock)
                              ON oi.Country = e.Shorthand
                       LEFT JOIN RB_Transport t WITH(nolock)
                              ON oi.CustomerID = t.CustomerID
                                 AND oi.TransportID = t.ID
                       LEFT JOIN RB_Transport_API tapi WITH(nolock)
                              ON t.Transport_API_ID = tapi.ID
                                 AND t.CustomerID = tapi.CustomerID
                       LEFT JOIN RB_WareHouse wh WITH(nolock)
                              ON oi.CustomerID = wh.CustomerID
                                 AND oi.WareHouseID = wh.ID
                       LEFT JOIN RB_Admin rba WITH(nolock)
                              ON rba.CustomerID = os.CustomerID
                                 AND rba.ID = os.SaleAdminID
                       LEFT JOIN RB_BusinessTeam bt WITH(nolock)
                              ON bt.CustomerID = os.CustomerID
                                 AND bt.ID = rba.BusinessTeamID
                       LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)
                              ON os.CustomerID = api.CustomerID
                                 AND os.ID = api.OrderSourceID
                       LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand
                              ON oi.CustomerID = cexpand.CustomerID
                                 AND oi.OrderCode = cexpand.OrderCode
                       OUTER apply (SELECT Count(1) IsAttachMail
                                    FROM   RB_OrderInfo_Attach att(nolock)
                                    WHERE  att.CustomerID = oi.CustomerID
                                           AND att.OrderCode = oi.OrderCode) att1
                WHERE  oi.CustomerID = 1
                       AND oi.IsStockOrder = 0
                       AND oi.IsPay = 1
                       AND oi.PayTime >= '2023/4/1 0:00:00'
                       AND oi.PayTime <= '2023/4/13 23:59:59'
                       AND EXISTS (SELECT 1
                                   FROM   RB_OrderList ol WITH(nolock)
                                          INNER JOIN RB_Product_Admin pa WITH(nolock)
                                                  ON ol.CustomerID = pa.CustomerID
                                                     AND ol.SKU = pa.SKU
                                   WHERE  ol.CustomerID = 1
                                          AND ol.OrderCode = oi.OrderCode
                                          AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTable
WHERE  PagerTable.[PageIndex] BETWEEN 1 AND 500
OPTION(recompile, maxdop 12)

历史记录  
  1. 2023-04-14 15:28:20, 由 陈少军 创建。
  2. 2023-04-14 16:02:02, 由 陈少军 编辑。
    修改了 任务名称，区别为：
    001- 【ERP 共享库慢sql 】E_Amazon_ShelfTemplateAttributes top 10慢sql优化
    001+ 【ERP 库慢sql 】RB_OrderInfo top 2慢sql优化
    001- 【ERP 共享库慢sql 】E_Amazon_ShelfTemplateAttributes top 10慢sql优化
    001+ 【ERP 库慢sql 】RB_OrderInfo top 2慢sql优化
    修改了 任务描述，区别为：
    003- 在ERP日志表里，大量的这种慢sql，查询时间46--560秒不等；
    003+ 在ERP日志表里，慢sql top 2, 70秒--600秒，测试8分钟查不处理；
    004- select count(0) from E_Amazon_ShelfTemplateAttributes nolock -- 160002807 1.6亿数据
    004+
    005-
    005+ select count(0) from RB_OrderInfo nolock -- 46116303select count(0) from RB_OrderList nolock -- 59512660
    007- 1.E_Amazon_ShelfTemplateAttributes nolock -- 160002807 1.6亿数据 是否可以归档，和翔宇沟通了不能归档，此处先记录后面和业务沟通解决办法
    007+ 将大量的运行在数据库运算，转移到应用程序计算
    008- 2.有使用索引
    008+
    009-
    009+ 1.先从数据库查询出基础数据；
    010- 3.sql
    010+ 2.在应用程序过滤
    012- SELECT alias.[ID], alias.[TemplateID], alias.[LocalLabelName], alias.[FieldName], alias.[AttributeType], alias.[AttributeName], alias.[AttributePosition], alias.[IsActivate], alias.[SerialNo], alias.[IsRequired], alias.[AddTime], alias.[AddUserID], alias.[AddUserName], alias.[UpdateTime], alias.[UpdateUserID], alias.[UpdateUserName], alias.[AttributeMaxLength], alias.[DefinitionUse], alias.[TableName], alias.[ColumnName], alias.[AcceptedValues], alias.[Example], alias.[IsDisplay], alias.[DisplaySerialNo], alias.[Country], alias.[AttibuteValues]FROM E_Amazon_ShelfTemplateAttributes (NOLOCK) alias INNER JOIN @tb_TemplateIDs IDS ON alias.TemplateID = IDS.IdWHERE alias.IsActivate = 1;
    012+ 优化后的sql;
    013+ SELECT *FROM (SELECT Row_number() OVER ( ORDER BY PayTime DESC) AS [PageIndex], * FROM (SELECT oi.ID, oi.OrderCode, oi.ClientOrderCode, oi.MainOrderCode, oi.MergeOrderCode, oi.SalesRecordNumber, oi.OrderSourceID, oi.ClientUserAccount, oi.OrderStatus, oi.OrderState, oi.IsFirstDeliver, ( CASE WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority ELSE Isnull(os.PriorityNum, 0) END ) AS IsPriority, oi.PackagedIDs, oi.IsASingleInTheEnd, oi.PayerId, oi.Email, oi.SellerOrderId, oi.IsPay, oi.CommentType, oi.TransactionId, oi.PaymentType, Isnull(oi.RefundPrice, 0) AS RefundPrice, Isnull(oi.ThrowingRate, 0) AS ThrowingRate, oi.TotalPrice, oi.ExchangeRMB, oi.ExchangeUSD, oi.TransportPay, oi.OtherPayPrice, oi.ProductTotalPrice, oi.PromotionDiscountAmount, oi.CouponCode, oi.CouponMinusPrice, oi.DiscountMinusPrice, oi.AddPayPrice, oi.AddOrderReasonType, oi.ShareType, oi.ShareCustomerID, oi.IsAccount, oi.AccountTime, oi.ShareTotalPrice, oi.SynchronousTime, oi.IsSynchronous, oi.IsTogeInvoice, Isnull(oi.Encrypted, 0) AS Encrypted, oi.FirstName, oi.LastName, oi.Address, oi.District, oi.CabinetNO, oi.City, oi.Province, oi.Country, oi.PostCode, oi.Telephone, oi.ShippingService, oi.DownLoadUpdateTime, oi.PromotionType, oi.TrackNumberTemp, oi.TrackNumbers, oi.UpdatePlatformTime, oi.MarkDeliveryState, oi.EMPSDeliveryState, oi.LogisticsOrderNo, oi.LogisticsOrderNo1, oi.ShipTime, oi.OrderDescription, oi.ISsynchOverseas, oi.ProductNumber, oi.ProductVolume, oi.ProductWeight, oi.PayTime, oi.AddTime, oi.UpdateTime, oi.LatestShipDate, oi.ServiceAdminName, oi.SKUNumber, oi.LatestDeliveryDate, oi.VipDiscount, Isnull(oi.IsTaxFree, 0) IsTaxFree, oi.Currency, os.ServiceAdminName AS ServiceOSAdminName, Isnull(oi.IsFBATransit, 0) IsFBATransit, oi.WareHouseID, wh.WareHouseName, os.OrderSourceName, os.OrderSourceType, os.OrderSourceCountry, e.Country_cn, t.TransportName, t.PMSTransport, tapi.Transport_API_Class, wh.WareHouseType, oi.Token, os.SaleAdminName, os.VATNumber, os.VATRate, oi.SaveTrackNumbersTime, t.TrackingNumberValidDays, Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay, Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay, bt.BusinessTeamName, t.ID AS TransportID, e.ID AS CountryID, oi.Oaid, api.CertIDAccessKeyId, Isnull(cexpand.BillPdfUrl, '') BillPdfUrl, att1.IsAttachMail AS IsAttachMail FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN RB_OrderSource os WITH(nolock) ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID LEFT JOIN E_Country e WITH(nolock) ON oi.Country = e.Shorthand LEFT JOIN RB_Transport t WITH(nolock) ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID LEFT JOIN RB_Transport_API tapi WITH(nolock) ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID LEFT JOIN RB_WareHouse wh WITH(nolock) ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID LEFT JOIN RB_Admin rba WITH(nolock) ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID LEFT JOIN RB_BusinessTeam bt WITH(nolock) ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock) ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand ON oi.CustomerID = cexpand.CustomerID AND oi.OrderCode = cexpand.OrderCode OUTER apply (SELECT Count(1) IsAttachMail FROM RB_OrderInfo_Attach att(nolock) WHERE att.CustomerID = oi.CustomerID AND att.OrderCode = oi.OrderCode) att1 WHERE oi.CustomerID = 1 AND oi.IsStockOrder = 0 AND oi.IsPay = 1 AND oi.PayTime &gt;= '2023/4/1 0:00:00' AND oi.PayTime &lt;= '2023/4/13 23:59:59' --AND EXISTS (SELECT 1 -- FROM RB_OrderList ol WITH(nolock) -- INNER JOIN RB_Product_Admin pa WITH(nolock) -- ON ol.CustomerID = pa.CustomerID -- AND ol.SKU = pa.SKU -- WHERE ol.CustomerID = 1 -- AND ol.OrderCode = oi.OrderCode -- AND pa.DevelopAdminID = 6323) ) AS TempTable) AS PagerTableWHERE PagerTable.[PageIndex] BETWEEN 1 AND 500
    014+
    015+
    016+ 3.sql
    017+
    018+ SELECT *FROM (SELECT Row_number() OVER ( ORDER BY PayTime DESC) AS [PageIndex], * FROM (SELECT oi.ID, oi.OrderCode, oi.ClientOrderCode, oi.MainOrderCode, oi.MergeOrderCode, oi.SalesRecordNumber, oi.OrderSourceID, oi.ClientUserAccount, oi.OrderStatus, oi.OrderState, oi.IsFirstDeliver, ( CASE WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority ELSE Isnull(os.PriorityNum, 0) END ) AS IsPriority, oi.PackagedIDs, oi.IsASingleInTheEnd, oi.PayerId, oi.Email, oi.SellerOrderId, oi.IsPay, oi.CommentType, oi.TransactionId, oi.PaymentType, Isnull(oi.RefundPrice, 0) AS RefundPrice, Isnull(oi.ThrowingRate, 0) AS ThrowingRate, oi.TotalPrice, oi.ExchangeRMB, oi.ExchangeUSD, oi.TransportPay, oi.OtherPayPrice, oi.ProductTotalPrice, oi.PromotionDiscountAmount, oi.CouponCode, oi.CouponMinusPrice, oi.DiscountMinusPrice, oi.AddPayPrice, oi.AddOrderReasonType, oi.ShareType, oi.ShareCustomerID, oi.IsAccount, oi.AccountTime, oi.ShareTotalPrice, oi.SynchronousTime, oi.IsSynchronous, oi.IsTogeInvoice, Isnull(oi.Encrypted, 0) AS Encrypted, oi.FirstName, oi.LastName, oi.Address, oi.District, oi.CabinetNO, oi.City, oi.Province, oi.Country, oi.PostCode, oi.Telephone, oi.ShippingService, oi.DownLoadUpdateTime, oi.PromotionType, oi.TrackNumberTemp, oi.TrackNumbers, oi.UpdatePlatformTime, oi.MarkDeliveryState, oi.EMPSDeliveryState, oi.LogisticsOrderNo, oi.LogisticsOrderNo1, oi.ShipTime, oi.OrderDescription, oi.ISsynchOverseas, oi.ProductNumber, oi.ProductVolume, oi.ProductWeight, oi.PayTime, oi.AddTime, oi.UpdateTime, oi.LatestShipDate, oi.ServiceAdminName, oi.SKUNumber, oi.LatestDeliveryDate, oi.VipDiscount, Isnull(oi.IsTaxFree, 0) IsTaxFree, oi.Currency, os.ServiceAdminName AS ServiceOSAdminName, Isnull(oi.IsFBATransit, 0) IsFBATransit, oi.WareHouseID, wh.WareHouseName, os.OrderSourceName, os.OrderSourceType, os.OrderSourceCountry, e.Country_cn, t.TransportName, t.PMSTransport, tapi.Transport_API_Class, wh.WareHouseType, oi.Token, os.SaleAdminName, os.VATNumber, os.VATRate, oi.SaveTrackNumbersTime, t.TrackingNumberValidDays, Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay, Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay, bt.BusinessTeamName, t.ID AS TransportID, e.ID AS CountryID, oi.Oaid, api.CertIDAccessKeyId, Isnull(cexpand.BillPdfUrl, '') BillPdfUrl, att1.IsAttachMail AS IsAttachMail FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN RB_OrderSource os WITH(nolock) ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID LEFT JOIN E_Country e WITH(nolock) ON oi.Country = e.Shorthand LEFT JOIN RB_Transport t WITH(nolock) ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID LEFT JOIN RB_Transport_API tapi WITH(nolock) ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID LEFT JOIN RB_WareHouse wh WITH(nolock) ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID LEFT JOIN RB_Admin rba WITH(nolock) ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID LEFT JOIN RB_BusinessTeam bt WITH(nolock) ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock) ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand ON oi.CustomerID = cexpand.CustomerID AND oi.OrderCode = cexpand.OrderCode OUTER apply (SELECT Count(1) IsAttachMail FROM RB_OrderInfo_Attach att(nolock) WHERE att.CustomerID = oi.CustomerID AND att.OrderCode = oi.OrderCode) att1 WHERE oi.CustomerID = 1 AND oi.IsStockOrder = 0 AND oi.IsPay = 1 AND oi.PayTime &gt;= '2023/4/1 0:00:00' AND oi.PayTime &lt;= '2023/4/13 23:59:59' AND EXISTS (SELECT 1 FROM RB_OrderList ol WITH(nolock) INNER JOIN RB_Product_Admin pa WITH(nolock) ON ol.CustomerID = pa.CustomerID AND ol.SKU = pa.SKU WHERE ol.CustomerID = 1 AND ol.OrderCode = oi.OrderCode AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTableWHERE PagerTable.[PageIndex] BETWEEN 1 AND 500OPTION(recompile, maxdop 12)
    003- <p>在ERP日志表里，大量的这种慢sql，查询时间46--560秒不等；</p>
    003+ <p>在ERP日志表里，慢sql top 2, 70秒--600秒，测试8分钟查不处理；</p>
    004- <p><span>select count(0) from E_Amazon_ShelfTemplateAttributes nolock -- 160002807 1.6亿数据</span></p>
    004+ <p><br /></p>
    005- <p><br /></p>
    005+ <p>select count(0) from RB_OrderInfo nolock -- 46116303<br />select count(0) from RB_OrderList nolock -- 59512660<br /></p>
    007- <p>1.<span>E_Amazon_ShelfTemplateAttributes nolock -- 160002807 1.6亿数据 是否可以归档，和翔宇沟通了不能归档，此处先记录后面和业务沟通解决办法</span></p>
    007+ <p>将大量的运行在数据库运算，转移到应用程序计算</p>
    008- <p>2.有使用索引</p>
    008+ <p><span><br /></span></p>
    009- <p><span><br /></span></p>
    009+ <p><span>1.先从数据库查询出基础数据；</span></p>
    010- <p>3.sql</p>
    010+ <p><span>2.在应用程序过滤</span></p>
    012- <p>SELECT alias.[ID],<br /> alias.[TemplateID],<br /> alias.[LocalLabelName],<br /> alias.[FieldName],<br /> alias.[AttributeType],<br /> alias.[AttributeName],<br /> alias.[AttributePosition],<br /> alias.[IsActivate],<br /> alias.[SerialNo],<br /> alias.[IsRequired],<br /> alias.[AddTime],<br /> alias.[AddUserID],<br /> alias.[AddUserName],<br /> alias.[UpdateTime],<br /> alias.[UpdateUserID],<br /> alias.[UpdateUserName],<br /> alias.[AttributeMaxLength],<br /> alias.[DefinitionUse],<br /> alias.[TableName],<br /> alias.[ColumnName],<br /> alias.[AcceptedValues],<br /> alias.[Example],<br /> alias.[IsDisplay],<br /> alias.[DisplaySerialNo],<br /> alias.[Country],<br /> alias.[AttibuteValues]<br />FROM E_Amazon_ShelfTemplateAttributes (NOLOCK) alias<br /> INNER JOIN @tb_TemplateIDs IDS<br /> ON alias.TemplateID = IDS.Id<br />WHERE alias.IsActivate = 1;<br /><br /><br /></p>
    012+ <p>优化后的sql;</p>
    013+ <p>SELECT *<br />FROM (SELECT Row_number()<br /> OVER (<br /> ORDER BY PayTime DESC) AS [PageIndex],<br /> *<br /> FROM (SELECT oi.ID,<br /> oi.OrderCode,<br /> oi.ClientOrderCode,<br /> oi.MainOrderCode,<br /> oi.MergeOrderCode,<br /> oi.SalesRecordNumber,<br /> oi.OrderSourceID,<br /> oi.ClientUserAccount,<br /> oi.OrderStatus,<br /> oi.OrderState,<br /> oi.IsFirstDeliver,<br /> ( CASE<br /> WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority<br /> ELSE Isnull(os.PriorityNum, 0)<br /> END ) AS IsPriority,<br /> oi.PackagedIDs,<br /> oi.IsASingleInTheEnd,<br /> oi.PayerId,<br /> oi.Email,<br /> oi.SellerOrderId,<br /> oi.IsPay,<br /> oi.CommentType,<br /> oi.TransactionId,<br /> oi.PaymentType,<br /> Isnull(oi.RefundPrice, 0) AS RefundPrice,<br /> Isnull(oi.ThrowingRate, 0) AS ThrowingRate,<br /> oi.TotalPrice,<br /> oi.ExchangeRMB,<br /> oi.ExchangeUSD,<br /> oi.TransportPay,<br /> oi.OtherPayPrice,<br /> oi.ProductTotalPrice,<br /> oi.PromotionDiscountAmount,<br /> oi.CouponCode,<br /> oi.CouponMinusPrice,<br /> oi.DiscountMinusPrice,<br /> oi.AddPayPrice,<br /> oi.AddOrderReasonType,<br /> oi.ShareType,<br /> oi.ShareCustomerID,<br /> oi.IsAccount,<br /> oi.AccountTime,<br /> oi.ShareTotalPrice,<br /> oi.SynchronousTime,<br /> oi.IsSynchronous,<br /> oi.IsTogeInvoice,<br /> Isnull(oi.Encrypted, 0) AS Encrypted,<br /> oi.FirstName,<br /> oi.LastName,<br /> oi.Address,<br /> oi.District,<br /> oi.CabinetNO,<br /> oi.City,<br /> oi.Province,<br /> oi.Country,<br /> oi.PostCode,<br /> oi.Telephone,<br /> oi.ShippingService,<br /> oi.DownLoadUpdateTime,<br /> oi.PromotionType,<br /> oi.TrackNumberTemp,<br /> oi.TrackNumbers,<br /> oi.UpdatePlatformTime,<br /> oi.MarkDeliveryState,<br /> oi.EMPSDeliveryState,<br /> oi.LogisticsOrderNo,<br /> oi.LogisticsOrderNo1,<br /> oi.ShipTime,<br /> oi.OrderDescription,<br /> oi.ISsynchOverseas,<br /> oi.ProductNumber,<br /> oi.ProductVolume,<br /> oi.ProductWeight,<br /> oi.PayTime,<br /> oi.AddTime,<br /> oi.UpdateTime,<br /> oi.LatestShipDate,<br /> oi.ServiceAdminName,<br /> oi.SKUNumber,<br /> oi.LatestDeliveryDate,<br /> oi.VipDiscount,<br /> Isnull(oi.IsTaxFree, 0) IsTaxFree,<br /> oi.Currency,<br /> os.ServiceAdminName AS ServiceOSAdminName,<br /> Isnull(oi.IsFBATransit, 0) IsFBATransit,<br /> oi.WareHouseID,<br /> wh.WareHouseName,<br /> os.OrderSourceName,<br /> os.OrderSourceType,<br /> os.OrderSourceCountry,<br /> e.Country_cn,<br /> t.TransportName,<br /> t.PMSTransport,<br /> tapi.Transport_API_Class,<br /> wh.WareHouseType,<br /> oi.Token,<br /> os.SaleAdminName,<br /> os.VATNumber,<br /> os.VATRate,<br /> oi.SaveTrackNumbersTime,<br /> t.TrackingNumberValidDays,<br /> Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay,<br /> Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,<br /> bt.BusinessTeamName,<br /> t.ID AS TransportID,<br /> e.ID AS CountryID,<br /> oi.Oaid,<br /> api.CertIDAccessKeyId,<br /> Isnull(cexpand.BillPdfUrl, '') BillPdfUrl,<br /> att1.IsAttachMail AS IsAttachMail<br /> FROM RB_OrderInfo oi WITH(nolock)<br /> LEFT JOIN RB_OrderSource os WITH(nolock)<br /> ON oi.CustomerID = os.CustomerID<br /> AND oi.OrderSourceID = os.ID<br /> LEFT JOIN E_Country e WITH(nolock)<br /> ON oi.Country = e.Shorthand<br /> LEFT JOIN RB_Transport t WITH(nolock)<br /> ON oi.CustomerID = t.CustomerID<br /> AND oi.TransportID = t.ID<br /> LEFT JOIN RB_Transport_API tapi WITH(nolock)<br /> ON t.Transport_API_ID = tapi.ID<br /> AND t.CustomerID = tapi.CustomerID<br /> LEFT JOIN RB_WareHouse wh WITH(nolock)<br /> ON oi.CustomerID = wh.CustomerID<br /> AND oi.WareHouseID = wh.ID<br /> LEFT JOIN RB_Admin rba WITH(nolock)<br /> ON rba.CustomerID = os.CustomerID<br /> AND rba.ID = os.SaleAdminID<br /> LEFT JOIN RB_BusinessTeam bt WITH(nolock)<br /> ON bt.CustomerID = os.CustomerID<br /> AND bt.ID = rba.BusinessTeamID<br /> LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)<br /> ON os.CustomerID = api.CustomerID<br /> AND os.ID = api.OrderSourceID<br /> LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand<br /> ON oi.CustomerID = cexpand.CustomerID<br /> AND oi.OrderCode = cexpand.OrderCode<br /> OUTER apply (SELECT Count(1) IsAttachMail<br /> FROM RB_OrderInfo_Attach att(nolock)<br /> WHERE att.CustomerID = oi.CustomerID<br /> AND att.OrderCode = oi.OrderCode) att1<br /> WHERE oi.CustomerID = 1<br /> AND oi.IsStockOrder = 0<br /> AND oi.IsPay = 1<br /> AND oi.PayTime &gt;= '2023/4/1 0:00:00'<br /> AND oi.PayTime &lt;= '2023/4/13 23:59:59'<br /> --AND EXISTS (SELECT 1<br /> -- FROM RB_OrderList ol WITH(nolock)<br /> -- INNER JOIN RB_Product_Admin pa WITH(nolock)<br /> -- ON ol.CustomerID = pa.CustomerID<br /> -- AND ol.SKU = pa.SKU<br /> -- WHERE ol.CustomerID = 1<br /> -- AND ol.OrderCode = oi.OrderCode<br /> -- AND pa.DevelopAdminID = 6323)<br /><span> </span> ) AS TempTable) AS PagerTable<br />WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500<br /></p>
    014+ <p><br /></p>
    015+ <p><br /></p>
    016+ <p>3.sql</p>
    017+ <p><br /></p>
    018+ <p><br />SELECT *<br />FROM (SELECT Row_number()<br /> OVER (<br /> ORDER BY PayTime DESC) AS [PageIndex],<br /> *<br /> FROM (SELECT oi.ID,<br /> oi.OrderCode,<br /> oi.ClientOrderCode,<br /> oi.MainOrderCode,<br /> oi.MergeOrderCode,<br /> oi.SalesRecordNumber,<br /> oi.OrderSourceID,<br /> oi.ClientUserAccount,<br /> oi.OrderStatus,<br /> oi.OrderState,<br /> oi.IsFirstDeliver,<br /> ( CASE<br /> WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority<br /> ELSE Isnull(os.PriorityNum, 0)<br /> END ) AS IsPriority,<br /> oi.PackagedIDs,<br /> oi.IsASingleInTheEnd,<br /> oi.PayerId,<br /> oi.Email,<br /> oi.SellerOrderId,<br /> oi.IsPay,<br /> oi.CommentType,<br /> oi.TransactionId,<br /> oi.PaymentType,<br /> Isnull(oi.RefundPrice, 0) AS RefundPrice,<br /> Isnull(oi.ThrowingRate, 0) AS ThrowingRate,<br /> oi.TotalPrice,<br /> oi.ExchangeRMB,<br /> oi.ExchangeUSD,<br /> oi.TransportPay,<br /> oi.OtherPayPrice,<br /> oi.ProductTotalPrice,<br /> oi.PromotionDiscountAmount,<br /> oi.CouponCode,<br /> oi.CouponMinusPrice,<br /> oi.DiscountMinusPrice,<br /> oi.AddPayPrice,<br /> oi.AddOrderReasonType,<br /> oi.ShareType,<br /> oi.ShareCustomerID,<br /> oi.IsAccount,<br /> oi.AccountTime,<br /> oi.ShareTotalPrice,<br /> oi.SynchronousTime,<br /> oi.IsSynchronous,<br /> oi.IsTogeInvoice,<br /> Isnull(oi.Encrypted, 0) AS Encrypted,<br /> oi.FirstName,<br /> oi.LastName,<br /> oi.Address,<br /> oi.District,<br /> oi.CabinetNO,<br /> oi.City,<br /> oi.Province,<br /> oi.Country,<br /> oi.PostCode,<br /> oi.Telephone,<br /> oi.ShippingService,<br /> oi.DownLoadUpdateTime,<br /> oi.PromotionType,<br /> oi.TrackNumberTemp,<br /> oi.TrackNumbers,<br /> oi.UpdatePlatformTime,<br /> oi.MarkDeliveryState,<br /> oi.EMPSDeliveryState,<br /> oi.LogisticsOrderNo,<br /> oi.LogisticsOrderNo1,<br /> oi.ShipTime,<br /> oi.OrderDescription,<br /> oi.ISsynchOverseas,<br /> oi.ProductNumber,<br /> oi.ProductVolume,<br /> oi.ProductWeight,<br /> oi.PayTime,<br /> oi.AddTime,<br /> oi.UpdateTime,<br /> oi.LatestShipDate,<br /> oi.ServiceAdminName,<br /> oi.SKUNumber,<br /> oi.LatestDeliveryDate,<br /> oi.VipDiscount,<br /> Isnull(oi.IsTaxFree, 0) IsTaxFree,<br /> oi.Currency,<br /> os.ServiceAdminName AS ServiceOSAdminName,<br /> Isnull(oi.IsFBATransit, 0) IsFBATransit,<br /> oi.WareHouseID,<br /> wh.WareHouseName,<br /> os.OrderSourceName,<br /> os.OrderSourceType,<br /> os.OrderSourceCountry,<br /> e.Country_cn,<br /> t.TransportName,<br /> t.PMSTransport,<br /> tapi.Transport_API_Class,<br /> wh.WareHouseType,<br /> oi.Token,<br /> os.SaleAdminName,<br /> os.VATNumber,<br /> os.VATRate,<br /> oi.SaveTrackNumbersTime,<br /> t.TrackingNumberValidDays,<br /> Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay,<br /> Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,<br /> bt.BusinessTeamName,<br /> t.ID AS TransportID,<br /> e.ID AS CountryID,<br /> oi.Oaid,<br /> api.CertIDAccessKeyId,<br /> Isnull(cexpand.BillPdfUrl, '') BillPdfUrl,<br /> att1.IsAttachMail AS IsAttachMail<br /> FROM RB_OrderInfo oi WITH(nolock)<br /> LEFT JOIN RB_OrderSource os WITH(nolock)<br /> ON oi.CustomerID = os.CustomerID<br /> AND oi.OrderSourceID = os.ID<br /> LEFT JOIN E_Country e WITH(nolock)<br /> ON oi.Country = e.Shorthand<br /> LEFT JOIN RB_Transport t WITH(nolock)<br /> ON oi.CustomerID = t.CustomerID<br /> AND oi.TransportID = t.ID<br /> LEFT JOIN RB_Transport_API tapi WITH(nolock)<br /> ON t.Transport_API_ID = tapi.ID<br /> AND t.CustomerID = tapi.CustomerID<br /> LEFT JOIN RB_WareHouse wh WITH(nolock)<br /> ON oi.CustomerID = wh.CustomerID<br /> AND oi.WareHouseID = wh.ID<br /> LEFT JOIN RB_Admin rba WITH(nolock)<br /> ON rba.CustomerID = os.CustomerID<br /> AND rba.ID = os.SaleAdminID<br /> LEFT JOIN RB_BusinessTeam bt WITH(nolock)<br /> ON bt.CustomerID = os.CustomerID<br /> AND bt.ID = rba.BusinessTeamID<br /> LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)<br /> ON os.CustomerID = api.CustomerID<br /> AND os.ID = api.OrderSourceID<br /> LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand<br /> ON oi.CustomerID = cexpand.CustomerID<br /> AND oi.OrderCode = cexpand.OrderCode<br /> OUTER apply (SELECT Count(1) IsAttachMail<br /> FROM RB_OrderInfo_Attach att(nolock)<br /> WHERE att.CustomerID = oi.CustomerID<br /> AND att.OrderCode = oi.OrderCode) att1<br /> WHERE oi.CustomerID = 1<br /> AND oi.IsStockOrder = 0<br /> AND oi.IsPay = 1<br /> AND oi.PayTime &gt;= '2023/4/1 0:00:00'<br /> AND oi.PayTime &lt;= '2023/4/13 23:59:59'<br /> AND EXISTS (SELECT 1<br /> FROM RB_OrderList ol WITH(nolock)<br /> INNER JOIN RB_Product_Admin pa WITH(nolock)<br /> ON ol.CustomerID = pa.CustomerID<br /> AND ol.SKU = pa.SKU<br /> WHERE ol.CustomerID = 1<br /> AND ol.OrderCode = oi.OrderCode<br /> AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTable<br />WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500<br />OPTION(recompile, maxdop 12)<br /><br /><br /></p>
  3. 2023-04-14 16:06:01, 由 陈少军 指派给 程伟。
    修改了 指派给 ，旧值为 "chenshaojun"，新值为 "chengwei"。
    修改了 预计剩余，旧值为 "0"，新值为 "2"。
    试着参考解决方案 优化，目前太慢了，把整个数据库拖死了
  4. 2023-04-14 16:07:00, 由 陈少军 编辑。
    修改了 开发组 ，旧值为 "2"，新值为 "4"。
    修改了 截止日期，旧值为 "2023-04-14"，新值为 "2023-04-17"。
    修改了 任务描述，区别为：
    005- select count(0) from RB_OrderInfo nolock -- 46116303select count(0) from RB_OrderList nolock -- 59512660
    005+ select count(0) from RB_OrderInfo nolock -- 46116303select count(0) from RB_OrderList nolock -- 59512660
    013- SELECT *FROM (SELECT Row_number() OVER ( ORDER BY PayTime DESC) AS [PageIndex], * FROM (SELECT oi.ID, oi.OrderCode, oi.ClientOrderCode, oi.MainOrderCode, oi.MergeOrderCode, oi.SalesRecordNumber, oi.OrderSourceID, oi.ClientUserAccount, oi.OrderStatus, oi.OrderState, oi.IsFirstDeliver, ( CASE WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority ELSE Isnull(os.PriorityNum, 0) END ) AS IsPriority, oi.PackagedIDs, oi.IsASingleInTheEnd, oi.PayerId, oi.Email, oi.SellerOrderId, oi.IsPay, oi.CommentType, oi.TransactionId, oi.PaymentType, Isnull(oi.RefundPrice, 0) AS RefundPrice, Isnull(oi.ThrowingRate, 0) AS ThrowingRate, oi.TotalPrice, oi.ExchangeRMB, oi.ExchangeUSD, oi.TransportPay, oi.OtherPayPrice, oi.ProductTotalPrice, oi.PromotionDiscountAmount, oi.CouponCode, oi.CouponMinusPrice, oi.DiscountMinusPrice, oi.AddPayPrice, oi.AddOrderReasonType, oi.ShareType, oi.ShareCustomerID, oi.IsAccount, oi.AccountTime, oi.ShareTotalPrice, oi.SynchronousTime, oi.IsSynchronous, oi.IsTogeInvoice, Isnull(oi.Encrypted, 0) AS Encrypted, oi.FirstName, oi.LastName, oi.Address, oi.District, oi.CabinetNO, oi.City, oi.Province, oi.Country, oi.PostCode, oi.Telephone, oi.ShippingService, oi.DownLoadUpdateTime, oi.PromotionType, oi.TrackNumberTemp, oi.TrackNumbers, oi.UpdatePlatformTime, oi.MarkDeliveryState, oi.EMPSDeliveryState, oi.LogisticsOrderNo, oi.LogisticsOrderNo1, oi.ShipTime, oi.OrderDescription, oi.ISsynchOverseas, oi.ProductNumber, oi.ProductVolume, oi.ProductWeight, oi.PayTime, oi.AddTime, oi.UpdateTime, oi.LatestShipDate, oi.ServiceAdminName, oi.SKUNumber, oi.LatestDeliveryDate, oi.VipDiscount, Isnull(oi.IsTaxFree, 0) IsTaxFree, oi.Currency, os.ServiceAdminName AS ServiceOSAdminName, Isnull(oi.IsFBATransit, 0) IsFBATransit, oi.WareHouseID, wh.WareHouseName, os.OrderSourceName, os.OrderSourceType, os.OrderSourceCountry, e.Country_cn, t.TransportName, t.PMSTransport, tapi.Transport_API_Class, wh.WareHouseType, oi.Token, os.SaleAdminName, os.VATNumber, os.VATRate, oi.SaveTrackNumbersTime, t.TrackingNumberValidDays, Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay, Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay, bt.BusinessTeamName, t.ID AS TransportID, e.ID AS CountryID, oi.Oaid, api.CertIDAccessKeyId, Isnull(cexpand.BillPdfUrl, '') BillPdfUrl, att1.IsAttachMail AS IsAttachMail FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN RB_OrderSource os WITH(nolock) ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID LEFT JOIN E_Country e WITH(nolock) ON oi.Country = e.Shorthand LEFT JOIN RB_Transport t WITH(nolock) ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID LEFT JOIN RB_Transport_API tapi WITH(nolock) ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID LEFT JOIN RB_WareHouse wh WITH(nolock) ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID LEFT JOIN RB_Admin rba WITH(nolock) ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID LEFT JOIN RB_BusinessTeam bt WITH(nolock) ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock) ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand ON oi.CustomerID = cexpand.CustomerID AND oi.OrderCode = cexpand.OrderCode OUTER apply (SELECT Count(1) IsAttachMail FROM RB_OrderInfo_Attach att(nolock) WHERE att.CustomerID = oi.CustomerID AND att.OrderCode = oi.OrderCode) att1 WHERE oi.CustomerID = 1 AND oi.IsStockOrder = 0 AND oi.IsPay = 1 AND oi.PayTime &gt;= '2023/4/1 0:00:00' AND oi.PayTime &lt;= '2023/4/13 23:59:59' --AND EXISTS (SELECT 1 -- FROM RB_OrderList ol WITH(nolock) -- INNER JOIN RB_Product_Admin pa WITH(nolock) -- ON ol.CustomerID = pa.CustomerID -- AND ol.SKU = pa.SKU -- WHERE ol.CustomerID = 1 -- AND ol.OrderCode = oi.OrderCode -- AND pa.DevelopAdminID = 6323) ) AS TempTable) AS PagerTableWHERE PagerTable.[PageIndex] BETWEEN 1 AND 500
    013+ SELECT *FROM (SELECT Row_number() OVER ( ORDER BY PayTime DESC) AS [PageIndex], * FROM (SELECT oi.ID, oi.OrderCode, oi.ClientOrderCode, oi.MainOrderCode, oi.MergeOrderCode, oi.SalesRecordNumber, oi.OrderSourceID, oi.ClientUserAccount, oi.OrderStatus, oi.OrderState, oi.IsFirstDeliver, ( CASE WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority ELSE Isnull(os.PriorityNum, 0) END ) AS IsPriority, oi.PackagedIDs, oi.IsASingleInTheEnd, oi.PayerId, oi.Email, oi.SellerOrderId, oi.IsPay, oi.CommentType, oi.TransactionId, oi.PaymentType, Isnull(oi.RefundPrice, 0) AS RefundPrice, Isnull(oi.ThrowingRate, 0) AS ThrowingRate, oi.TotalPrice, oi.ExchangeRMB, oi.ExchangeUSD, oi.TransportPay, oi.OtherPayPrice, oi.ProductTotalPrice, oi.PromotionDiscountAmount, oi.CouponCode, oi.CouponMinusPrice, oi.DiscountMinusPrice, oi.AddPayPrice, oi.AddOrderReasonType, oi.ShareType, oi.ShareCustomerID, oi.IsAccount, oi.AccountTime, oi.ShareTotalPrice, oi.SynchronousTime, oi.IsSynchronous, oi.IsTogeInvoice, Isnull(oi.Encrypted, 0) AS Encrypted, oi.FirstName, oi.LastName, oi.Address, oi.District, oi.CabinetNO, oi.City, oi.Province, oi.Country, oi.PostCode, oi.Telephone, oi.ShippingService, oi.DownLoadUpdateTime, oi.PromotionType, oi.TrackNumberTemp, oi.TrackNumbers, oi.UpdatePlatformTime, oi.MarkDeliveryState, oi.EMPSDeliveryState, oi.LogisticsOrderNo, oi.LogisticsOrderNo1, oi.ShipTime, oi.OrderDescription, oi.ISsynchOverseas, oi.ProductNumber, oi.ProductVolume, oi.ProductWeight, oi.PayTime, oi.AddTime, oi.UpdateTime, oi.LatestShipDate, oi.ServiceAdminName, oi.SKUNumber, oi.LatestDeliveryDate, oi.VipDiscount, Isnull(oi.IsTaxFree, 0) IsTaxFree, oi.Currency, os.ServiceAdminName AS ServiceOSAdminName, Isnull(oi.IsFBATransit, 0) IsFBATransit, oi.WareHouseID, wh.WareHouseName, os.OrderSourceName, os.OrderSourceType, os.OrderSourceCountry, e.Country_cn, t.TransportName, t.PMSTransport, tapi.Transport_API_Class, wh.WareHouseType, oi.Token, os.SaleAdminName, os.VATNumber, os.VATRate, oi.SaveTrackNumbersTime, t.TrackingNumberValidDays, Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay, Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay, bt.BusinessTeamName, t.ID AS TransportID, e.ID AS CountryID, oi.Oaid, api.CertIDAccessKeyId, Isnull(cexpand.BillPdfUrl, '') BillPdfUrl, att1.IsAttachMail AS IsAttachMail FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN RB_OrderSource os WITH(nolock) ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID LEFT JOIN E_Country e WITH(nolock) ON oi.Country = e.Shorthand LEFT JOIN RB_Transport t WITH(nolock) ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID LEFT JOIN RB_Transport_API tapi WITH(nolock) ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID LEFT JOIN RB_WareHouse wh WITH(nolock) ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID LEFT JOIN RB_Admin rba WITH(nolock) ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID LEFT JOIN RB_BusinessTeam bt WITH(nolock) ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock) ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand ON oi.CustomerID = cexpand.CustomerID AND oi.OrderCode = cexpand.OrderCode OUTER apply (SELECT Count(1) IsAttachMail FROM RB_OrderInfo_Attach att(nolock) WHERE att.CustomerID = oi.CustomerID AND att.OrderCode = oi.OrderCode) att1 WHERE oi.CustomerID = 1 AND oi.IsStockOrder = 0 AND oi.IsPay = 1 AND oi.PayTime &gt;= '2023/4/1 0:00:00' AND oi.PayTime &lt;= '2023/4/13 23:59:59' --AND EXISTS (SELECT 1 -- FROM RB_OrderList ol WITH(nolock) -- INNER JOIN RB_Product_Admin pa WITH(nolock) -- ON ol.CustomerID = pa.CustomerID -- AND ol.SKU = pa.SKU -- WHERE ol.CustomerID = 1 -- AND ol.OrderCode = oi.OrderCode -- AND pa.DevelopAdminID = 6323) ) AS TempTable) AS PagerTableWHERE PagerTable.[PageIndex] BETWEEN 1 AND 500
    018- SELECT *FROM (SELECT Row_number() OVER ( ORDER BY PayTime DESC) AS [PageIndex], * FROM (SELECT oi.ID, oi.OrderCode, oi.ClientOrderCode, oi.MainOrderCode, oi.MergeOrderCode, oi.SalesRecordNumber, oi.OrderSourceID, oi.ClientUserAccount, oi.OrderStatus, oi.OrderState, oi.IsFirstDeliver, ( CASE WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority ELSE Isnull(os.PriorityNum, 0) END ) AS IsPriority, oi.PackagedIDs, oi.IsASingleInTheEnd, oi.PayerId, oi.Email, oi.SellerOrderId, oi.IsPay, oi.CommentType, oi.TransactionId, oi.PaymentType, Isnull(oi.RefundPrice, 0) AS RefundPrice, Isnull(oi.ThrowingRate, 0) AS ThrowingRate, oi.TotalPrice, oi.ExchangeRMB, oi.ExchangeUSD, oi.TransportPay, oi.OtherPayPrice, oi.ProductTotalPrice, oi.PromotionDiscountAmount, oi.CouponCode, oi.CouponMinusPrice, oi.DiscountMinusPrice, oi.AddPayPrice, oi.AddOrderReasonType, oi.ShareType, oi.ShareCustomerID, oi.IsAccount, oi.AccountTime, oi.ShareTotalPrice, oi.SynchronousTime, oi.IsSynchronous, oi.IsTogeInvoice, Isnull(oi.Encrypted, 0) AS Encrypted, oi.FirstName, oi.LastName, oi.Address, oi.District, oi.CabinetNO, oi.City, oi.Province, oi.Country, oi.PostCode, oi.Telephone, oi.ShippingService, oi.DownLoadUpdateTime, oi.PromotionType, oi.TrackNumberTemp, oi.TrackNumbers, oi.UpdatePlatformTime, oi.MarkDeliveryState, oi.EMPSDeliveryState, oi.LogisticsOrderNo, oi.LogisticsOrderNo1, oi.ShipTime, oi.OrderDescription, oi.ISsynchOverseas, oi.ProductNumber, oi.ProductVolume, oi.ProductWeight, oi.PayTime, oi.AddTime, oi.UpdateTime, oi.LatestShipDate, oi.ServiceAdminName, oi.SKUNumber, oi.LatestDeliveryDate, oi.VipDiscount, Isnull(oi.IsTaxFree, 0) IsTaxFree, oi.Currency, os.ServiceAdminName AS ServiceOSAdminName, Isnull(oi.IsFBATransit, 0) IsFBATransit, oi.WareHouseID, wh.WareHouseName, os.OrderSourceName, os.OrderSourceType, os.OrderSourceCountry, e.Country_cn, t.TransportName, t.PMSTransport, tapi.Transport_API_Class, wh.WareHouseType, oi.Token, os.SaleAdminName, os.VATNumber, os.VATRate, oi.SaveTrackNumbersTime, t.TrackingNumberValidDays, Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay, Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay, bt.BusinessTeamName, t.ID AS TransportID, e.ID AS CountryID, oi.Oaid, api.CertIDAccessKeyId, Isnull(cexpand.BillPdfUrl, '') BillPdfUrl, att1.IsAttachMail AS IsAttachMail FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN RB_OrderSource os WITH(nolock) ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID LEFT JOIN E_Country e WITH(nolock) ON oi.Country = e.Shorthand LEFT JOIN RB_Transport t WITH(nolock) ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID LEFT JOIN RB_Transport_API tapi WITH(nolock) ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID LEFT JOIN RB_WareHouse wh WITH(nolock) ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID LEFT JOIN RB_Admin rba WITH(nolock) ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID LEFT JOIN RB_BusinessTeam bt WITH(nolock) ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock) ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand ON oi.CustomerID = cexpand.CustomerID AND oi.OrderCode = cexpand.OrderCode OUTER apply (SELECT Count(1) IsAttachMail FROM RB_OrderInfo_Attach att(nolock) WHERE att.CustomerID = oi.CustomerID AND att.OrderCode = oi.OrderCode) att1 WHERE oi.CustomerID = 1 AND oi.IsStockOrder = 0 AND oi.IsPay = 1 AND oi.PayTime &gt;= '2023/4/1 0:00:00' AND oi.PayTime &lt;= '2023/4/13 23:59:59' AND EXISTS (SELECT 1 FROM RB_OrderList ol WITH(nolock) INNER JOIN RB_Product_Admin pa WITH(nolock) ON ol.CustomerID = pa.CustomerID AND ol.SKU = pa.SKU WHERE ol.CustomerID = 1 AND ol.OrderCode = oi.OrderCode AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTableWHERE PagerTable.[PageIndex] BETWEEN 1 AND 500OPTION(recompile, maxdop 12)
    018+ SELECT *FROM (SELECT Row_number() OVER ( ORDER BY PayTime DESC) AS [PageIndex], * FROM (SELECT oi.ID, oi.OrderCode, oi.ClientOrderCode, oi.MainOrderCode, oi.MergeOrderCode, oi.SalesRecordNumber, oi.OrderSourceID, oi.ClientUserAccount, oi.OrderStatus, oi.OrderState, oi.IsFirstDeliver, ( CASE WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority ELSE Isnull(os.PriorityNum, 0) END ) AS IsPriority, oi.PackagedIDs, oi.IsASingleInTheEnd, oi.PayerId, oi.Email, oi.SellerOrderId, oi.IsPay, oi.CommentType, oi.TransactionId, oi.PaymentType, Isnull(oi.RefundPrice, 0) AS RefundPrice, Isnull(oi.ThrowingRate, 0) AS ThrowingRate, oi.TotalPrice, oi.ExchangeRMB, oi.ExchangeUSD, oi.TransportPay, oi.OtherPayPrice, oi.ProductTotalPrice, oi.PromotionDiscountAmount, oi.CouponCode, oi.CouponMinusPrice, oi.DiscountMinusPrice, oi.AddPayPrice, oi.AddOrderReasonType, oi.ShareType, oi.ShareCustomerID, oi.IsAccount, oi.AccountTime, oi.ShareTotalPrice, oi.SynchronousTime, oi.IsSynchronous, oi.IsTogeInvoice, Isnull(oi.Encrypted, 0) AS Encrypted, oi.FirstName, oi.LastName, oi.Address, oi.District, oi.CabinetNO, oi.City, oi.Province, oi.Country, oi.PostCode, oi.Telephone, oi.ShippingService, oi.DownLoadUpdateTime, oi.PromotionType, oi.TrackNumberTemp, oi.TrackNumbers, oi.UpdatePlatformTime, oi.MarkDeliveryState, oi.EMPSDeliveryState, oi.LogisticsOrderNo, oi.LogisticsOrderNo1, oi.ShipTime, oi.OrderDescription, oi.ISsynchOverseas, oi.ProductNumber, oi.ProductVolume, oi.ProductWeight, oi.PayTime, oi.AddTime, oi.UpdateTime, oi.LatestShipDate, oi.ServiceAdminName, oi.SKUNumber, oi.LatestDeliveryDate, oi.VipDiscount, Isnull(oi.IsTaxFree, 0) IsTaxFree, oi.Currency, os.ServiceAdminName AS ServiceOSAdminName, Isnull(oi.IsFBATransit, 0) IsFBATransit, oi.WareHouseID, wh.WareHouseName, os.OrderSourceName, os.OrderSourceType, os.OrderSourceCountry, e.Country_cn, t.TransportName, t.PMSTransport, tapi.Transport_API_Class, wh.WareHouseType, oi.Token, os.SaleAdminName, os.VATNumber, os.VATRate, oi.SaveTrackNumbersTime, t.TrackingNumberValidDays, Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay, Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay, bt.BusinessTeamName, t.ID AS TransportID, e.ID AS CountryID, oi.Oaid, api.CertIDAccessKeyId, Isnull(cexpand.BillPdfUrl, '') BillPdfUrl, att1.IsAttachMail AS IsAttachMail FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN RB_OrderSource os WITH(nolock) ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID LEFT JOIN E_Country e WITH(nolock) ON oi.Country = e.Shorthand LEFT JOIN RB_Transport t WITH(nolock) ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID LEFT JOIN RB_Transport_API tapi WITH(nolock) ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID LEFT JOIN RB_WareHouse wh WITH(nolock) ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID LEFT JOIN RB_Admin rba WITH(nolock) ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID LEFT JOIN RB_BusinessTeam bt WITH(nolock) ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock) ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand ON oi.CustomerID = cexpand.CustomerID AND oi.OrderCode = cexpand.OrderCode OUTER apply (SELECT Count(1) IsAttachMail FROM RB_OrderInfo_Attach att(nolock) WHERE att.CustomerID = oi.CustomerID AND att.OrderCode = oi.OrderCode) att1 WHERE oi.CustomerID = 1 AND oi.IsStockOrder = 0 AND oi.IsPay = 1 AND oi.PayTime &gt;= '2023/4/1 0:00:00' AND oi.PayTime &lt;= '2023/4/13 23:59:59' AND EXISTS (SELECT 1 FROM RB_OrderList ol WITH(nolock) INNER JOIN RB_Product_Admin pa WITH(nolock) ON ol.CustomerID = pa.CustomerID AND ol.SKU = pa.SKU WHERE ol.CustomerID = 1 AND ol.OrderCode = oi.OrderCode AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTableWHERE PagerTable.[PageIndex] BETWEEN 1 AND 500OPTION(recompile, maxdop 12)
    005- <p>select count(0) from RB_OrderInfo nolock -- 46116303<br />select count(0) from RB_OrderList nolock -- 59512660<br /></p>
    005+ <p>select count(0) from RB_OrderInfo nolock -- 46116303<br />select count(0) from RB_OrderList nolock -- 59512660</p>
    013- <p>SELECT *<br />FROM (SELECT Row_number()<br /> OVER (<br /> ORDER BY PayTime DESC) AS [PageIndex],<br /> *<br /> FROM (SELECT oi.ID,<br /> oi.OrderCode,<br /> oi.ClientOrderCode,<br /> oi.MainOrderCode,<br /> oi.MergeOrderCode,<br /> oi.SalesRecordNumber,<br /> oi.OrderSourceID,<br /> oi.ClientUserAccount,<br /> oi.OrderStatus,<br /> oi.OrderState,<br /> oi.IsFirstDeliver,<br /> ( CASE<br /> WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority<br /> ELSE Isnull(os.PriorityNum, 0)<br /> END ) AS IsPriority,<br /> oi.PackagedIDs,<br /> oi.IsASingleInTheEnd,<br /> oi.PayerId,<br /> oi.Email,<br /> oi.SellerOrderId,<br /> oi.IsPay,<br /> oi.CommentType,<br /> oi.TransactionId,<br /> oi.PaymentType,<br /> Isnull(oi.RefundPrice, 0) AS RefundPrice,<br /> Isnull(oi.ThrowingRate, 0) AS ThrowingRate,<br /> oi.TotalPrice,<br /> oi.ExchangeRMB,<br /> oi.ExchangeUSD,<br /> oi.TransportPay,<br /> oi.OtherPayPrice,<br /> oi.ProductTotalPrice,<br /> oi.PromotionDiscountAmount,<br /> oi.CouponCode,<br /> oi.CouponMinusPrice,<br /> oi.DiscountMinusPrice,<br /> oi.AddPayPrice,<br /> oi.AddOrderReasonType,<br /> oi.ShareType,<br /> oi.ShareCustomerID,<br /> oi.IsAccount,<br /> oi.AccountTime,<br /> oi.ShareTotalPrice,<br /> oi.SynchronousTime,<br /> oi.IsSynchronous,<br /> oi.IsTogeInvoice,<br /> Isnull(oi.Encrypted, 0) AS Encrypted,<br /> oi.FirstName,<br /> oi.LastName,<br /> oi.Address,<br /> oi.District,<br /> oi.CabinetNO,<br /> oi.City,<br /> oi.Province,<br /> oi.Country,<br /> oi.PostCode,<br /> oi.Telephone,<br /> oi.ShippingService,<br /> oi.DownLoadUpdateTime,<br /> oi.PromotionType,<br /> oi.TrackNumberTemp,<br /> oi.TrackNumbers,<br /> oi.UpdatePlatformTime,<br /> oi.MarkDeliveryState,<br /> oi.EMPSDeliveryState,<br /> oi.LogisticsOrderNo,<br /> oi.LogisticsOrderNo1,<br /> oi.ShipTime,<br /> oi.OrderDescription,<br /> oi.ISsynchOverseas,<br /> oi.ProductNumber,<br /> oi.ProductVolume,<br /> oi.ProductWeight,<br /> oi.PayTime,<br /> oi.AddTime,<br /> oi.UpdateTime,<br /> oi.LatestShipDate,<br /> oi.ServiceAdminName,<br /> oi.SKUNumber,<br /> oi.LatestDeliveryDate,<br /> oi.VipDiscount,<br /> Isnull(oi.IsTaxFree, 0) IsTaxFree,<br /> oi.Currency,<br /> os.ServiceAdminName AS ServiceOSAdminName,<br /> Isnull(oi.IsFBATransit, 0) IsFBATransit,<br /> oi.WareHouseID,<br /> wh.WareHouseName,<br /> os.OrderSourceName,<br /> os.OrderSourceType,<br /> os.OrderSourceCountry,<br /> e.Country_cn,<br /> t.TransportName,<br /> t.PMSTransport,<br /> tapi.Transport_API_Class,<br /> wh.WareHouseType,<br /> oi.Token,<br /> os.SaleAdminName,<br /> os.VATNumber,<br /> os.VATRate,<br /> oi.SaveTrackNumbersTime,<br /> t.TrackingNumberValidDays,<br /> Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay,<br /> Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,<br /> bt.BusinessTeamName,<br /> t.ID AS TransportID,<br /> e.ID AS CountryID,<br /> oi.Oaid,<br /> api.CertIDAccessKeyId,<br /> Isnull(cexpand.BillPdfUrl, '') BillPdfUrl,<br /> att1.IsAttachMail AS IsAttachMail<br /> FROM RB_OrderInfo oi WITH(nolock)<br /> LEFT JOIN RB_OrderSource os WITH(nolock)<br /> ON oi.CustomerID = os.CustomerID<br /> AND oi.OrderSourceID = os.ID<br /> LEFT JOIN E_Country e WITH(nolock)<br /> ON oi.Country = e.Shorthand<br /> LEFT JOIN RB_Transport t WITH(nolock)<br /> ON oi.CustomerID = t.CustomerID<br /> AND oi.TransportID = t.ID<br /> LEFT JOIN RB_Transport_API tapi WITH(nolock)<br /> ON t.Transport_API_ID = tapi.ID<br /> AND t.CustomerID = tapi.CustomerID<br /> LEFT JOIN RB_WareHouse wh WITH(nolock)<br /> ON oi.CustomerID = wh.CustomerID<br /> AND oi.WareHouseID = wh.ID<br /> LEFT JOIN RB_Admin rba WITH(nolock)<br /> ON rba.CustomerID = os.CustomerID<br /> AND rba.ID = os.SaleAdminID<br /> LEFT JOIN RB_BusinessTeam bt WITH(nolock)<br /> ON bt.CustomerID = os.CustomerID<br /> AND bt.ID = rba.BusinessTeamID<br /> LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)<br /> ON os.CustomerID = api.CustomerID<br /> AND os.ID = api.OrderSourceID<br /> LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand<br /> ON oi.CustomerID = cexpand.CustomerID<br /> AND oi.OrderCode = cexpand.OrderCode<br /> OUTER apply (SELECT Count(1) IsAttachMail<br /> FROM RB_OrderInfo_Attach att(nolock)<br /> WHERE att.CustomerID = oi.CustomerID<br /> AND att.OrderCode = oi.OrderCode) att1<br /> WHERE oi.CustomerID = 1<br /> AND oi.IsStockOrder = 0<br /> AND oi.IsPay = 1<br /> AND oi.PayTime &gt;= '2023/4/1 0:00:00'<br /> AND oi.PayTime &lt;= '2023/4/13 23:59:59'<br /> --AND EXISTS (SELECT 1<br /> -- FROM RB_OrderList ol WITH(nolock)<br /> -- INNER JOIN RB_Product_Admin pa WITH(nolock)<br /> -- ON ol.CustomerID = pa.CustomerID<br /> -- AND ol.SKU = pa.SKU<br /> -- WHERE ol.CustomerID = 1<br /> -- AND ol.OrderCode = oi.OrderCode<br /> -- AND pa.DevelopAdminID = 6323)<br /><span> </span> ) AS TempTable) AS PagerTable<br />WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500<br /></p>
    013+ <p>SELECT *<br />FROM (SELECT Row_number()<br /> OVER (<br /> ORDER BY PayTime DESC) AS [PageIndex],<br /> *<br /> FROM (SELECT oi.ID,<br /> oi.OrderCode,<br /> oi.ClientOrderCode,<br /> oi.MainOrderCode,<br /> oi.MergeOrderCode,<br /> oi.SalesRecordNumber,<br /> oi.OrderSourceID,<br /> oi.ClientUserAccount,<br /> oi.OrderStatus,<br /> oi.OrderState,<br /> oi.IsFirstDeliver,<br /> ( CASE<br /> WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority<br /> ELSE Isnull(os.PriorityNum, 0)<br /> END ) AS IsPriority,<br /> oi.PackagedIDs,<br /> oi.IsASingleInTheEnd,<br /> oi.PayerId,<br /> oi.Email,<br /> oi.SellerOrderId,<br /> oi.IsPay,<br /> oi.CommentType,<br /> oi.TransactionId,<br /> oi.PaymentType,<br /> Isnull(oi.RefundPrice, 0) AS RefundPrice,<br /> Isnull(oi.ThrowingRate, 0) AS ThrowingRate,<br /> oi.TotalPrice,<br /> oi.ExchangeRMB,<br /> oi.ExchangeUSD,<br /> oi.TransportPay,<br /> oi.OtherPayPrice,<br /> oi.ProductTotalPrice,<br /> oi.PromotionDiscountAmount,<br /> oi.CouponCode,<br /> oi.CouponMinusPrice,<br /> oi.DiscountMinusPrice,<br /> oi.AddPayPrice,<br /> oi.AddOrderReasonType,<br /> oi.ShareType,<br /> oi.ShareCustomerID,<br /> oi.IsAccount,<br /> oi.AccountTime,<br /> oi.ShareTotalPrice,<br /> oi.SynchronousTime,<br /> oi.IsSynchronous,<br /> oi.IsTogeInvoice,<br /> Isnull(oi.Encrypted, 0) AS Encrypted,<br /> oi.FirstName,<br /> oi.LastName,<br /> oi.Address,<br /> oi.District,<br /> oi.CabinetNO,<br /> oi.City,<br /> oi.Province,<br /> oi.Country,<br /> oi.PostCode,<br /> oi.Telephone,<br /> oi.ShippingService,<br /> oi.DownLoadUpdateTime,<br /> oi.PromotionType,<br /> oi.TrackNumberTemp,<br /> oi.TrackNumbers,<br /> oi.UpdatePlatformTime,<br /> oi.MarkDeliveryState,<br /> oi.EMPSDeliveryState,<br /> oi.LogisticsOrderNo,<br /> oi.LogisticsOrderNo1,<br /> oi.ShipTime,<br /> oi.OrderDescription,<br /> oi.ISsynchOverseas,<br /> oi.ProductNumber,<br /> oi.ProductVolume,<br /> oi.ProductWeight,<br /> oi.PayTime,<br /> oi.AddTime,<br /> oi.UpdateTime,<br /> oi.LatestShipDate,<br /> oi.ServiceAdminName,<br /> oi.SKUNumber,<br /> oi.LatestDeliveryDate,<br /> oi.VipDiscount,<br /> Isnull(oi.IsTaxFree, 0) IsTaxFree,<br /> oi.Currency,<br /> os.ServiceAdminName AS ServiceOSAdminName,<br /> Isnull(oi.IsFBATransit, 0) IsFBATransit,<br /> oi.WareHouseID,<br /> wh.WareHouseName,<br /> os.OrderSourceName,<br /> os.OrderSourceType,<br /> os.OrderSourceCountry,<br /> e.Country_cn,<br /> t.TransportName,<br /> t.PMSTransport,<br /> tapi.Transport_API_Class,<br /> wh.WareHouseType,<br /> oi.Token,<br /> os.SaleAdminName,<br /> os.VATNumber,<br /> os.VATRate,<br /> oi.SaveTrackNumbersTime,<br /> t.TrackingNumberValidDays,<br /> Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay,<br /> Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,<br /> bt.BusinessTeamName,<br /> t.ID AS TransportID,<br /> e.ID AS CountryID,<br /> oi.Oaid,<br /> api.CertIDAccessKeyId,<br /> Isnull(cexpand.BillPdfUrl, '') BillPdfUrl,<br /> att1.IsAttachMail AS IsAttachMail<br /> FROM RB_OrderInfo oi WITH(nolock)<br /> LEFT JOIN RB_OrderSource os WITH(nolock)<br /> ON oi.CustomerID = os.CustomerID<br /> AND oi.OrderSourceID = os.ID<br /> LEFT JOIN E_Country e WITH(nolock)<br /> ON oi.Country = e.Shorthand<br /> LEFT JOIN RB_Transport t WITH(nolock)<br /> ON oi.CustomerID = t.CustomerID<br /> AND oi.TransportID = t.ID<br /> LEFT JOIN RB_Transport_API tapi WITH(nolock)<br /> ON t.Transport_API_ID = tapi.ID<br /> AND t.CustomerID = tapi.CustomerID<br /> LEFT JOIN RB_WareHouse wh WITH(nolock)<br /> ON oi.CustomerID = wh.CustomerID<br /> AND oi.WareHouseID = wh.ID<br /> LEFT JOIN RB_Admin rba WITH(nolock)<br /> ON rba.CustomerID = os.CustomerID<br /> AND rba.ID = os.SaleAdminID<br /> LEFT JOIN RB_BusinessTeam bt WITH(nolock)<br /> ON bt.CustomerID = os.CustomerID<br /> AND bt.ID = rba.BusinessTeamID<br /> LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)<br /> ON os.CustomerID = api.CustomerID<br /> AND os.ID = api.OrderSourceID<br /> LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand<br /> ON oi.CustomerID = cexpand.CustomerID<br /> AND oi.OrderCode = cexpand.OrderCode<br /> OUTER apply (SELECT Count(1) IsAttachMail<br /> FROM RB_OrderInfo_Attach att(nolock)<br /> WHERE att.CustomerID = oi.CustomerID<br /> AND att.OrderCode = oi.OrderCode) att1<br /> WHERE oi.CustomerID = 1<br /> AND oi.IsStockOrder = 0<br /> AND oi.IsPay = 1<br /> AND oi.PayTime &gt;= '2023/4/1 0:00:00'<br /> AND oi.PayTime &lt;= '2023/4/13 23:59:59'<br /> --AND EXISTS (SELECT 1<br /> -- FROM RB_OrderList ol WITH(nolock)<br /> -- INNER JOIN RB_Product_Admin pa WITH(nolock)<br /> -- ON ol.CustomerID = pa.CustomerID<br /> -- AND ol.SKU = pa.SKU<br /> -- WHERE ol.CustomerID = 1<br /> -- AND ol.OrderCode = oi.OrderCode<br /> -- AND pa.DevelopAdminID = 6323)<br /><span> </span> ) AS TempTable) AS PagerTable<br />WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500</p>
    018- <p><br />SELECT *<br />FROM (SELECT Row_number()<br /> OVER (<br /> ORDER BY PayTime DESC) AS [PageIndex],<br /> *<br /> FROM (SELECT oi.ID,<br /> oi.OrderCode,<br /> oi.ClientOrderCode,<br /> oi.MainOrderCode,<br /> oi.MergeOrderCode,<br /> oi.SalesRecordNumber,<br /> oi.OrderSourceID,<br /> oi.ClientUserAccount,<br /> oi.OrderStatus,<br /> oi.OrderState,<br /> oi.IsFirstDeliver,<br /> ( CASE<br /> WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority<br /> ELSE Isnull(os.PriorityNum, 0)<br /> END ) AS IsPriority,<br /> oi.PackagedIDs,<br /> oi.IsASingleInTheEnd,<br /> oi.PayerId,<br /> oi.Email,<br /> oi.SellerOrderId,<br /> oi.IsPay,<br /> oi.CommentType,<br /> oi.TransactionId,<br /> oi.PaymentType,<br /> Isnull(oi.RefundPrice, 0) AS RefundPrice,<br /> Isnull(oi.ThrowingRate, 0) AS ThrowingRate,<br /> oi.TotalPrice,<br /> oi.ExchangeRMB,<br /> oi.ExchangeUSD,<br /> oi.TransportPay,<br /> oi.OtherPayPrice,<br /> oi.ProductTotalPrice,<br /> oi.PromotionDiscountAmount,<br /> oi.CouponCode,<br /> oi.CouponMinusPrice,<br /> oi.DiscountMinusPrice,<br /> oi.AddPayPrice,<br /> oi.AddOrderReasonType,<br /> oi.ShareType,<br /> oi.ShareCustomerID,<br /> oi.IsAccount,<br /> oi.AccountTime,<br /> oi.ShareTotalPrice,<br /> oi.SynchronousTime,<br /> oi.IsSynchronous,<br /> oi.IsTogeInvoice,<br /> Isnull(oi.Encrypted, 0) AS Encrypted,<br /> oi.FirstName,<br /> oi.LastName,<br /> oi.Address,<br /> oi.District,<br /> oi.CabinetNO,<br /> oi.City,<br /> oi.Province,<br /> oi.Country,<br /> oi.PostCode,<br /> oi.Telephone,<br /> oi.ShippingService,<br /> oi.DownLoadUpdateTime,<br /> oi.PromotionType,<br /> oi.TrackNumberTemp,<br /> oi.TrackNumbers,<br /> oi.UpdatePlatformTime,<br /> oi.MarkDeliveryState,<br /> oi.EMPSDeliveryState,<br /> oi.LogisticsOrderNo,<br /> oi.LogisticsOrderNo1,<br /> oi.ShipTime,<br /> oi.OrderDescription,<br /> oi.ISsynchOverseas,<br /> oi.ProductNumber,<br /> oi.ProductVolume,<br /> oi.ProductWeight,<br /> oi.PayTime,<br /> oi.AddTime,<br /> oi.UpdateTime,<br /> oi.LatestShipDate,<br /> oi.ServiceAdminName,<br /> oi.SKUNumber,<br /> oi.LatestDeliveryDate,<br /> oi.VipDiscount,<br /> Isnull(oi.IsTaxFree, 0) IsTaxFree,<br /> oi.Currency,<br /> os.ServiceAdminName AS ServiceOSAdminName,<br /> Isnull(oi.IsFBATransit, 0) IsFBATransit,<br /> oi.WareHouseID,<br /> wh.WareHouseName,<br /> os.OrderSourceName,<br /> os.OrderSourceType,<br /> os.OrderSourceCountry,<br /> e.Country_cn,<br /> t.TransportName,<br /> t.PMSTransport,<br /> tapi.Transport_API_Class,<br /> wh.WareHouseType,<br /> oi.Token,<br /> os.SaleAdminName,<br /> os.VATNumber,<br /> os.VATRate,<br /> oi.SaveTrackNumbersTime,<br /> t.TrackingNumberValidDays,<br /> Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay,<br /> Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,<br /> bt.BusinessTeamName,<br /> t.ID AS TransportID,<br /> e.ID AS CountryID,<br /> oi.Oaid,<br /> api.CertIDAccessKeyId,<br /> Isnull(cexpand.BillPdfUrl, '') BillPdfUrl,<br /> att1.IsAttachMail AS IsAttachMail<br /> FROM RB_OrderInfo oi WITH(nolock)<br /> LEFT JOIN RB_OrderSource os WITH(nolock)<br /> ON oi.CustomerID = os.CustomerID<br /> AND oi.OrderSourceID = os.ID<br /> LEFT JOIN E_Country e WITH(nolock)<br /> ON oi.Country = e.Shorthand<br /> LEFT JOIN RB_Transport t WITH(nolock)<br /> ON oi.CustomerID = t.CustomerID<br /> AND oi.TransportID = t.ID<br /> LEFT JOIN RB_Transport_API tapi WITH(nolock)<br /> ON t.Transport_API_ID = tapi.ID<br /> AND t.CustomerID = tapi.CustomerID<br /> LEFT JOIN RB_WareHouse wh WITH(nolock)<br /> ON oi.CustomerID = wh.CustomerID<br /> AND oi.WareHouseID = wh.ID<br /> LEFT JOIN RB_Admin rba WITH(nolock)<br /> ON rba.CustomerID = os.CustomerID<br /> AND rba.ID = os.SaleAdminID<br /> LEFT JOIN RB_BusinessTeam bt WITH(nolock)<br /> ON bt.CustomerID = os.CustomerID<br /> AND bt.ID = rba.BusinessTeamID<br /> LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)<br /> ON os.CustomerID = api.CustomerID<br /> AND os.ID = api.OrderSourceID<br /> LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand<br /> ON oi.CustomerID = cexpand.CustomerID<br /> AND oi.OrderCode = cexpand.OrderCode<br /> OUTER apply (SELECT Count(1) IsAttachMail<br /> FROM RB_OrderInfo_Attach att(nolock)<br /> WHERE att.CustomerID = oi.CustomerID<br /> AND att.OrderCode = oi.OrderCode) att1<br /> WHERE oi.CustomerID = 1<br /> AND oi.IsStockOrder = 0<br /> AND oi.IsPay = 1<br /> AND oi.PayTime &gt;= '2023/4/1 0:00:00'<br /> AND oi.PayTime &lt;= '2023/4/13 23:59:59'<br /> AND EXISTS (SELECT 1<br /> FROM RB_OrderList ol WITH(nolock)<br /> INNER JOIN RB_Product_Admin pa WITH(nolock)<br /> ON ol.CustomerID = pa.CustomerID<br /> AND ol.SKU = pa.SKU<br /> WHERE ol.CustomerID = 1<br /> AND ol.OrderCode = oi.OrderCode<br /> AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTable<br />WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500<br />OPTION(recompile, maxdop 12)<br /><br /><br /></p>
    018+ <p><br />SELECT *<br />FROM (SELECT Row_number()<br /> OVER (<br /> ORDER BY PayTime DESC) AS [PageIndex],<br /> *<br /> FROM (SELECT oi.ID,<br /> oi.OrderCode,<br /> oi.ClientOrderCode,<br /> oi.MainOrderCode,<br /> oi.MergeOrderCode,<br /> oi.SalesRecordNumber,<br /> oi.OrderSourceID,<br /> oi.ClientUserAccount,<br /> oi.OrderStatus,<br /> oi.OrderState,<br /> oi.IsFirstDeliver,<br /> ( CASE<br /> WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority<br /> ELSE Isnull(os.PriorityNum, 0)<br /> END ) AS IsPriority,<br /> oi.PackagedIDs,<br /> oi.IsASingleInTheEnd,<br /> oi.PayerId,<br /> oi.Email,<br /> oi.SellerOrderId,<br /> oi.IsPay,<br /> oi.CommentType,<br /> oi.TransactionId,<br /> oi.PaymentType,<br /> Isnull(oi.RefundPrice, 0) AS RefundPrice,<br /> Isnull(oi.ThrowingRate, 0) AS ThrowingRate,<br /> oi.TotalPrice,<br /> oi.ExchangeRMB,<br /> oi.ExchangeUSD,<br /> oi.TransportPay,<br /> oi.OtherPayPrice,<br /> oi.ProductTotalPrice,<br /> oi.PromotionDiscountAmount,<br /> oi.CouponCode,<br /> oi.CouponMinusPrice,<br /> oi.DiscountMinusPrice,<br /> oi.AddPayPrice,<br /> oi.AddOrderReasonType,<br /> oi.ShareType,<br /> oi.ShareCustomerID,<br /> oi.IsAccount,<br /> oi.AccountTime,<br /> oi.ShareTotalPrice,<br /> oi.SynchronousTime,<br /> oi.IsSynchronous,<br /> oi.IsTogeInvoice,<br /> Isnull(oi.Encrypted, 0) AS Encrypted,<br /> oi.FirstName,<br /> oi.LastName,<br /> oi.Address,<br /> oi.District,<br /> oi.CabinetNO,<br /> oi.City,<br /> oi.Province,<br /> oi.Country,<br /> oi.PostCode,<br /> oi.Telephone,<br /> oi.ShippingService,<br /> oi.DownLoadUpdateTime,<br /> oi.PromotionType,<br /> oi.TrackNumberTemp,<br /> oi.TrackNumbers,<br /> oi.UpdatePlatformTime,<br /> oi.MarkDeliveryState,<br /> oi.EMPSDeliveryState,<br /> oi.LogisticsOrderNo,<br /> oi.LogisticsOrderNo1,<br /> oi.ShipTime,<br /> oi.OrderDescription,<br /> oi.ISsynchOverseas,<br /> oi.ProductNumber,<br /> oi.ProductVolume,<br /> oi.ProductWeight,<br /> oi.PayTime,<br /> oi.AddTime,<br /> oi.UpdateTime,<br /> oi.LatestShipDate,<br /> oi.ServiceAdminName,<br /> oi.SKUNumber,<br /> oi.LatestDeliveryDate,<br /> oi.VipDiscount,<br /> Isnull(oi.IsTaxFree, 0) IsTaxFree,<br /> oi.Currency,<br /> os.ServiceAdminName AS ServiceOSAdminName,<br /> Isnull(oi.IsFBATransit, 0) IsFBATransit,<br /> oi.WareHouseID,<br /> wh.WareHouseName,<br /> os.OrderSourceName,<br /> os.OrderSourceType,<br /> os.OrderSourceCountry,<br /> e.Country_cn,<br /> t.TransportName,<br /> t.PMSTransport,<br /> tapi.Transport_API_Class,<br /> wh.WareHouseType,<br /> oi.Token,<br /> os.SaleAdminName,<br /> os.VATNumber,<br /> os.VATRate,<br /> oi.SaveTrackNumbersTime,<br /> t.TrackingNumberValidDays,<br /> Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay,<br /> Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,<br /> bt.BusinessTeamName,<br /> t.ID AS TransportID,<br /> e.ID AS CountryID,<br /> oi.Oaid,<br /> api.CertIDAccessKeyId,<br /> Isnull(cexpand.BillPdfUrl, '') BillPdfUrl,<br /> att1.IsAttachMail AS IsAttachMail<br /> FROM RB_OrderInfo oi WITH(nolock)<br /> LEFT JOIN RB_OrderSource os WITH(nolock)<br /> ON oi.CustomerID = os.CustomerID<br /> AND oi.OrderSourceID = os.ID<br /> LEFT JOIN E_Country e WITH(nolock)<br /> ON oi.Country = e.Shorthand<br /> LEFT JOIN RB_Transport t WITH(nolock)<br /> ON oi.CustomerID = t.CustomerID<br /> AND oi.TransportID = t.ID<br /> LEFT JOIN RB_Transport_API tapi WITH(nolock)<br /> ON t.Transport_API_ID = tapi.ID<br /> AND t.CustomerID = tapi.CustomerID<br /> LEFT JOIN RB_WareHouse wh WITH(nolock)<br /> ON oi.CustomerID = wh.CustomerID<br /> AND oi.WareHouseID = wh.ID<br /> LEFT JOIN RB_Admin rba WITH(nolock)<br /> ON rba.CustomerID = os.CustomerID<br /> AND rba.ID = os.SaleAdminID<br /> LEFT JOIN RB_BusinessTeam bt WITH(nolock)<br /> ON bt.CustomerID = os.CustomerID<br /> AND bt.ID = rba.BusinessTeamID<br /> LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)<br /> ON os.CustomerID = api.CustomerID<br /> AND os.ID = api.OrderSourceID<br /> LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand<br /> ON oi.CustomerID = cexpand.CustomerID<br /> AND oi.OrderCode = cexpand.OrderCode<br /> OUTER apply (SELECT Count(1) IsAttachMail<br /> FROM RB_OrderInfo_Attach att(nolock)<br /> WHERE att.CustomerID = oi.CustomerID<br /> AND att.OrderCode = oi.OrderCode) att1<br /> WHERE oi.CustomerID = 1<br /> AND oi.IsStockOrder = 0<br /> AND oi.IsPay = 1<br /> AND oi.PayTime &gt;= '2023/4/1 0:00:00'<br /> AND oi.PayTime &lt;= '2023/4/13 23:59:59'<br /> AND EXISTS (SELECT 1<br /> FROM RB_OrderList ol WITH(nolock)<br /> INNER JOIN RB_Product_Admin pa WITH(nolock)<br /> ON ol.CustomerID = pa.CustomerID<br /> AND ol.SKU = pa.SKU<br /> WHERE ol.CustomerID = 1<br /> AND ol.OrderCode = oi.OrderCode<br /> AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTable<br />WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500<br />OPTION(recompile, maxdop 12)<br /></p>
  5. 2023-04-14 16:23:54, 由 陈少军 编辑。
    修改了 截止日期，旧值为 "2023-04-17"，新值为 "2023-04-21"。
    修改了 任务描述，区别为：
    018- SELECT *FROM (SELECT Row_number() OVER ( ORDER BY PayTime DESC) AS [PageIndex], * FROM (SELECT oi.ID, oi.OrderCode, oi.ClientOrderCode, oi.MainOrderCode, oi.MergeOrderCode, oi.SalesRecordNumber, oi.OrderSourceID, oi.ClientUserAccount, oi.OrderStatus, oi.OrderState, oi.IsFirstDeliver, ( CASE WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority ELSE Isnull(os.PriorityNum, 0) END ) AS IsPriority, oi.PackagedIDs, oi.IsASingleInTheEnd, oi.PayerId, oi.Email, oi.SellerOrderId, oi.IsPay, oi.CommentType, oi.TransactionId, oi.PaymentType, Isnull(oi.RefundPrice, 0) AS RefundPrice, Isnull(oi.ThrowingRate, 0) AS ThrowingRate, oi.TotalPrice, oi.ExchangeRMB, oi.ExchangeUSD, oi.TransportPay, oi.OtherPayPrice, oi.ProductTotalPrice, oi.PromotionDiscountAmount, oi.CouponCode, oi.CouponMinusPrice, oi.DiscountMinusPrice, oi.AddPayPrice, oi.AddOrderReasonType, oi.ShareType, oi.ShareCustomerID, oi.IsAccount, oi.AccountTime, oi.ShareTotalPrice, oi.SynchronousTime, oi.IsSynchronous, oi.IsTogeInvoice, Isnull(oi.Encrypted, 0) AS Encrypted, oi.FirstName, oi.LastName, oi.Address, oi.District, oi.CabinetNO, oi.City, oi.Province, oi.Country, oi.PostCode, oi.Telephone, oi.ShippingService, oi.DownLoadUpdateTime, oi.PromotionType, oi.TrackNumberTemp, oi.TrackNumbers, oi.UpdatePlatformTime, oi.MarkDeliveryState, oi.EMPSDeliveryState, oi.LogisticsOrderNo, oi.LogisticsOrderNo1, oi.ShipTime, oi.OrderDescription, oi.ISsynchOverseas, oi.ProductNumber, oi.ProductVolume, oi.ProductWeight, oi.PayTime, oi.AddTime, oi.UpdateTime, oi.LatestShipDate, oi.ServiceAdminName, oi.SKUNumber, oi.LatestDeliveryDate, oi.VipDiscount, Isnull(oi.IsTaxFree, 0) IsTaxFree, oi.Currency, os.ServiceAdminName AS ServiceOSAdminName, Isnull(oi.IsFBATransit, 0) IsFBATransit, oi.WareHouseID, wh.WareHouseName, os.OrderSourceName, os.OrderSourceType, os.OrderSourceCountry, e.Country_cn, t.TransportName, t.PMSTransport, tapi.Transport_API_Class, wh.WareHouseType, oi.Token, os.SaleAdminName, os.VATNumber, os.VATRate, oi.SaveTrackNumbersTime, t.TrackingNumberValidDays, Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay, Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay, bt.BusinessTeamName, t.ID AS TransportID, e.ID AS CountryID, oi.Oaid, api.CertIDAccessKeyId, Isnull(cexpand.BillPdfUrl, '') BillPdfUrl, att1.IsAttachMail AS IsAttachMail FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN RB_OrderSource os WITH(nolock) ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID LEFT JOIN E_Country e WITH(nolock) ON oi.Country = e.Shorthand LEFT JOIN RB_Transport t WITH(nolock) ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID LEFT JOIN RB_Transport_API tapi WITH(nolock) ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID LEFT JOIN RB_WareHouse wh WITH(nolock) ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID LEFT JOIN RB_Admin rba WITH(nolock) ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID LEFT JOIN RB_BusinessTeam bt WITH(nolock) ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock) ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand ON oi.CustomerID = cexpand.CustomerID AND oi.OrderCode = cexpand.OrderCode OUTER apply (SELECT Count(1) IsAttachMail FROM RB_OrderInfo_Attach att(nolock) WHERE att.CustomerID = oi.CustomerID AND att.OrderCode = oi.OrderCode) att1 WHERE oi.CustomerID = 1 AND oi.IsStockOrder = 0 AND oi.IsPay = 1 AND oi.PayTime &gt;= '2023/4/1 0:00:00' AND oi.PayTime &lt;= '2023/4/13 23:59:59' AND EXISTS (SELECT 1 FROM RB_OrderList ol WITH(nolock) INNER JOIN RB_Product_Admin pa WITH(nolock) ON ol.CustomerID = pa.CustomerID AND ol.SKU = pa.SKU WHERE ol.CustomerID = 1 AND ol.OrderCode = oi.OrderCode AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTableWHERE PagerTable.[PageIndex] BETWEEN 1 AND 500OPTION(recompile, maxdop 12)
    018+ SELECT *FROM (SELECT Row_number() OVER ( ORDER BY PayTime DESC) AS [PageIndex], * FROM (SELECT oi.ID, oi.OrderCode, oi.ClientOrderCode, oi.MainOrderCode, oi.MergeOrderCode, oi.SalesRecordNumber, oi.OrderSourceID, oi.ClientUserAccount, oi.OrderStatus, oi.OrderState, oi.IsFirstDeliver, ( CASE WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority ELSE Isnull(os.PriorityNum, 0) END ) AS IsPriority, oi.PackagedIDs, oi.IsASingleInTheEnd, oi.PayerId, oi.Email, oi.SellerOrderId, oi.IsPay, oi.CommentType, oi.TransactionId, oi.PaymentType, Isnull(oi.RefundPrice, 0) AS RefundPrice, Isnull(oi.ThrowingRate, 0) AS ThrowingRate, oi.TotalPrice, oi.ExchangeRMB, oi.ExchangeUSD, oi.TransportPay, oi.OtherPayPrice, oi.ProductTotalPrice, oi.PromotionDiscountAmount, oi.CouponCode, oi.CouponMinusPrice, oi.DiscountMinusPrice, oi.AddPayPrice, oi.AddOrderReasonType, oi.ShareType, oi.ShareCustomerID, oi.IsAccount, oi.AccountTime, oi.ShareTotalPrice, oi.SynchronousTime, oi.IsSynchronous, oi.IsTogeInvoice, Isnull(oi.Encrypted, 0) AS Encrypted, oi.FirstName, oi.LastName, oi.Address, oi.District, oi.CabinetNO, oi.City, oi.Province, oi.Country, oi.PostCode, oi.Telephone, oi.ShippingService, oi.DownLoadUpdateTime, oi.PromotionType, oi.TrackNumberTemp, oi.TrackNumbers, oi.UpdatePlatformTime, oi.MarkDeliveryState, oi.EMPSDeliveryState, oi.LogisticsOrderNo, oi.LogisticsOrderNo1, oi.ShipTime, oi.OrderDescription, oi.ISsynchOverseas, oi.ProductNumber, oi.ProductVolume, oi.ProductWeight, oi.PayTime, oi.AddTime, oi.UpdateTime, oi.LatestShipDate, oi.ServiceAdminName, oi.SKUNumber, oi.LatestDeliveryDate, oi.VipDiscount, Isnull(oi.IsTaxFree, 0) IsTaxFree, oi.Currency, os.ServiceAdminName AS ServiceOSAdminName, Isnull(oi.IsFBATransit, 0) IsFBATransit, oi.WareHouseID, wh.WareHouseName, os.OrderSourceName, os.OrderSourceType, os.OrderSourceCountry, e.Country_cn, t.TransportName, t.PMSTransport, tapi.Transport_API_Class, wh.WareHouseType, oi.Token, os.SaleAdminName, os.VATNumber, os.VATRate, oi.SaveTrackNumbersTime, t.TrackingNumberValidDays, Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay, Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay, bt.BusinessTeamName, t.ID AS TransportID, e.ID AS CountryID, oi.Oaid, api.CertIDAccessKeyId, Isnull(cexpand.BillPdfUrl, '') BillPdfUrl, att1.IsAttachMail AS IsAttachMail FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN RB_OrderSource os WITH(nolock) ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID LEFT JOIN E_Country e WITH(nolock) ON oi.Country = e.Shorthand LEFT JOIN RB_Transport t WITH(nolock) ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID LEFT JOIN RB_Transport_API tapi WITH(nolock) ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID LEFT JOIN RB_WareHouse wh WITH(nolock) ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID LEFT JOIN RB_Admin rba WITH(nolock) ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID LEFT JOIN RB_BusinessTeam bt WITH(nolock) ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock) ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand ON oi.CustomerID = cexpand.CustomerID AND oi.OrderCode = cexpand.OrderCode OUTER apply (SELECT Count(1) IsAttachMail FROM RB_OrderInfo_Attach att(nolock) WHERE att.CustomerID = oi.CustomerID AND att.OrderCode = oi.OrderCode) att1 WHERE oi.CustomerID = 1 AND oi.IsStockOrder = 0 AND oi.IsPay = 1 AND oi.PayTime &gt;= '2023/4/1 0:00:00' AND oi.PayTime &lt;= '2023/4/13 23:59:59' AND EXISTS (SELECT 1 FROM RB_OrderList ol WITH(nolock) INNER JOIN RB_Product_Admin pa WITH(nolock) ON ol.CustomerID = pa.CustomerID AND ol.SKU = pa.SKU WHERE ol.CustomerID = 1 AND ol.OrderCode = oi.OrderCode AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTableWHERE PagerTable.[PageIndex] BETWEEN 1 AND 500OPTION(recompile, maxdop 12)
    018- <p><br />SELECT *<br />FROM (SELECT Row_number()<br /> OVER (<br /> ORDER BY PayTime DESC) AS [PageIndex],<br /> *<br /> FROM (SELECT oi.ID,<br /> oi.OrderCode,<br /> oi.ClientOrderCode,<br /> oi.MainOrderCode,<br /> oi.MergeOrderCode,<br /> oi.SalesRecordNumber,<br /> oi.OrderSourceID,<br /> oi.ClientUserAccount,<br /> oi.OrderStatus,<br /> oi.OrderState,<br /> oi.IsFirstDeliver,<br /> ( CASE<br /> WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority<br /> ELSE Isnull(os.PriorityNum, 0)<br /> END ) AS IsPriority,<br /> oi.PackagedIDs,<br /> oi.IsASingleInTheEnd,<br /> oi.PayerId,<br /> oi.Email,<br /> oi.SellerOrderId,<br /> oi.IsPay,<br /> oi.CommentType,<br /> oi.TransactionId,<br /> oi.PaymentType,<br /> Isnull(oi.RefundPrice, 0) AS RefundPrice,<br /> Isnull(oi.ThrowingRate, 0) AS ThrowingRate,<br /> oi.TotalPrice,<br /> oi.ExchangeRMB,<br /> oi.ExchangeUSD,<br /> oi.TransportPay,<br /> oi.OtherPayPrice,<br /> oi.ProductTotalPrice,<br /> oi.PromotionDiscountAmount,<br /> oi.CouponCode,<br /> oi.CouponMinusPrice,<br /> oi.DiscountMinusPrice,<br /> oi.AddPayPrice,<br /> oi.AddOrderReasonType,<br /> oi.ShareType,<br /> oi.ShareCustomerID,<br /> oi.IsAccount,<br /> oi.AccountTime,<br /> oi.ShareTotalPrice,<br /> oi.SynchronousTime,<br /> oi.IsSynchronous,<br /> oi.IsTogeInvoice,<br /> Isnull(oi.Encrypted, 0) AS Encrypted,<br /> oi.FirstName,<br /> oi.LastName,<br /> oi.Address,<br /> oi.District,<br /> oi.CabinetNO,<br /> oi.City,<br /> oi.Province,<br /> oi.Country,<br /> oi.PostCode,<br /> oi.Telephone,<br /> oi.ShippingService,<br /> oi.DownLoadUpdateTime,<br /> oi.PromotionType,<br /> oi.TrackNumberTemp,<br /> oi.TrackNumbers,<br /> oi.UpdatePlatformTime,<br /> oi.MarkDeliveryState,<br /> oi.EMPSDeliveryState,<br /> oi.LogisticsOrderNo,<br /> oi.LogisticsOrderNo1,<br /> oi.ShipTime,<br /> oi.OrderDescription,<br /> oi.ISsynchOverseas,<br /> oi.ProductNumber,<br /> oi.ProductVolume,<br /> oi.ProductWeight,<br /> oi.PayTime,<br /> oi.AddTime,<br /> oi.UpdateTime,<br /> oi.LatestShipDate,<br /> oi.ServiceAdminName,<br /> oi.SKUNumber,<br /> oi.LatestDeliveryDate,<br /> oi.VipDiscount,<br /> Isnull(oi.IsTaxFree, 0) IsTaxFree,<br /> oi.Currency,<br /> os.ServiceAdminName AS ServiceOSAdminName,<br /> Isnull(oi.IsFBATransit, 0) IsFBATransit,<br /> oi.WareHouseID,<br /> wh.WareHouseName,<br /> os.OrderSourceName,<br /> os.OrderSourceType,<br /> os.OrderSourceCountry,<br /> e.Country_cn,<br /> t.TransportName,<br /> t.PMSTransport,<br /> tapi.Transport_API_Class,<br /> wh.WareHouseType,<br /> oi.Token,<br /> os.SaleAdminName,<br /> os.VATNumber,<br /> os.VATRate,<br /> oi.SaveTrackNumbersTime,<br /> t.TrackingNumberValidDays,<br /> Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay,<br /> Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,<br /> bt.BusinessTeamName,<br /> t.ID AS TransportID,<br /> e.ID AS CountryID,<br /> oi.Oaid,<br /> api.CertIDAccessKeyId,<br /> Isnull(cexpand.BillPdfUrl, '') BillPdfUrl,<br /> att1.IsAttachMail AS IsAttachMail<br /> FROM RB_OrderInfo oi WITH(nolock)<br /> LEFT JOIN RB_OrderSource os WITH(nolock)<br /> ON oi.CustomerID = os.CustomerID<br /> AND oi.OrderSourceID = os.ID<br /> LEFT JOIN E_Country e WITH(nolock)<br /> ON oi.Country = e.Shorthand<br /> LEFT JOIN RB_Transport t WITH(nolock)<br /> ON oi.CustomerID = t.CustomerID<br /> AND oi.TransportID = t.ID<br /> LEFT JOIN RB_Transport_API tapi WITH(nolock)<br /> ON t.Transport_API_ID = tapi.ID<br /> AND t.CustomerID = tapi.CustomerID<br /> LEFT JOIN RB_WareHouse wh WITH(nolock)<br /> ON oi.CustomerID = wh.CustomerID<br /> AND oi.WareHouseID = wh.ID<br /> LEFT JOIN RB_Admin rba WITH(nolock)<br /> ON rba.CustomerID = os.CustomerID<br /> AND rba.ID = os.SaleAdminID<br /> LEFT JOIN RB_BusinessTeam bt WITH(nolock)<br /> ON bt.CustomerID = os.CustomerID<br /> AND bt.ID = rba.BusinessTeamID<br /> LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)<br /> ON os.CustomerID = api.CustomerID<br /> AND os.ID = api.OrderSourceID<br /> LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand<br /> ON oi.CustomerID = cexpand.CustomerID<br /> AND oi.OrderCode = cexpand.OrderCode<br /> OUTER apply (SELECT Count(1) IsAttachMail<br /> FROM RB_OrderInfo_Attach att(nolock)<br /> WHERE att.CustomerID = oi.CustomerID<br /> AND att.OrderCode = oi.OrderCode) att1<br /> WHERE oi.CustomerID = 1<br /> AND oi.IsStockOrder = 0<br /> AND oi.IsPay = 1<br /> AND oi.PayTime &gt;= '2023/4/1 0:00:00'<br /> AND oi.PayTime &lt;= '2023/4/13 23:59:59'<br /> AND EXISTS (SELECT 1<br /> FROM RB_OrderList ol WITH(nolock)<br /> INNER JOIN RB_Product_Admin pa WITH(nolock)<br /> ON ol.CustomerID = pa.CustomerID<br /> AND ol.SKU = pa.SKU<br /> WHERE ol.CustomerID = 1<br /> AND ol.OrderCode = oi.OrderCode<br /> AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTable<br />WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500<br />OPTION(recompile, maxdop 12)<br /></p>
    018+ <p><br />SELECT *<br />FROM (SELECT Row_number()<br /> OVER (<br /> ORDER BY PayTime DESC) AS [PageIndex],<br /> *<br /> FROM (SELECT oi.ID,<br /> oi.OrderCode,<br /> oi.ClientOrderCode,<br /> oi.MainOrderCode,<br /> oi.MergeOrderCode,<br /> oi.SalesRecordNumber,<br /> oi.OrderSourceID,<br /> oi.ClientUserAccount,<br /> oi.OrderStatus,<br /> oi.OrderState,<br /> oi.IsFirstDeliver,<br /> ( CASE<br /> WHEN oi.IsPriority &gt; 0 THEN oi.IsPriority<br /> ELSE Isnull(os.PriorityNum, 0)<br /> END ) AS IsPriority,<br /> oi.PackagedIDs,<br /> oi.IsASingleInTheEnd,<br /> oi.PayerId,<br /> oi.Email,<br /> oi.SellerOrderId,<br /> oi.IsPay,<br /> oi.CommentType,<br /> oi.TransactionId,<br /> oi.PaymentType,<br /> Isnull(oi.RefundPrice, 0) AS RefundPrice,<br /> Isnull(oi.ThrowingRate, 0) AS ThrowingRate,<br /> oi.TotalPrice,<br /> oi.ExchangeRMB,<br /> oi.ExchangeUSD,<br /> oi.TransportPay,<br /> oi.OtherPayPrice,<br /> oi.ProductTotalPrice,<br /> oi.PromotionDiscountAmount,<br /> oi.CouponCode,<br /> oi.CouponMinusPrice,<br /> oi.DiscountMinusPrice,<br /> oi.AddPayPrice,<br /> oi.AddOrderReasonType,<br /> oi.ShareType,<br /> oi.ShareCustomerID,<br /> oi.IsAccount,<br /> oi.AccountTime,<br /> oi.ShareTotalPrice,<br /> oi.SynchronousTime,<br /> oi.IsSynchronous,<br /> oi.IsTogeInvoice,<br /> Isnull(oi.Encrypted, 0) AS Encrypted,<br /> oi.FirstName,<br /> oi.LastName,<br /> oi.Address,<br /> oi.District,<br /> oi.CabinetNO,<br /> oi.City,<br /> oi.Province,<br /> oi.Country,<br /> oi.PostCode,<br /> oi.Telephone,<br /> oi.ShippingService,<br /> oi.DownLoadUpdateTime,<br /> oi.PromotionType,<br /> oi.TrackNumberTemp,<br /> oi.TrackNumbers,<br /> oi.UpdatePlatformTime,<br /> oi.MarkDeliveryState,<br /> oi.EMPSDeliveryState,<br /> oi.LogisticsOrderNo,<br /> oi.LogisticsOrderNo1,<br /> oi.ShipTime,<br /> oi.OrderDescription,<br /> oi.ISsynchOverseas,<br /> oi.ProductNumber,<br /> oi.ProductVolume,<br /> oi.ProductWeight,<br /> oi.PayTime,<br /> oi.AddTime,<br /> oi.UpdateTime,<br /> oi.LatestShipDate,<br /> oi.ServiceAdminName,<br /> oi.SKUNumber,<br /> oi.LatestDeliveryDate,<br /> oi.VipDiscount,<br /> Isnull(oi.IsTaxFree, 0) IsTaxFree,<br /> oi.Currency,<br /> os.ServiceAdminName AS ServiceOSAdminName,<br /> Isnull(oi.IsFBATransit, 0) IsFBATransit,<br /> oi.WareHouseID,<br /> wh.WareHouseName,<br /> os.OrderSourceName,<br /> os.OrderSourceType,<br /> os.OrderSourceCountry,<br /> e.Country_cn,<br /> t.TransportName,<br /> t.PMSTransport,<br /> tapi.Transport_API_Class,<br /> wh.WareHouseType,<br /> oi.Token,<br /> os.SaleAdminName,<br /> os.VATNumber,<br /> os.VATRate,<br /> oi.SaveTrackNumbersTime,<br /> t.TrackingNumberValidDays,<br /> Isnull(t.TrackNumbersIsNaturalDay, 1) AS TrackNumbersIsNaturalDay,<br /> Datediff(day, Getdate(), ( Dateadd(day, TrackingNumberValidDays, SaveTrackNumbersTime) )) AS TrackNumbersDiffDay,<br /> bt.BusinessTeamName,<br /> t.ID AS TransportID,<br /> e.ID AS CountryID,<br /> oi.Oaid,<br /> api.CertIDAccessKeyId,<br /> Isnull(cexpand.BillPdfUrl, '') BillPdfUrl,<br /> att1.IsAttachMail AS IsAttachMail<br /> FROM RB_OrderInfo oi WITH(nolock)<br /> LEFT JOIN RB_OrderSource os WITH(nolock)<br /> ON oi.CustomerID = os.CustomerID<br /> AND oi.OrderSourceID = os.ID<br /> LEFT JOIN E_Country e WITH(nolock)<br /> ON oi.Country = e.Shorthand<br /> LEFT JOIN RB_Transport t WITH(nolock)<br /> ON oi.CustomerID = t.CustomerID<br /> AND oi.TransportID = t.ID<br /> LEFT JOIN RB_Transport_API tapi WITH(nolock)<br /> ON t.Transport_API_ID = tapi.ID<br /> AND t.CustomerID = tapi.CustomerID<br /> LEFT JOIN RB_WareHouse wh WITH(nolock)<br /> ON oi.CustomerID = wh.CustomerID<br /> AND oi.WareHouseID = wh.ID<br /> LEFT JOIN RB_Admin rba WITH(nolock)<br /> ON rba.CustomerID = os.CustomerID<br /> AND rba.ID = os.SaleAdminID<br /> LEFT JOIN RB_BusinessTeam bt WITH(nolock)<br /> ON bt.CustomerID = os.CustomerID<br /> AND bt.ID = rba.BusinessTeamID<br /> LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)<br /> ON os.CustomerID = api.CustomerID<br /> AND os.ID = api.OrderSourceID<br /> LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand<br /> ON oi.CustomerID = cexpand.CustomerID<br /> AND oi.OrderCode = cexpand.OrderCode<br /> OUTER apply (SELECT Count(1) IsAttachMail<br /> FROM RB_OrderInfo_Attach att(nolock)<br /> WHERE att.CustomerID = oi.CustomerID<br /> AND att.OrderCode = oi.OrderCode) att1<br /> WHERE oi.CustomerID = 1<br /> AND oi.IsStockOrder = 0<br /> AND oi.IsPay = 1<br /> AND oi.PayTime &gt;= '2023/4/1 0:00:00'<br /> AND oi.PayTime &lt;= '2023/4/13 23:59:59'<br /> AND EXISTS (SELECT 1<br /> FROM RB_OrderList ol WITH(nolock)<br /> INNER JOIN RB_Product_Admin pa WITH(nolock)<br /> ON ol.CustomerID = pa.CustomerID<br /> AND ol.SKU = pa.SKU<br /> WHERE ol.CustomerID = 1<br /> AND ol.OrderCode = oi.OrderCode<br /> AND pa.DevelopAdminID = 6323)) AS TempTable) AS PagerTable<br />WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500<br />OPTION(recompile, maxdop 12)</p>
  6. 2023-04-18 16:19:40, 由 陈少军 指派给 陈少军。
    修改了 指派给，旧值为 "chengwei"，新值为 "chenshaojun"。
    产开发沟通了，需从业务角度优化
  7. 2023-04-18 16:19:51, 由 陈少军 完成。
    修改了 总计消耗，旧值为 "0"，新值为 "1"。
    修改了 实际完成，旧值为 ""，新值为 "2023-04-18 16:19:45"。
    修改了 任务状态，旧值为 "wait"，新值为 "done"。
    修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2023-04-18 16:19:51"。
    修改了 预计剩余，旧值为 "2"，新值为 "0"。
    修改了 由谁完成，旧值为 ""，新值为 "chenshaojun"。
  8. 2023-10-31 14:53:16, 由 周杰 关闭。
    修改了 任务状态，旧值为 "done"，新值为 "closed"。
    修改了 指派给 ，旧值为 "chenshaojun"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
    修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2023-10-31 14:53:16"。
    修改了 关闭原因，旧值为 ""，新值为 "done"。
返回
所属迭代 系统管理部 - 系统服务优化
所属模块 /
相关需求 暂无
指派给 Closed 于 2023-10-31 14:53:16
任务类型 后端
任务标签 初始
开发组
任务状态 已关闭
进度 100%
优先级 P2
关键词
抄送给
由谁创建 陈少军 于 2023-04-14 15:28:20
由谁完成 陈少军 于 2023-04-18 16:19:45
由谁取消 暂无
由谁关闭 周杰 于 2023-10-31 14:53:16
关闭原因 已完成
最后编辑 周杰 于 2023-10-31 14:53:16
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 0工时
总计消耗 1工时
预计剩余 0工时
预计开始 2023-04-14
实际开始 2023-04-18
截止日期 2023-04-21