平均查询ORA-00936错误

Question

SQL> SELECT consignmentNo, VoyageNo, Weight
  2  (SELECT (AVG(WEIGHT) FROM consignment), AS AVERAGE,
  3  WHERE Weight = 650,
  4  FROM consignment;
(SELECT (AVG(WEIGHT) FROM consignment), AS AVERAGE,
 *
ERROR at line 2:
ORA-00936: missing expression

特定船舶的平均重量，也列出了特定船舶的托运，无法识别错误

Answer 1

你只是在找group by吗？

SELECT VoyageNo, AVG(Weight)
FROM consignment
GROUP BY VoyageNo;

如果你想要平均值和详细信息，你需要一个窗口函数：

SELECT c.*, AVG(Weight) OVER (PARTITION BY VoyageNo)
FROM consignment c;

这里假设您所说的ship就是VoyageNo。

Answer 2

您的查询中有一个额外的, (在AS AVERAGE之前)，并且在Weight之后缺少一个,。此外，from和where的顺序也不对。试试这个：

SELECT consignmentNo, VoyageNo, Weight,
(SELECT (AVG(WEIGHT) FROM consignment) AS AVERAGE,
FROM consignment
WHERE Weight = 650;

Answer 3

你似乎想要：

SELECT consignmentNo, VoyageNo, Weight, avg.AVERAGE 
FROM consignment CROSS JOIN 
     (SELECT AVG(WEIGHT) AS AVERAGE FROM consignment) avg
WHERE Weight = 650;