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

最新推荐文章于 2023-08-09 17:22:03 发布

原创最新推荐文章于 2023-08-09 17:22:03 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #join

SQL 杂谈专栏收录该内容

2 篇文章

订阅专栏

本文介绍了如何通过SQL查询找出上期结账的老物料存货余额，并将其导入到新物料存货中，同时进行存货余额的调整。通过使用LEFT JOIN和条件查询，确保了操作的有效性和准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--------查询出上期结账的老物料存货余额，导入到新物料存货中去并将老物料存货余额调平。----------
Select S1.NumAtCrd  , S1.CrdID  , S1.ItmID , TB1.ItmName , TB1.ItmSpec , TB1.Serials , S1.WhsID , S1.LineSumFC  , S1.FreeTxt
From(
	Select CONVERT(varchar(100), GETDATE(), 112) AS NumAtCrd , 'A05' CrdID , @OldItmID ItmID, WhsID , -EndAmount LineSumFC , 
		'关闭物料，原始物料存货余额调平' FreeTxt
	From AAFinWh
	Where 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 , EndAmount
	Having AbsID = (Select Max(AbsID) From AAFinWh Where ItmID = @OldItmID) 
	UNION ALL
	Select CONVERT(varchar(100), GETDATE(), 112) AS NumAtCrd , 'A05' CrdID , @NewItmID ItmID, TB1.WhsID , Sum(EndAmount) LineSumFC ,
		'关闭物料的存货余额，调至新物料中' FreeTxt
	From AAFinWh TD1
	LEFT JOIN MDItm TB1 ON TD1.ItmID = TB1.ItmID
	Where 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.WhsID
	Having 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 这样的类似条件去限制将会更有效率。