SQL查询在Oracle数据库上给出错误

I'm trying to write a query to list accounts along with current balance, branch no to which it belongs and the average balance of that branch having a balance more than a average balance of the branch to which the account belongs.

我正在尝试编写一个查询来列出帐户以及当前余额,它所属的分支号以及该分支的平均余额超过帐户所属分支的平均余额。

The table name is acmaster, columns are acno, currbal, and branchno.

表名是acmaster,列是acno,currbal和branchno。

Records:

acno currbal branchno
1    1000     2
2    2000     2
3    3000     3
4    2000     3

I am expecting result like this:

我期待这样的结果:

acno  currbal  branchno   AVG(currbal)
2     2000      2           1500
3     3000      3           2500

This is my current query:

这是我目前的查询:

select acno,currbal,branchno,AVG(currbal) abal
from acmaster 
group by branchno as bno
having abal < (select currbal from acmaster group by (select branchno from acmaster where branchno=bno));

which gets error:

得到错误:

ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 3 Column: 19

2 个解决方案

#1

The immediate cause of the error you're getting, as FoxCy mentioned, is the presence of as bno in your group-by clause. The error message is trying to help you there: it refers to line 3 column 19, which is the start of that. SQL Developer even underlines it in red to tell you it's wrong, and I imagine other clients will do something similar.

正如FoxCy所提到的,你得到的错误的直接原因是你的group-by子句中存在as bno。错误消息正试图帮助您:它引用第3行第19列,这是它的开始。 SQL Developer甚至用红色强调它告诉你这是错误的,我想其他客户会做类似的事情。

But you also have columns in your select list which are not aggregates and are not in the group-by; you can't refer to the abal alias in the same level of query it was defined - you would have to do having avg(currbal) > ... - and by the same token if you added an alias for branchno in the select list instead you couldn't then refer to that either.

但是,您的选择列表中的列也不是聚合的,不在分组中;你不能在定义的同一级别的查询中引用abal别名 - 你必须有avg(currbal)> ... - 并且如果你在选择列表中为branchno添加了别名相反,你也不能参考那个。

Your having clause doesn't make much sense though; the subquery you're trying to compare that average against will get multiple rows, and has an invalid subquery in its own group-by clause. At this point it's probably simpler to start again...

你的条款虽然没有多大意义;您尝试比较该平均值的子查询将获得多行,并且在其自己的group-by子句中具有无效子查询。在这一点上,重新开始可能更简单......

A simplistic way to get the result you want is with something like:

获得所需结果的简单方法是:

select a1.acno, a1.currbal, a1.branchno, (
    select avg(a2.currbal)
    from acmaster a2
    where a2.branchno = a1.branchno
    group by a2.branchno
  ) as abal
from acmaster a1
where a1.currbal > (
    select avg(a3.currbal)
    from acmaster a3
    where a3.branchno = a1.branchno
    group by a3.branchno
  );

      ACNO    CURRBAL   BRANCHNO       ABAL
---------- ---------- ---------- ----------
         2       2000          2       1500
         3       3000          3       2500

The same subquery is repeated as a column expression in the select list, and in the where clause, which is messy, repetitive, hard to maintain, and hard to follow.

相同的子查询将作为选择列表中的列表达式重复,并在where子句中重复,该子句是混乱的,重复的,难以维护的,并且难以遵循。

A slightly better option is to get the average once in an inline view and then filter the results from that:

稍微好一点的选择是在内联视图中获取平均值一次,然后从中过滤结果:

select * from (
  select a1.acno, a1.currbal, a1.branchno, (
    select avg(a2.currbal)
    from acmaster a2
    where a2.branchno = a1.branchno
    group by a2.branchno) as abal
  from acmaster a1
)
where currbal > abal;

Notice that you can now refer to the abal alias, as it is being used in a different level of query to where it was defined.

请注意,您现在可以引用abal别名,因为它在不同级别的查询中用于定义它的位置。

Oracle also has analytic/window functions to simplify that even more, so you don't need the subquery; if you do:

Oracle还具有分析/窗口功能,可以进一步简化,因此您不需要子查询;如果你这样做:

select a.acno, a.currbal, a.branchno,
  avg(a.currbal) over (partition by a.branchno) as abal
from acmaster a;

      ACNO    CURRBAL   BRANCHNO       ABAL
---------- ---------- ---------- ----------
         1       1000          2       1500
         2       2000          2       1500
         3       3000          3       2500
         4       2000          3       2500

you can see the average for every branch against each row again, and can filter that as an inline view instead:

您可以再次看到每个分支对每行的平均值,并可以将其过滤为内联视图:

select * from (
  select a.acno, a.currbal, a.branchno,
    avg(a.currbal) over (partition by a.branchno) as abal
  from acmaster a
)
where currbal > abal;

      ACNO    CURRBAL   BRANCHNO       ABAL
---------- ---------- ---------- ----------
         2       2000          2       1500
         3       3000          3       2500

#2

Lets look at this a line at a time:

让我们一次看一下这一行:

1> select acno,currbal,branchno,AVG(currbal) abal
2> from acmaster 
3> group by branchno as bno 
4> having abal < (select currbal from acmaster group by (select branchno from acmaster where branchno=bno));

On line 3, you are trying to alias branchno as bno within the group by statement. You can't do that, at least not in oracle.

在第3行,您试图在group by语句中将branchno别名为bno。你不能这样做,至少不是在oracle中。

On line 1, you reference columns acno and currbal without any form of aggregate function, but they don't exist in the group by clause on line 3. Either add an aggregate function to each of these columns, or add them to the group by clause

在第1行,您引用了没有任何形式的聚合函数的列acno和currbal,但它们不存在于第3行的group by子句中。要么将聚合函数添加到每个列中,要么将它们添加到组中条款

In line 4 you try referencing abal from line 1, but in oracle you can't reference column aliases in the having clause, so use the aggregate AVG(currbal) instead. Additionally Oracle does not allow subqueries in the group by clause, however, the gist of what you appear to be doing in line 4 is ensuring that AVG(currbal) is less than some currbal value for that branch, perhaps the MAX(currbal)?

在第4行中,您尝试从第1行引用abal,但在oracle中,您无法在having子句中引用列别名,因此请使用聚合AVG(currbal)。此外,Oracle不允许group by子句中的子查询,但是,您在第4行中看到的要点是确保AVG(currbal)小于该分支的某些currbal值,也许是MAX(currbal)?

Putting it all together this provides the answer you are looking for, though I'm not certain you even need the having clause, as it would eliminate records where the average balance equals the greatest balance (branchnos with a single record or where all records for the branchno have the same balance):

把这一切放在一起这提供了你正在寻找的答案,虽然我不确定你甚至需要有条款,因为它会消除平均余额等于最大余额的记录(具有单一记录的branchnos或所有记录的所有记录) branchno有相同的余额):

select max(acno) acno
     , max(currbal) currbal
     , branchno
     , AVG(currbal) abal
from acmaster a
group by branchno
having AVG(currbal) < MAX(currbal)

#1