MySQL查询中CROSS JOIN和引用的问题、字段列表错误

发布于 2024-08-22 10:14:26 字数 751 浏览 12 评论 0原文

我正在尝试运行以下 MySQL 查询:

mysql> SELECT num.value, agro.mean, agro.dev
    -> FROM randomNumbers num
    -> CROSS JOIN (
    ->         SELECT AVG(value) AS mean, STDDEV(value) AS dev 
    ->         FROM randomNumbers
    ->     ) agro
    -> ORDER BY num.value;

示例来自此处 http://www.sitecrafting.com/blog/stats-in-mysql-pt-outliers/,randomNumbers 只是一个随机数列表。

我收到错误:错误 1054 (42S22):“字段列表”中的未知列“num.value”。当我尝试调试它时,我意识到我不知道“agro”在做什么。我认为它允许我使用前缀 agro 来引用平均值和 dev,但这并没有真正意义,我不知道为什么该语句不起作用。这句话很有效:

mysql> select num.value from randomNumbers num;

你能帮忙吗?谢谢。

I'm trying to run the following MySQL query:

mysql> SELECT num.value, agro.mean, agro.dev
    -> FROM randomNumbers num
    -> CROSS JOIN (
    ->         SELECT AVG(value) AS mean, STDDEV(value) AS dev 
    ->         FROM randomNumbers
    ->     ) agro
    -> ORDER BY num.value;

the example came from here http://www.sitecrafting.com/blog/stats-in-mysql-pt-outliers/, randomNumbers is just a list of random numbers.

I'm getting the error: ERROR 1054 (42S22): Unknown column 'num.value' in 'field list'. When I try to debug it, I realized that I don't know what 'agro' is doing. I presume it is allowing me to reference the mean and dev with the prefix agro, but it doesn't really make sense and I don't know why the statement is not working. This statement works fine:

mysql> select num.value from randomNumbers num;

Can you help? Thanks.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

王权女流氓 2024-08-29 10:14:26

agro 是子选择创建的结果集的别名 - 这是位于 FROM 子句中的子选择的要求,以便可以正确引用列。我不确定为什么您的查询无法正常工作。您是否尝试过单独运行子选择?

SELECT AVG(value) AS mean, STDDEV(value) AS dev FROM randomNumbers;

这可能不会解决任何问题,但请尝试在别名之前添加 AS

SELECT num.value, agro.mean, agro.dev
FROM randomNumbers AS num
    CROSS JOIN (
        SELECT AVG(value) AS mean, STDDEV(value) AS dev 
        FROM randomNumbers
    ) AS agro
ORDER BY num.value;

agro is an alias for the result set created by the subselect - this is a requirement for subselects located in the FROM clause so that the columns can be referenced properly. I'm not sure why you're query isn't working correctly. Have you tried running the subselect by itself?

SELECT AVG(value) AS mean, STDDEV(value) AS dev FROM randomNumbers;

This probably won't fix anything but try adding AS before your aliases.

SELECT num.value, agro.mean, agro.dev
FROM randomNumbers AS num
    CROSS JOIN (
        SELECT AVG(value) AS mean, STDDEV(value) AS dev 
        FROM randomNumbers
    ) AS agro
ORDER BY num.value;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文