把子查询所得到的列作为运算列

[绿豆丫]

SELECT PID,SaleOrderID,MaterialID,NewOrderNum,OldOrderNum,OldBuyIn  
FROM st_NeedBuyIn AS A
WHERE EXISTS
(	SELECT MaterialID,Number 
	FROM HtSaleProductDetail AS B
	WHERE SaleProductID=(
		SELECT AlreadySaleProduct FROM HtSaleOrder WHERE PID=24
			     ) 	AND A.MaterialID=B.MaterialID)

我想把别名B表中的Number，作为别名A表中的运算列，也就是OldBuyIn=OldBuyIn * B.Number，能否实现，请各位指点，谢谢！

[绿豆丫]

--表HtSaleOrder
DROP TABLE [HtSaleOrder] 
 CREATE TABLE [HtSaleOrder] (  [PID] [int] IDENTITY (1, 1) NOT NULL ,[Number] [varchar] (50) NULL ,[AlreadySaleProduct] [int] NULL )
 ALTER TABLE [HtSaleOrder] WITH NOCHECK ADD CONSTRAINT [PK_HtSaleOrder] PRIMARY KEY  NONCLUSTERED ( [PID] )

 SET IDENTITY_INSERT [HtSaleOrder] ON


 INSERT [HtSaleOrder] ( [PID] , [Number] , [AlreadySaleProduct]  ) VALUES ( 24 ,'100',16)

 SET IDENTITY_INSERT [HtSaleOrder] OFF

--表HtSaleProductDetail

DROP TABLE [HtSaleProductDetail] 
 CREATE TABLE [HtSaleProductDetail] (  [PID] [int] IDENTITY (1, 1) NOT NULL ,  [SaleProductID] [int] NULL ,  [MaterialID] [int] NULL ,  [Number] [int] NULL )
 ALTER TABLE [HtSaleProductDetail] WITH NOCHECK ADD CONSTRAINT [PK_HtSaleProductDetail] PRIMARY KEY  NONCLUSTERED ( [PID] )

 SET IDENTITY_INSERT [HtSaleProductDetail] ON

 INSERT [HtSaleProductDetail] ( [PID] , [SaleProductID] , [MaterialID] , [Number] ) VALUES ( 114 , 16 , 17 , 10 )
 INSERT [HtSaleProductDetail] ( [PID] , [SaleProductID] , [MaterialID] , [Number] ) VALUES ( 115 , 16 , 18 , 20 )
 INSERT [HtSaleProductDetail] ( [PID] , [SaleProductID] , [MaterialID] , [Number] ) VALUES ( 116 , 16 , 20 , 40 )
 INSERT [HtSaleProductDetail] ( [PID] , [SaleProductID] , [MaterialID] , [Number] ) VALUES ( 117 , 16 , 22 , 2 )
 INSERT [HtSaleProductDetail] ( [PID] , [SaleProductID] , [MaterialID] , [Number] ) VALUES ( 118 , 16 , 23 , 3 )

 SET IDENTITY_INSERT [HtSaleProductDetail] OFF

--表St_NeedBuyIn

 DROP TABLE [St_NeedBuyIn] 
 CREATE TABLE [St_NeedBuyIn] (  [PID] [int] IDENTITY (1, 1) NOT NULL ,  [SaleOrderID] [int] NULL ,  [MaterialID] [int] NULL ,  [NewOrderNum] [int] NULL ,  

[OldOrderNum] [int] NULL ,  [OldBuyIn] [int] NULL )
 ALTER TABLE [St_NeedBuyIn] WITH NOCHECK ADD CONSTRAINT [DF_St_NeedBuyIn_NewOrderNum] DEFAULT (0) FOR [NewOrderNum],CONSTRAINT [DF_St_NeedBuyIn_OldOrderNum] 

DEFAULT (0) FOR [OldOrderNum],CONSTRAINT [DF_St_NeedBuyIn_OldBuyIn] DEFAULT (0) FOR [OldBuyIn],CONSTRAINT [PK_St_NeedBuyIn] PRIMARY KEY  NONCLUSTERED ( [PID] 

)

 SET IDENTITY_INSERT [St_NeedBuyIn] ON


 INSERT [St_NeedBuyIn] ( [PID] , [MaterialID] , [NewOrderNum] , [OldOrderNum] , [OldBuyIn] ) VALUES ( 17 , 15 , 0 , 0 , 0 )
 INSERT [St_NeedBuyIn] ( [PID] , [MaterialID] , [NewOrderNum] , [OldOrderNum] , [OldBuyIn] ) VALUES ( 18 , 16 , 0 , 0 , 0 )
 INSERT [St_NeedBuyIn] ( [PID] , [MaterialID] , [NewOrderNum] , [OldOrderNum] , [OldBuyIn] ) VALUES ( 19 , 17 , 100 , 0 , 0 )
 INSERT [St_NeedBuyIn] ( [PID] , [MaterialID] , [NewOrderNum] , [OldOrderNum] , [OldBuyIn] ) VALUES ( 20 , 20 , 100 , 0 , 0 )
 INSERT [St_NeedBuyIn] ( [PID] , [MaterialID] , [NewOrderNum] , [OldOrderNum] , [OldBuyIn] ) VALUES ( 21 , 21 , 0 , 0 , 0 )
 INSERT [St_NeedBuyIn] ( [PID] , [MaterialID] , [NewOrderNum] , [OldOrderNum] , [OldBuyIn] ) VALUES ( 22 , 22 , 100 , 0 , 0 )
 INSERT [St_NeedBuyIn] ( [PID] , [MaterialID] , [NewOrderNum] , [OldOrderNum] , [OldBuyIn] ) VALUES ( 23 , 23 , 100 , 0 , 0 )

 SET IDENTITY_INSERT [St_NeedBuyIn] OFF

测试数据，共三个表。2楼的方法试过，语法正确，但结果不对。谢谢

[绿豆丫]

求助人呢？

[开着拖拉机泡妞]

直接是不可以的吧。建议你提供测试数据和结果

[Felixzhaowenzhong]

SELECT PID,SaleOrderID,MaterialID,NewOrderNum,OldOrderNum,(OldBuyIn*B.Number) OldBuyIn  
FROM st_NeedBuyIn AS A
join HtSaleProductDetail AS B on A.MaterialID=B.MaterialID
join (SELECT AlreadySaleProduct FROM HtSaleOrder WHERE PID=24) as C ON B.SaleProductID=C.AlreadySaleProduct

[shuobudeDE]

LZ 给答案吧