获取sql查询中的错误

Question

Problem ->选择大于10月4日平均值的所有订单。

表架构：

mysql> desc orders;
+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| ONUM  | int   | NO   | PRI | NULL    |       |
| AMT   | float | NO   |     | NULL    |       |
| ODATE | date  | NO   |     | NULL    |       |
| CNUM  | int   | YES  | MUL | NULL    |       |
| SNUM  | int   | YES  | MUL | NULL    |       |
+-------+-------+------+-----+---------+-------+

我已经为此尝试了许多查询(大约10个)，但仍然不能通过。我尝试过的一些查询：

select outer.onum, outer.amt from orders outer where outer.amt>any(select avg(o.amt) from orders o group by o.odate having o.odate='1996-10-04') and outer.odate<>'1996-10-04';
select outer.onum, outer.amt from orders as outer where outer.amt>any(select avg(o.amt) as 04_avg from orders o group by o.odate having o.odate='1996-10-04') and outer.odate<>'1996-10-04';

尽管如此，还是没有成功。有人能帮我吗？另外，谁能解释在子查询中聚合函数的用法，以及相关子查询和嵌套查询的正确用法。互联网也帮不上什么忙。谢谢。

Answer 1

您可以使用以下查询获得1996-10-04的平均值：

SELECT AVG(AMT) 
FROM orders
WHERE ODATE = '1996-10-04'

您可以在WHERE子句中使用上述查询，如下所示：

SELECT *
FROM orders
WHERE AMT > (
  SELECT AVG(AMT) 
  FROM orders
  WHERE ODATE = '1996-10-04'
) 

如果你想过滤掉1996-10-04的行，你可以添加：

AND ODATE <> '1996-10-04'

Answer 2

这也是可行的。更改了外部表的别名，并使用了“外部”以外的词。

select outer4.onum, outer4.amt from orders as outer4 where outer4.amt >any(select avg(o.amt) from orders o group by o.odate having o.odate='1996-10-04') and outer4.odate<>'1996-10-04';

select onum, amt from orders where amt >(select avg(o.amt) from orders o group by o.odate having o.odate='1996-10-04') and odate<>'1996-10-04';