SELECT a.金额 * ISNULL
((SELECT TOP 1 汇率比例
FROM 汇率表 c
WHERE c.货币A类型 = a.货币类型 AND
货币B类型 = b.客户表货币类型
ORDER BY 更新日期 DESC), 1) AS EXPR1
FROM 总货币储存表 a INNER JOIN
客户表 b ON b.客户索引 = a.货币储存表索引
WHERE (a.货币储存表索引 = '{F09561DA-7F64-44D6-98AE-03CA33916D1F}')上面是段SQL语句,输出的结果如下,已经转换成客户需要的货币等值
EXPR1
100
200
2400
3200
这是多行的结果,怎么加起来呢?
用下面这种方法,加个SUM,则SQL无法执行,返回错误!!!!!
____
SELECT SUM(a.金额 * ISNULL
((SELECT TOP 1 汇率比例
FROM 汇率表 c
WHERE c.货币A类型 = a.货币类型 AND
货币B类型 = b.客户表货币类型
ORDER BY 更新日期 DESC), 1)) AS EXPR1
FROM 总货币储存表 a INNER JOIN
客户表 b ON b.客户索引 = a.货币储存表索引
WHERE (a.货币储存表索引 = '{F09561DA-7F64-44D6-98AE-03CA33916D1F}')
FROM 总货币储存表 a INNER JOIN
客户表 b ON b.客户索引 = a.货币储存表索引
INNER JOIN 汇率表 C
ON c.货币A类型 = a.货币类型 AND C.货币B类型 = b.客户表货币类型
WHERE (a.货币储存表索引 = '{F09561DA-7F64-44D6-98AE-03CA33916D1F}')
AND C.更新日期 = (SELECT MAX(更新日期) FROM 汇率表
WHERE 货币A类型 = a.货币类型 AND C.货币B类型 = b.客户表货币类型)
SELECT a.金额 * ISNULL
((SELECT TOP 1 汇率比例
FROM 汇率表 c
WHERE c.货币A类型 = a.货币类型 AND
货币B类型 = b.客户表货币类型
ORDER BY 更新日期 DESC), 1) AS EXPR1
FROM 总货币储存表 a INNER JOIN
客户表 b ON b.客户索引 = a.货币储存表索引
WHERE (a.货币储存表索引 = '{F09561DA-7F64-44D6-98AE-03CA33916D1F}')SELECT SUM(EXPR1) FROM #TDROP TABLE #T
(
SELECT a.金额 * ISNULL
((SELECT TOP 1 汇率比例
FROM 汇率表 c
WHERE c.货币A类型 = a.货币类型 AND
货币B类型 = b.客户表货币类型
ORDER BY 更新日期 DESC), 1) AS EXPR1
FROM 总货币储存表 a INNER JOIN
客户表 b ON b.客户索引 = a.货币储存表索引
WHERE (a.货币储存表索引 = '{F09561DA-7F64-44D6-98AE-03CA33916D1F}')
) as t
select sum(字段) as 显示内容 from
(
SELECT a.金额 * ISNULL
((SELECT TOP 1 汇率比例
FROM 汇率表 c
WHERE c.货币A类型 = a.货币类型 AND
货币B类型 = b.客户表货币类型
ORDER BY 更新日期 DESC), 1) AS EXPR1
FROM 总货币储存表 a INNER JOIN
客户表 b ON b.客户索引 = a.货币储存表索引
WHERE (a.货币储存表索引 = '{F09561DA-7F64-44D6-98AE-03CA33916D1F}')
) m