SQL Server 如何将子查询结果存储在变量中并在主查询中使用

6xfqseft 于 2022-11-28 发布在其他

关注(0)|答案(1)|浏览(227)

我需要将subquery结果存储在变量中，并用于主查询中，以保存查询的执行时间，否则我必须为此再次编写相同的subquery。代码如下所示：

select DISTINCT
convert(datetime,substring(o.TransactTime, 0,9), 112) + CONVERT(datetime, substring(o.transacttime,10,LEN(o.transacttime)), 114),
o.clordid,
ack.strategyid,
o.Account, 
o.AllocAccount, 
o.symbol,
o.price,
ISNULL((Select Top 1 L.orderqty from order_msgs_incoming1 L where o.clordid=L.clordid and L.msg_id in (17,18,19,22,59,44) order BY L.transacttime DESC),o.orderqty) orderqty,

o.InClOrdID,
(SELECT  ISNULL(ISNULL((Select Top 1 L.orderqty from order_msgs_incoming1 L where o.clordid=L.clordid and L.msg_id in (17,18,19,22,59,44) order BY L.transacttime DESC),o.orderqty)-sum(ex.LastShares),ISNULL((Select Top 1 L.orderqty from order_msgs_incoming1 L where o.clordid=L.clordid and L.msg_id in (17,18,19,22,59,44) order BY L.transacttime DESC),o.orderqty)) from order_msgs_incoming<TblIndx> ex where
    o.Clordid = ex.clordid 
) RemainingQuantity
from order_msgs_incoming1 o 
/*picking ack rows to update strategyId since StrategyId is null for 43 but populated for 46*/
left outer join order_msgs_incoming1 ack on o.clordid = ack.clordid and ack.msg_id=46
where 
/*row for new order only since that contains all columns values*/
o.msg_id = 43

我需要这样

select DISTINCT
variableValue=ISNULL((Select Top 1 L.orderqty from order_msgs_incoming1 L where o.clordid=L.clordid and L.msg_id in (17,18,19,22,59,44) order BY L.transacttime DESC),o.orderqty) orderqty,

o.InClOrdID,
(SELECT  ISNULL(variableValue-sum(ex.LastShares),variableValue) from order_msgs_incoming<TblIndx> ex where
    o.Clordid = ex.clordid 
) RemainingQuantity
from order_msgs_incoming1 o

请你帮帮我。

sql-server

来源：https://stackoverflow.com/questions/26601768/how-do-i-store-subquery-result-in-variable-and-use-in-master-query

1条答案

按热度按时间

yhived7q1#

您可以使用SELECT子句声明一个变量并为其赋值。然后在主查询中使用该变量。请查看以下代码：

DECLARE @variableValue bit;

SELECT @variableValue = ISNULL(
                        (
                        SELECT TOP 1 L.orderqty 
                        FROM order_msgs_incoming1 L 
                        WHERE o.clordid=L.clordid
                        AND L.msg_id in (17,18,19,22,59,44) 
                        ORDER BY L.transacttime DESC
                        )
                        ,o.orderqty
                    )
FROM order_msgs_incoming1 o

SELECT DISTINCT
@variableValue AS orderqty,
o.InClOrdID,
(
    SELECT  ISNULL(variableValue-sum(ex.LastShares)
    ,variableValue) 
    FROM order_msgs_incoming<TblIndx> ex 
    WHERE o.Clordid = ex.clordid 
) RemainingQuantity
FROM order_msgs_incoming1 o

也可以使用以下代码更改子查询，以便在变量中使用它：

SELECT TOP 1 L.orderqty 
FROM order_msgs_incoming1 L 
INNER JOIN order_msgs_incoming1 o 
    ON o.clordid=L.clordid
    AND L.msg_id in (17,18,19,22,59,44) 
ORDER BY L.transacttime DESC

赞(0）回复(0）举报 2022-11-28

我来回答

SQL Server 如何将子查询结果存储在变量中并在主查询中使用

1条答案

相关问题

热门标签

最新问答