英文:
What is the meaning of the subquery used in the order by clause of an SQL statement?
问题
The subquery used in the ORDER BY clause of an SQL statement retrieves the average (AVG) of the "amount" column from the "sales" table and orders the result set based on this average value.
英文:
What is the meaning of the subquery used in the order by clause of an SQL statement?
CREATE TABLE sales
(
id INT,
name VARCHAR(50),
amount INT
);
COMMIT;
INSERT INTO sales
VALUES (1, 'John', 100),
(2, 'Mary', 200),
(3, 'Tom', 300),
(4, 'Jane', 150),
(5, 'Peter', 250);
SELECT AVG(amount) FROM sales;
SELECT *
FROM sales
ORDER BY (SELECT AVG(amount) FROM sales);
答案1
得分: 1
一个ORDER BY子查询是有道理的，但它必须是相关的，也就是说，子查询必须引用要排序的表的一行。如果我们想要首先列出平均销售额最低的人，我们可以这样写：
SELECT *
FROM sales
ORDER BY (SELECT AVG(s.amount) FROM sales s WHERE s.name = sales.name),
name,
sales_date;
例如。
在你的示例中，子查询是不相关的。销售的平均金额是一个数字。你告诉DBMS对行进行排序，但使用同一个数字作为每一行的排序键。不会发生排序。
英文:
A subquery in an ORDER BY clause can make sense, but it must be correlated then, i.e. the subquery would have to refer to a row of the table that is to be sorted. If we wanted to have the persons with the lowest average amount first, we could write:
SELECT *
FROM sales
ORDER BY (SELECT AVG(s.amount) FROM sales s WHERE s.name = sales.name),
name,
sales_date;
for instance.
In your example, the subquery is not correlated. The average amount of sales is one number. You tell the DBMS to order the rows, but use that same number as a sortkey for every row. No sorting will take place.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论