Sql语句的知识积累

SELECT T1.ManagerName Manager,T2.* FROM platform.dbo.Bonus_Amazon_ManagerTarget T1 WITH(NOLOCK)
OUTER APPLY
(
SELECT
Income=SUM(Income),
AfnIncome= SUM(AfnIncome),
InstockQTY=SUM(InstockQTY),
InstockCost=SUM(InstockCost),
InboundQTY=SUM(InboundQTY),
InboundCost=SUM(InboundCost),
NotCount=SUM(NotCount),
NotCountCost=SUM(NotCountCost),
ActualInCount=SUM(ActualInCount),
ActualInCountCost=SUM(ActualInCountCost),
TotalCost=SUM(TotalCost),
Rate=SUM(Rate),
ThirtySalesQty=SUM(ThirtySalesQty),
DOI=SUM(DOI),
SyncTime=MAX(SyncTime)
FROM Formal_Amazon.dbo.Report_Stockup_AmazonShop
WHERE Manager LIKE '%'+T1.ManagerName+'%'
) T2
WHERE T1.DataYear=DATEPART(YEAR,GETDATE()) AND T1.DataMonth=DATEPART(MONTH,GETDATE())

outer apply,主要是为了追加字段。该写法不适合复杂的sql查询,但有时候也是一个查询的解决方案,作为写SQL语句,应该要了解的一个写法

sql server中的datediff与dateadd,效率相差很大。datediff会破坏索引。具体操作可以写两条sql语句,
然后通过“显示估计的执行计划”来看执行效率   (PS:显示估计的执行计划,是优化sql非常重要的一个手段。ssms一般都会有,反正我的v18.5是有的)

Sql语句的知识积累

 

上一篇:sqlserver夸服务器夸数据库查询


下一篇:sqlserver查看数据库的连接数