如何让MS Access子查询无需输入即可显示所有Symbol的相关数据？

问题现状 #

当前查询依赖手动输入[Input]参数，仅能返回单个Symbol的汇总数据，无法自动展示所有Symbol的对应统计值（买入手数、卖出手数、净手数、买入利润、卖出利润、净利润）。原查询语句如下：

SELECT Symbol, 
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE [Trade Type] = "Buy" AND Symbol = [Input];) AS [Buy Lot], 
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE [Trade Type] = "Sell" AND Symbol = [Input];) AS [Sell Lot],
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE Symbol = [Input];) AS [Net Lots],
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE [Trade Type] = "Buy" AND Symbol = [Input];) AS [Buy Profits], 
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE [Trade Type] = "Sell" AND Symbol = [Input];) AS [Sell Profits], 
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE Symbol = [Input];) AS [Net Profits]
FROM [Mock Trades]
GROUP BY Symbol
HAVING Symbol = [Input];

解决方案 #

将子查询改为关联子查询，让子查询与主查询的Symbol字段关联，同时移除限制单个Symbol的HAVING条件和[Input]参数，即可自动统计所有Symbol的汇总数据：

SELECT DISTINCT mt.Symbol, 
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE [Trade Type] = "Buy" AND Symbol = mt.Symbol) AS [Buy Lot], 
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE [Trade Type] = "Sell" AND Symbol = mt.Symbol) AS [Sell Lot],
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE Symbol = mt.Symbol) AS [Net Lots],
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE [Trade Type] = "Buy" AND Symbol = mt.Symbol) AS [Buy Profits], 
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE [Trade Type] = "Sell" AND Symbol = mt.Symbol) AS [Sell Profits], 
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE Symbol = mt.Symbol) AS [Net Profits]
FROM [Mock Trades] AS mt;

关键修改点 #

• 主查询使用DISTINCT替代GROUP BY（或保留GROUP BY mt.Symbol），确保每个Symbol仅返回一行统计数据
• 子查询中用主查询的表别名mt.Symbol替换[Input]参数，实现按当前Symbol关联统计对应数据
• 移除HAVING Symbol = [Input]条件，取消单个Symbol的查询限制
• 移除子查询末尾的分号，适配Access关联子查询的语法要求

更高效的替代方案（条件聚合） #

使用IIF函数实现条件聚合，仅需扫描一次数据表，性能更优：

SELECT 
    Symbol,
    SUM(IIF([Trade Type] = "Buy", [Lot Size], 0)) AS [Buy Lot],
    SUM(IIF([Trade Type] = "Sell", [Lot Size], 0)) AS [Sell Lot],
    SUM([Lot Size]) AS [Net Lots],
    SUM(IIF([Trade Type] = "Buy", [Profit], 0)) AS [Buy Profits],
    SUM(IIF([Trade Type] = "Sell", [Profit], 0)) AS [Sell Profits],
    SUM([Profit]) AS [Net Profits]
FROM [Mock Trades]
GROUP BY Symbol;

内容的提问来源于stack exchange，提问作者Nikhar Ramlakhan