我正在尝试使用Oracle的数据库。 我试图将同一行中的两列相加并在运行中输出总数。 但是，我似乎无法让它发挥作用。 这是我到目前为止的代码。
SELECT a.name , SUM(b.sequence + b.length) as total FROM ( SELECT a.name, a.sequence, b.length FROM tbl1 a, tbl2 b WHERE b.sequence = a.sequence AND a.loc <> -1 AND a.id='10201' ORDER BY a.location )内部查询有效，但我似乎无法使新查询和子查询一起工作。
这是我正在使用的示例表：
...[name][sequence][length]... ...['aa']['100000']['2000']... ... ...['za']['200000']['3001']...这是我想要的输出：
[name][ total ] ['aa']['102000'] ... ['za']['203001']非常感谢，谢谢！
I'm trying to play with Oracle's DB. I'm trying to sum two columns from the same row and output a total on the fly. However, I can't seem to get it to work. Here's the code I have so far.
SELECT a.name , SUM(b.sequence + b.length) as total FROM ( SELECT a.name, a.sequence, b.length FROM tbl1 a, tbl2 b WHERE b.sequence = a.sequence AND a.loc <> -1 AND a.id='10201' ORDER BY a.location )The inner query works, but I can't seem to make the new query and the subquery work together.
Here's a sample table I'm using:
...[name][sequence][length]... ...['aa']['100000']['2000']... ... ...['za']['200000']['3001']...And here's the output I'd like:
[name][ total ] ['aa']['102000'] ... ['za']['203001']Help much appreciated, thanks!
最满意答案
SUM()汇总行数。 而是用sequence + length替换它。
...或者如果sequence或length列中可能出现NULL值，请使用： COALESCE(sequence, 0) + COALESCE(length, 0) 。
或者，如果您的意图确实是产生运行总计（即聚合每个用户的所有总计和长度的总和），请在子查询结束后添加GROUP BY a.name 。
顺便说一句：您不应该引用子查询外部子查询中使用的内部别名。 一些数据库服务器允许它（我现在没有方便的访问Oracle服务器，所以我可以测试它），但这不是很好的做法。
我认为你所追求的是：
SELECT a.name, SUM(B.sequence + B.length) AS total FROM Tbl1 A INNER JOIN Tbl2 B ON B.sequence = A.sequence WHERE A.loc <> -1 AND A.id = 10201 GROUP BY a.name ORDER BY A.locationSUM() sums number across rows. Instead replace it with sequence + length.
...or if there is the possibility of NULL values occurring in either the sequence or length columns, use: COALESCE(sequence, 0) + COALESCE(length, 0).
Or, if your intention was indeed to produce a running total (i.e. aggregating the sum of all the totals and lengths for each user), add a GROUP BY a.name after the end of the subquery.
BTW: you shouldn't be referencing the internal aliases used inside a subquery from outside of that subquery. Some DB servers allow it (and I don't have convenient access to an Oracle server right now, so I can test it), but it's not really good practice.
I think what you are after is something like:
SELECT a.name, SUM(B.sequence + B.length) AS total FROM Tbl1 A INNER JOIN Tbl2 B ON B.sequence = A.sequence WHERE A.loc <> -1 AND A.id = 10201 GROUP BY a.name ORDER BY A.location更多推荐
发布评论