I want to do something like the example below
我想做一些类似下面的例子
ColumnAVal = Select ColumnA from TableA where....
ColumnBVal = Select ColumnB from TableA where....
Select * from TableB where ColumnC = (value from ColumnA)
Select * from TableC where ColumnD = (value from ColumnB)
I have to get the values from TableA query which has one hefty where clause. And I have to get about 20 different columns from TableA. So, above example won't be a good idea. I am trying to find a better way to save these values, that doesn't require me to use same where clause 20 times.
我必须从TableA查询中获取值它有一个大where子句。我需要从TableA得到20个不同的列。上面的例子不是个好主意。我正在寻找一个更好的方法来保存这些值，这并不要求我使用相同的where子句20次。
Basically idea is to get the 20 or so columns from the TableA and save those as variables for later use. That way I can create just one query to save the column values instead of calling it twenty times.
基本的想法是，从TableA中获取20个左右的列，并将这些列保存为以后使用的变量。这样，我可以创建一个查询来保存列值，而不是调用它20次。
E.g.
如。
@QuantityAvailable = SELECT TOP 1 WLPS_Qty_Available FROM
TBL_Warehouse_Location_Parts_Spec, TBL_Location_Master,
TBL_Warehouse_Master WHERE WLPS_Parts_Spec_ID = @PartSpecID AND WLPS_Part_Type_ID IN ( 0, @PartTypeID ) AND WLPS_Active_Flag = 'Y' AND ( WLPS_Location_ID = @LocationID )
I have to run the same query again and again 20 times. And I would prefer I don't have to, to save some processing.
我必须反复运行同一个查询20次。我宁愿我不需要，保存一些处理。
2 个解决方案
#1
3
declare @ColumnAVal varchar(20);
declare @ColumnBVal varchar(20);
select @ColumnAVal = ColumnA , @ColumnBVal = ColumnB from TableA where....
The values used are from the last row in the returned set so it only makes sense when the select returns a single row or it's suitably ordered (less good).
所使用的值来自返回集中的最后一行，因此只有当select返回单个行或它有适当的排序(不太好)时才有意义。
#2
0
--I have to assume that the where clause is different in each case
我必须假设where子句在每种情况下都是不同的
Select * from TableB where ColumnC IN (Select ColumnA from TableA where....)
Select * from TableC where ColumnD IN (Select ColumnB from TableA where....)
#1
3
declare @ColumnAVal varchar(20);
declare @ColumnBVal varchar(20);
select @ColumnAVal = ColumnA , @ColumnBVal = ColumnB from TableA where....
The values used are from the last row in the returned set so it only makes sense when the select returns a single row or it's suitably ordered (less good).
所使用的值来自返回集中的最后一行，因此只有当select返回单个行或它有适当的排序(不太好)时才有意义。
#2
0
--I have to assume that the where clause is different in each case
我必须假设where子句在每种情况下都是不同的
Select * from TableB where ColumnC IN (Select ColumnA from TableA where....)
Select * from TableC where ColumnD IN (Select ColumnB from TableA where....)