需要帮助连接多个表以提供每个表的最大数目吗? [英] Need help joining multiple tables to give the maximum number for each one?

问题描述

我正在一个项目上，需要创建一些join语句.我试图制作一个在三个不同的表中选择一列的最大值的方法.我在SELECT上遇到错误，提示选择在此服务器位置无效，期望FOR，LOCK，TABLE，VALUES，WITH，'('".不确定该怎么办，有什么建议吗?代码是在下面.

I'm working on a project and need to create a few join statements. I tried to make one that selects the maximum value of a column in three different tables. I'm getting an error on the SELECT that says "select is not valid at this server position, expecting FOR, LOCK, TABLE, VALUES, WITH, '('". Not sure what to do, any advice? Code is below.

SELECT MAX(cn.TotalDollarPerArea) as 'North Total Dollar per Area',
        MAX(cs.TotalDollarPerArea) as 'South Total Dollar per Area', 
        MAX(ncl.TotalDollarPerArea) as 'Non-Climate Total Dollar Per Area',
FROM ClimateNorth cn,
    JOIN ClimateSouth cs using (TotalDollarPerArea),
    JOIN NonClimate ncl using (TotalDollarPerArea);

推荐答案

只需使用子查询:

SELECT (SELECT MAX(cn.TotalDollarPerArea)
        FROM ClimateNorth cn
       ) as `North Total Dollar per Area`,
       (SELECT MAX(cs.TotalDollarPerArea)
        FROM ClimateSouth cs
       ) as `South Total Dollar per Area`,
       (SELECT MAX(ncl.TotalDollarPerArea)
        FROM NonClimate ncl
       ) as `Non-Climate Total Dollar Per Area`;

注意:仅对字符串和日期常量使用单引号.列别名有更好的转义符.

Note: Only use single quotes for string and date constants. There are better escape characters for column aliases.

这篇关于需要帮助连接多个表以提供每个表的最大数目吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！