MSSQL中一列计算结果在不同列中的使用
MSSQL中一列计算结果在不同列中的使用
提问于 2017-05-31 09:43:09
我试图在MS的后面列中使用计算中的一列的结果。
下面是我的SQL代码：
SELECT
(SELECT 'EURUSD' AS a) AS symbol_name,
(SELECT TOP 1 ROUND(p.BidLast,5) FROM mt5_prices p WHERE p.Symbol LIKE 'EURUSD%') AS Bid,
(SELECT SUM(m.Volume)
FROM mt5_positions m
WHERE m.Symbol LIKE 'EURUSD%' AND m.Login IN (SELECT u.Login FROM mt5_users u WHERE u."Group" LIKE 'real%') AND m."Action" = 0) AS TotalBuy,
(SELECT CASE
WHEN 'EURUSD' LIKE 'USOIL%' OR 'EURUSD' LIKE 'UKOIL%' OR 'EURUSD' LIKE 'Bund%' THEN ROUND(TotalBuy/10*Bid,2)
WHEN 'EURUSD' LIKE 'NGAS%' OR 'EURUSD' LIKE 'Copper%' THEN ROUND(TotalBuy/1*Bid,2)
ELSE ROUND(TotalBuy/100*Bid,2) END) AS ExpositionBuyLots当然，返回的错误是：
SQL (207)：无效列名“TotalBuy” 无效的列名‘出价’。
我的问题是：如何使用从列TotalBuy和Bid中计算出来的已经(或者可以说是“动态的”)？我读过有关计算列的文章，但不幸的是，修改数据库是不可能的。
2012版本。
编辑:以上功能在MySQL中是可用的，我希望在MySQL中也能实现这一点。如果有人对该解决方案感兴趣(我选择@Damien是因为它最接近原始的MySQL查询(是的，它是查询，从MySQL重写到MSSQL)，即只需要少量的SQL修改)：
SELECT * FROM
(SELECT 'EURUSD' AS symbol_name) symbol_name
CROSS apply
(SELECT TOP 1 ROUND(p.AskLast,5) AS Ask FROM mt5_prices p WHERE p.Symbol LIKE 'EURUSD%') Ask
CROSS apply
(SELECT TOP 1 ROUND(p.BidLast,5) AS Bid FROM mt5_prices p WHERE p.Symbol LIKE 'EURUSD%') Bid
CROSS apply
(SELECT SUM(m.Volume) AS TotalBuy
FROM mt5_positions m
WHERE m.Symbol LIKE 'EURUSD%' AND m.Login IN (SELECT u.Login FROM mt5_users u WHERE u."Group" LIKE 'real%') AND m."Action" = 0) TotalBuy
CROSS apply
(SELECT SUM(m.Volume) AS TotalSell
FROM mt5_positions m
WHERE m.Symbol LIKE 'EURUSD%' AND m.Login IN (SELECT u.Login FROM mt5_users u WHERE u."Group" LIKE 'real%') AND m."Action" = 1) TotalSell
CROSS apply
(SELECT (CASE
WHEN 'EURUSD' LIKE 'USOIL%' OR 'EURUSD' LIKE 'UKOIL%' OR 'EURUSD' LIKE 'Bund%' THEN ROUND(TotalBuy/10*Bid,2)
WHEN 'EURUSD' LIKE 'NGAS%' OR 'EURUSD' LIKE 'Copper%' THEN ROUND(TotalBuy/1*Bid,2)
ELSE ROUND(TotalBuy/100*Bid,2) END) AS ExpositionBuyLots) ExpositionBuyLots回答 3
Stack Overflow用户
回答已采纳
发布于 2017-05-31 09:50:09
您可以使用cross apply (或子查询)引入额外的select子句。每个select子句的计算“就好像”所有表达式都是并行计算的--这就是为什么在单个select子句中不能在表达式之间存在依赖关系的原因。
例如：
select
*
from
sys.objects
cross apply
(select object_id + 10 as mega_object_id) u
cross apply
(select mega_object_id / 30 as little_object_id) wStack Overflow用户
发布于 2017-05-31 09:49:42
您可以使用另一个子查询块来实现这一点：
SELECT (
CASE
WHEN 'EURUSD' LIKE 'USOIL%'
OR 'EURUSD' LIKE 'UKOIL%'
OR 'EURUSD' LIKE 'Bund%'
THEN ROUND(TotalBuy / 10 * Bid, 2)
WHEN 'EURUSD' LIKE 'NGAS%'
OR 'EURUSD' LIKE 'Copper%'
THEN ROUND(TotalBuy / 1 * Bid, 2)
ELSE ROUND(TotalBuy / 100 * Bid, 2)
END
) AS ExpositionBuyLots, *
FROM (
SELECT ( SELECT 'EURUSD' AS a ) AS symbol_name
,( SELECT TOP 1 ROUND(p.BidLast, 5)
FROM mt5_prices p
WHERE p.Symbol LIKE 'EURUSD%'
) AS Bid
,( SELECT SUM(m.Volume)
FROM mt5_positions m
WHERE m.Symbol LIKE 'EURUSD%'
AND m.LOGIN IN (
SELECT u.LOGIN
FROM mt5_users u
WHERE u."Group" LIKE 'real%'
)
AND m."Action" = 0
) AS TotalBuy
) AS RStack Overflow用户
发布于 2017-05-31 10:00:02
您应该使用APPLY操作符。可以在YourTable中使用APPLY中的值。
SELECT
ExpositionBuyLots = CASE
WHEN 'EURUSD' LIKE 'USOIL%' OR 'EURUSD' LIKE 'UKOIL%' OR 'EURUSD' LIKE 'Bund%' THEN ROUND(ComputedValues.TotalBuy/10*ComputedValues.Bid,2)
WHEN 'EURUSD' LIKE 'NGAS%' OR 'EURUSD' LIKE 'Copper%' THEN ROUND(ComputedValues.TotalBuy/1*ComputedValues.Bid,2)
ELSE ROUND(ComputedValues.TotalBuy/100 * ComputedValues.Bid,2) END
FROM YourTable
OUTER APPLY (
SELECT
symbol_name = 'EURUSD',
Bid = (SELECT TOP 1 ROUND(p.BidLast,5) FROM mt5_prices p WHERE p.Symbol LIKE 'EURUSD%') ,
TotalBuy = (SELECT SUM(m.Volume)
FROM mt5_positions m
WHERE m.Symbol LIKE 'EURUSD%' AND m.Login IN (SELECT u.Login FROM mt5_users u WHERE u."Group" LIKE 'real%') AND m."Action" = 0
)
) ComputedValues页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44281256
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号