mysql 将一个表除以在同一查询中创建的另一个表

aurhwmvo 于 2022-11-28 发布在 Mysql

关注(0)|答案(2)|浏览(168)

我有以下SQL查询：

SELECT `NeighbourhoodName`,
count(NAME) as `Number of Parks`,
sum(CASE 
    WHEN `parks`.`Advisories` = 'Y' THEN 1
    ELSE 0 
END) as Advisories,
FROM parks
GROUP BY `NeighbourhoodName`;

在代码的第二行中，我创建了一个名为“公园数量”的列。我希望下一列（咨询）中的所有值都除以“公园数量”中的值。但是，当我尝试在该列后插入除法语句时，如下所示：

SELECT `NeighbourhoodName`,
count(NAME) as `Number of Parks`,
sum(CASE 
    WHEN `parks`.`Advisories` = 'Y' THEN 1
    ELSE 0 
END)/`Number of Parks` as Advisories
FROM parks
GROUP BY `NeighbourhoodName`;

出现以下错误：

Unknown column, `Number of Parks` in field list.

如何执行此除法，同时仍将其保留在一个查询中？

mysql

来源：https://stackoverflow.com/questions/74552158/divide-one-table-by-another-table-created-in-the-same-query

2条答案

按热度按时间

lvmkulzt1#

您需要使用外部查询来进行除法运算。

SELECT `NeighbourhoodName`,
       `Number of Parks`,
        Advisories/`Number of Parks` as Advisories
FROM    ( SELECT `NeighbourhoodName`,
                 count(NAME) as `Number of Parks`,
                 sum( CASE  WHEN `parks`.`Advisories` = 'Y' THEN 1 ELSE 0 END ) as Advisories
          FROM parks
          GROUP BY `NeighbourhoodName`
         ) as tbl;

Problems with Column Aliases
别名可用于查询选择列表中，以便为列指定不同的名称。可以在GROUP BY、ORDER BY或HAVING子句中使用别名来引用列。

赞(0）回复(0）举报 2022-11-28

r7s23pms2#

或者使用count(Name)的表达式代替Number of Parks：

select NeighbourhoodName,
       count(Name)      as `Number of Parks`,
       sum(case when Advisories='Y' then 1 else 0 end)
       /count(Name)     as Advisories
  from parks
 group by NeighbourhoodName;

赞(0）回复(0）举报 2022-11-28

我来回答

mysql 将一个表除以在同一查询中创建的另一个表

2条答案

相关问题

热门标签

最新问答