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 判断是否在里面,括号里面的表也是几个字段相加起来,这个注释就很快。
NOT IN 估计要改成 LEFT JOIN 来了
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另外,最好贴出来执行计划看看是哪儿的问题。
4个字段的比较当然是应该逐个直接比较,先转换、再拼字符串、再比较太作死了。 NOT IN 不是改成 LEFT JOIN,是改成 NOT EXISTS。
我改成LEFT JOIN 然后排除后表为NULL的,貌似是可以了。 谢谢 Tiger_Zhao 老师,请问 NOT EXISTS 怎么改呢,几乎很少用这个来。
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)
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 判断是否在里面,括号里面的表也是几个字段相加起来,这个注释就很快。
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另外,最好贴出来执行计划看看是哪儿的问题。
CONVERT(VARCHAR(15),a.FCustID)+CONVERT(VARCHAR(15),b.FItemID)+CONVERT(VARCHAR(15),a.FCurrencyID)+b.FMapNumber
能换成几个条件吗
NOT IN 不是改成 LEFT JOIN,是改成 NOT EXISTS。
谢谢 Tiger_Zhao 老师,请问 NOT EXISTS 怎么改呢,几乎很少用这个来。
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)