问题

我必须获取产品名称和迄今为止订购该产品的总数量，以便将它们并排显示，以显示具有最大总订单数量的产品。

数据来自两个表（1）Sales_order和（2）Products

销售订单表结构：
ID（整数）
product（整数）
quantity_ordered（整数）
产品表结构：
Product_code（整数）
Product_line（Varchar（20））
它们通过Products.Product_code ->> Sales_order.product链接。

我的查询是：

select x.product_line, x.TotalQty from 	
    ( select P.Product_line, sum(S.quantity_ordered) as TotalQty 		
      from sales_order S 		
      join products P on S.product = P.product_code 		
      group by P.Product_line
     order by TotalQty desc 	
    ) x 
 where x.TotalQty = (select max(x.TotalQty) from x)

内部查询产生以下结果：

product_line    TotalQty
"Classic Cars"	33992
"Vintage Cars"	21069
"Motorcycles"	11663

但一旦我加上外部查询和'where'子句，就会出现以下错误：

错误：'select'附近的语法错误 LINE 8: where x.TotalQty = select max(x.TotalQty) from x

我还尝试了：

where x.TotalQty = (select max(x.TotalQty) from x)

通过在最后一个select周围加上括号，但这会导致错误，表明关系x不存在。

我是SQL新手，无法弄清楚我做错了什么以及如何纠正它。（要求不是“找到答案”，比如通过按降序排序并选择第一个项目。我应该编写代码，仅为最大值或与所有订单数量的平均值完全匹配的产品线和TotalQty一起显示）。

 select x.product_line, x.TotalQty from 	
    ( select P. Product_line,sum(S.quantity_ordered) as TotalQty 		
      from sales_order S 		
      join products P on S.product = P.product_code 		
      group by P. Product_line
     order by TotalQty desc 	
    ) x 
 where x.TotalQty = select max(x.TotalQty) from x

product_line    TotalQty
"Classic Cars"	33992
"Vintage Cars"	21069
"Motorcycles"	11663

where x.TotalQty = (select max(x.TotalQty) from x) 

答案1

得分: 1

A CTE will be a better a choice, as you can use the CTE as basis for your queries.

CREATE TABLE sales_order(quantity_ordered int, product int)

CREATE TABLE products (Product_line varchar(10), product_code int)

WITH CTE AS (SELECT P.Product_line, SUM(S.quantity_ordered) as TotalQty        
      FROM sales_order S        
      JOIN products P ON S.product = P.product_code         
      GROUP BY P.Product_line
      ORDER BY TotalQty DESC)
 SELECT x.Product_line, x.TotalQty FROM     
    CTE x
 WHERE x.TotalQty = (SELECT MAX(TotalQty) FROM CTE)

[fiddle](https://dbfiddle.uk/UI54pVig)

<details>
<summary>英文:</summary>

A CTE will be a better a choice, as you can use the CTE as basis for your queries.

CREATE tABLe sales_order(quantity_ordered int, product int)

CREATE tABLe products (Product_line varchar(10),product_code int)

WITH CTE AS (select P. Product_line,sum(S.quantity_ordered) as TotalQty
from sales_order S
join products P on S.product = P.product_code
group by P. Product_line
order by TotalQty desc)
select x.product_line, x.TotalQty from
CTE x
where x.TotalQty = (select max(TotalQty) from CTE)

| product\_line | totalqty |
|:-------------|---------:|
> ``` status
> SELECT 0
> ```

[fiddle](https://dbfiddle.uk/UI54pVig)


</details>