22,207
社区成员
发帖
与我相关
我的任务
分享
SELECT DISTINCT c.FNumber 物料代码,c.FName 物料名称,c.FModel 物料规格,b.FMapNumber 客户对应代码,d.FNumber 客户代码,'' 价格类型,1 [销货量(从)],10000000 [销货量(到)], b1.FNumber 计量单位代码,a1.FName 币别,0 [报价],e1.FName 业务员,CONVERT(DATETIME,'1900-01-01') 生效日期,CONVERT(DATETIME,'2100-01-01') 失效日期,'' 备注,d.fname 客户名称,a.FDate 订单日期 ,a.FInterID,a.FBillNo 订单编号,ss1.fname 销售模式
FROM dbo.SEOrder a INNER JOIN dbo.SEOrderEntry b ON a.FInterID = b.FInterID
INNER JOIN dbo.t_MeasureUnit b1 ON b.FUnitID=b1.FMeasureUnitID
INNER JOIN dbo.t_ICItem c ON b.FItemID = c.FItemID INNER JOIN dbo.t_Organization d ON a.FCustID=d.FItemID
INNER JOIN dbo.t_Currency a1 ON a.FCurrencyID=a1.FCurrencyID LEFT JOIN t_Item t7933 ON b.FEntrySelfS0150 = t7933.FItemID
LEFT JOIN dbo.t_Emp e1 ON a.FEmpID=e1.FItemID
LEFT JOIN dbo.t_SubMessage ss1 ON ss1.FInterID=a.FHeadSelfS0138 WHERE ISNULL(d.F_120,0)=1 and t7933.fname not like '%取消%'
AND a.FDate >='2014-09-23' and a.FDate<='2014-10-23' AND
CONVERT(VARCHAR(15),a.FCustID)+CONVERT(VARCHAR(15),b.FItemID)+CONVERT(VARCHAR(15),a.FCurrencyID)+b.FMapNumber
NOT IN (
SELECT CONVERT(VARCHAR(15),FCustID)+CONVERT(VARCHAR(15),FItemID)+CONVERT(VARCHAR(15),FCurrency)+FMapNumber
FROM dbo.t_Xm_SalePrice WHERE CONVERT(VARCHAR(10),GETDATE(),120)>=FQuoteTime
AND CONVERT(VARCHAR(10),GETDATE(),120)<=FDisTime ) AND a.FInterID=(SELECT min(a1.FInterID)
FROM seorder a1 INNER JOIN dbo.SEOrderEntry b1 ON a1.FInterID = b1.FInterID
WHERE a1.FCustID=a.FCustID AND b1.FItemID=b.FItemID AND b1.FMapNumber=b.FMapNumber and a1.fdate>='2014-09-23'
)
AND NOT EXISTS (SELECT *
FROM dbo.t_xm_saleprice s
WHERE s.fcustid = a.fcustid
AND s.fitemid = b.fitemid
AND s.fcurrency = a.fcurrencyid
AND s.fmapnumber = b.fmapnumber)
SELECT DISTINCT
c.FNumber 物料代码 ,
c.FName 物料名称 ,
c.FModel 物料规格 ,
b.FMapNumber 客户对应代码 ,
d.FNumber 客户代码 ,
'' 价格类型 ,
1 [销货量(从)] ,
10000000 [销货量(到)] ,
b1.FNumber 计量单位代码 ,
a1.FName 币别 ,
0 [报价] ,
e1.FName 业务员 ,
CONVERT(DATETIME, '1900-01-01') 生效日期 ,
CONVERT(DATETIME, '2100-01-01') 失效日期 ,
'' 备注 ,
d.fname 客户名称 ,
a.FDate 订单日期 ,
a.FInterID ,
a.FBillNo 订单编号 ,
ss1.fname 销售模式
FROM dbo.SEOrder a
INNER JOIN dbo.SEOrderEntry b ON a.FInterID = b.FInterID
INNER JOIN dbo.t_MeasureUnit b1 ON b.FUnitID = b1.FMeasureUnitID
INNER JOIN dbo.t_ICItem c ON b.FItemID = c.FItemID
INNER JOIN dbo.t_Organization d ON a.FCustID = d.FItemID
INNER JOIN dbo.t_Currency a1 ON a.FCurrencyID = a1.FCurrencyID
LEFT JOIN t_Item t7933 ON b.FEntrySelfS0150 = t7933.FItemID
LEFT JOIN dbo.t_Emp e1 ON a.FEmpID = e1.FItemID
LEFT JOIN dbo.t_SubMessage ss1 ON ss1.FInterID = a.FHeadSelfS0138
WHERE ISNULL(d.F_120, 0) = 1
AND t7933.fname NOT LIKE '%取消%'
AND a.FDate >= '2014-09-23'
AND a.FDate <= '2014-10-23'
AND CONVERT(VARCHAR(15), a.FCustID) + CONVERT(VARCHAR(15), b.FItemID)
+ CONVERT(VARCHAR(15), a.FCurrencyID) + b.FMapNumber NOT IN ( --这个not in的地方可以使用not exists来处理
SELECT CONVERT(VARCHAR(15), FCustID) + CONVERT(VARCHAR(15), FItemID)
+ CONVERT(VARCHAR(15), FCurrency) + FMapNumber
FROM dbo.t_Xm_SalePrice
WHERE CONVERT(VARCHAR(10), GETDATE(), 120) >= FQuoteTime
AND CONVERT(VARCHAR(10), GETDATE(), 120) <= FDisTime )
AND a.FInterID = ( SELECT MIN(a1.FInterID) --这个地方也可以考虑使用exists来处理
FROM seorder a1
INNER JOIN dbo.SEOrderEntry b1 ON a1.FInterID = b1.FInterID
WHERE a1.FCustID = a.FCustID
AND b1.FItemID = b.FItemID
AND b1.FMapNumber = b.FMapNumber
AND a1.fdate >= '2014-09-23'
)
配合索引进行处理 。参考 http://blog.csdn.net/orchidcat/article/details/6267552
另外,最好贴出来执行计划看看是哪儿的问题。
AND
CONVERT(VARCHAR(15),a.FCustID)+CONVERT(VARCHAR(15),b.FItemID)+CONVERT(VARCHAR(15),a.FCurrencyID)+b.FMapNumber
NOT IN (
SELECT CONVERT(VARCHAR(15),FCustID)+CONVERT(VARCHAR(15),FItemID)+CONVERT(VARCHAR(15),FCurrency)+FMapNumber
FROM dbo.t_Xm_SalePrice WHERE CONVERT(VARCHAR(10),GETDATE(),120)>=FQuoteTime
AND CONVERT(VARCHAR(10),GETDATE(),120)<=FDisTime )
主要是这段的问题,上面连接的几个表都是要用的,将几个表的字段相加起来组成一个字段,再用NOT IN 判断是否在里面,括号里面的表也是几个字段相加起来,这个注释就很快。