以下为存储过程中的主要部分:SELECT
S.Type,
left( convert(varchar(10),D.DateIn,110) ,5) as DateIn,
EmplNo=isnull(p.EmplNo + '-'+ P.EmplName,(select top 1 EmplNo +'-'+ EmplName from SaleProfit where FlowNo=d.FlowNo)),
isnull(P.PaymentByNoMoney,0 ) as PaymentByNoMoney,
isnull(P.AchieveByPayment,0 ) as AchieveByPayment,
isnull(P.AchieveByPrice,0 ) as AchieveByPrice,
isnull(P.ProfitSceneMoney,0 ) as ProfitSceneMoney,
isnull(P.ProfitSceneAchieve,0 ) as ProfitSceneAchieve, isnull(P.ConsumeByPrice,0 ) as ConsumeByPrice,
isnull(P.ConsumeByPayment,0 ) as ConsumeByPayment,
isnull(P.ProfitGeneral,0 ) as ProfitGeneral,
isnull(P.ProfitSpecial,0 ) as ProfitSpecial, D.ItemSort,
D.ItemNo,
D.ItemName,
D.PriceDiscount,
D.Much, P.ItemConsumeType,
P.ItemGradeType,
isnull(P.ServiceHowMany,0) as ServiceHowMany,
P.ServiceMode,
P.ServiceContent
INTO
##TempDataFROM
SaleProfit P, SaleDetail D
LEFT JOIN
SaleMain S
ON
S.FlowNo=D.FlowNo WHERE
P.ProfitID = D.Guid AND '+ 灵活的条件组合语句
请问:
1.提取此记录的说明及规则,请详见以下地址中的帖子里的 倒数第 2 个 及 倒数 第 4 个 的解决方法和说明。
2.通过什么样的方法来实现以上的要求,尽可能地提高数据效率?
3.如果能提高语句的执行效力,换成其它的方法或思路也行。
4.明天 端午节了 祝各们节日快乐!和身边的亲人,朋友 快乐地享受可口的粽子~~~
在线等待ing...
S.Type,
left( convert(varchar(10),D.DateIn,110) ,5) as DateIn,
EmplNo=isnull(p.EmplNo + '-'+ P.EmplName,(select top 1 EmplNo +'-'+ EmplName from SaleProfit where FlowNo=d.FlowNo)),
isnull(P.PaymentByNoMoney,0 ) as PaymentByNoMoney,
isnull(P.AchieveByPayment,0 ) as AchieveByPayment,
isnull(P.AchieveByPrice,0 ) as AchieveByPrice,
isnull(P.ProfitSceneMoney,0 ) as ProfitSceneMoney,
isnull(P.ProfitSceneAchieve,0 ) as ProfitSceneAchieve, isnull(P.ConsumeByPrice,0 ) as ConsumeByPrice,
isnull(P.ConsumeByPayment,0 ) as ConsumeByPayment,
isnull(P.ProfitGeneral,0 ) as ProfitGeneral,
isnull(P.ProfitSpecial,0 ) as ProfitSpecial, D.ItemSort,
D.ItemNo,
D.ItemName,
D.PriceDiscount,
D.Much, P.ItemConsumeType,
P.ItemGradeType,
isnull(P.ServiceHowMany,0) as ServiceHowMany,
P.ServiceMode,
P.ServiceContent
INTO
##TempDataFROM
SaleProfit P, SaleDetail D
LEFT JOIN
SaleMain S
ON
S.FlowNo=D.FlowNo WHERE
P.ProfitID = D.Guid AND '+ 灵活的条件组合语句
请问:
1.提取此记录的说明及规则,请详见以下地址中的帖子里的 倒数第 2 个 及 倒数 第 4 个 的解决方法和说明。
2.通过什么样的方法来实现以上的要求,尽可能地提高数据效率?
3.如果能提高语句的执行效力,换成其它的方法或思路也行。
4.明天 端午节了 祝各们节日快乐!和身边的亲人,朋友 快乐地享受可口的粽子~~~
在线等待ing...
http://community.csdn.net/Expert/topic/4774/4774632.xml?temp=.5069696请见此帖子里的 倒数第 2 个 及 倒数 第 4 个 的解决方法和说明。
改成
convert(varchar(5),D.DateIn,110) as DateIn其它的就是看你索引怎么建的
SaleDetail的索引有 FlowNo,ID
SlaeProfit 的索引有 FlowNo,ProfitID
from SaleDetail D join SaleProfit P
on P.ProfitID = D.Guid AND '+ 灵活的条件组合语句
left outer join SaleMain S
on S.FlowNo=D.FlowNo
你再试试用多长时间
SELECT
...
into ##TempData
FROM
SaleProfit P
INNER JOIN SaleDetail D on P.ProfitID = D.Guid AND '+ 灵活的条件组合语句
LEFT JOIN SaleMain S ON S.FlowNo=D.FlowNo
并 优化索引设置,
另外,"EmplNo=isnull(p.EmplNo + '-'+ P.EmplName,(select top 1 EmplNo +'-'+ EmplName from SaleProfit where FlowNo=d.FlowNo)),",此处似有不妥,