SQL 之Where条件与From表无关性例子

来源：互联网发布：淘宝怎么买分期手机编辑：程序博客网时间：2024/06/05 05:04

--------查询出上期结账的老物料存货余额，导入到新物料存货中去并将老物料存货余额调平。----------Select S1.NumAtCrd  , S1.CrdID  , S1.ItmID , TB1.ItmName , TB1.ItmSpec , TB1.Serials , S1.WhsID , S1.LineSumFC  , S1.FreeTxtFrom(Select CONVERT(varchar(100), GETDATE(), 112) AS NumAtCrd , 'A05' CrdID , @OldItmID ItmID, WhsID , -EndAmount LineSumFC , '关闭物料，原始物料存货余额调平' FreeTxtFrom AAFinWhWhere ItmID = @OldItmID AND 1 = ISNULL(                --检查OldItmID是否可用(select top 1 1 from MDItm T0 where T0.ItmID = @OldItmID and @OldItmID <> @NewItmID and T0.IsClose = 'N') , 2) AND 1 = ISNULL( (select top 1 1 from MDItm T0  where T0.ItmID = @NewItmID and T0.IsClose = 'N') , 2)  -- 检查新物料是否可用Group by AbsID , WhsID , EndAmountHaving AbsID = (Select Max(AbsID) From AAFinWh Where ItmID = @OldItmID) UNION ALLSelect CONVERT(varchar(100), GETDATE(), 112) AS NumAtCrd , 'A05' CrdID , @NewItmID ItmID, TB1.WhsID , Sum(EndAmount) LineSumFC ,'关闭物料的存货余额，调至新物料中' FreeTxtFrom AAFinWh TD1LEFT JOIN MDItm TB1 ON TD1.ItmID = TB1.ItmIDWhere TD1.ItmID = @OldItmID AND 1 = ISNULL(                --检查OldItmID是否可用(select top 1 1 from MDItm T0 where T0.ItmID = @OldItmID and @OldItmID <> @NewItmID and T0.IsClose = 'N') , 2) AND 1 = ISNULL( (select top 1 1 from MDItm T0  where T0.ItmID = @NewItmID and T0.IsClose = 'N') , 2) -- 检查新物料是否可用Group by TD1.AbsID , TB1.WhsIDHaving TD1.AbsID = (Select Max(AbsID) From AAFinWh Where ItmID = @OldItmID))S1 LEFT JOIN MDItm TB1 ON S1.ItmID = TB1.ItmID

查询中，如何去检查老物料是否可用，新物料是否可用。可用LEFT JOIN 去左连AAFinWh，但万一要查的XX表与AAFinWh没有关系，此时的LEFT JOIN 是没有效率的。

显然用 1 = Select Top 1 1 From XX Where YY = ZZ 这样的类似条件去限制将会更有效率。