个人中心
gpt4 book ai didi

column "name" does not exist(列“名称”不存在)

转载 作者：bug小助手 更新时间：2023-10-24 18:54:07 25 4
gpt4 key购买 nike



I am having issue with ALIAS variable "distance" in my PSQL query. (*please ignore that I am using voutecount as distance)

我在PSQL查询中遇到了别名变量“Distance”的问题。(*请忽略我使用vouteccount作为距离)


SELECT rentalid, createdDate, votecount AS distance
FROM rental 
WHERE longitude=? AND latitude=?   
HAVING distance < 25 ORDER BY distance LIMIT 0 OFFSET 30

The error is "distance does not exist" but I have defined distance already so I cannot tell what the issue is.

错误是“距离不存在”，但我已经定义了距离，所以我不知道是什么问题。


nested exception is org.postgresql.util.PSQLException: ERROR: column "distance" does not exist
  Position: 107] with root cause


@Maimoona Abid Okay I am not getting any error but the actual SQL command I am running is as below and I am getting any empty resultset. I believe it is because the query is actually searching RENTAL Table for longitude and latitude that I give as parameter instead of using the parameter for calculating the distance.

@Maimoona Abid好的，我没有得到任何错误，但我运行的实际SQL命令如下所示，我得到任何空的结果集。我相信这是因为查询实际上是在RENTAL表中搜索我作为参数给出的经度和纬度，而不是使用参数来计算距离。


public List<RentalDto> selectRentalsByDistance(Double lon, Double lat) {
        var sql = """
                SELECT *
                FROM (
                    SELECT rentalid, createdDate, 
                    ( 3959 * acos( cos(radians(37) ) * cos( radians(latitude) ) * cos( radians(longitude) - radians(-122) )
                                    + sin( radians(37) ) * sin( radians(latitude) )) ) as distance
                    FROM rental
                    WHERE lon = ? AND lat = ?                                    
                ) AS subquery
                WHERE distance < 25
                ORDER BY distance
                LIMIT 30 OFFSET 0
                """;
        return jdbcTemplate.query(sql, rentalDtoRowMapper, new Object[] {lon, lat});
    }


更多回答

Except for ORDER BY clauses and specific cases for GROUP BY clauses, column aliases from the SELECT clause aren't visible to the rest of the query. Another problem is that conditions in the HAVING clause "must unambiguously reference a grouping column, unless the reference appears within an aggregate function or the ungrouped column is functionally dependent on the grouping columns" (from SELECT in the PostgreSQL documentation).

除了ORDER BY子句和GROUP BY子句的特定情况外，SELECT子句中的列别名对查询的其余部分不可见。另一个问题是，HAVING子句中的条件“必须明确地引用分组列，除非引用出现在聚合函数中，或者未分组的列在函数上依赖于分组列”(在PostgreSQL文档中来自SELECT)。

Is this the full real query? In the code you've shared, there isn't any aggregation clause, so it doesn't really make sense to use HAVING, it could be WHERE all the way down. In general, HAVING should repeat the full definition, e.g. HAVING COUNT(DISTINCT vote.id) > 25.

这是真正的完整问题吗？在您所共享的代码中，没有任何Aggregation子句，所以使用HAVING实际上没有任何意义，它可能一直向下。一般来说，HAVING应该重复完整的定义，例如具有COUNT(DISTINCT VOTE.ID)>25。

@ÁlvaroGonzález, I suspect that this is the full query since the error's location of 107 is consistent with the query as presented. A HAVING clause without GROUP BY is similar to using a non-windowed aggregate function in the SELECT list; i.e., all rows meeting the WHERE clause conditions form a single group. If the conditions in the HAVING clause are met, then a single row is returned; however, if the conditions are not met, then no row is returned.

@álvaroGonzález，我怀疑这是完整的查询，因为错误位置107与所给出的查询一致。不带GROUP BY的HAVING子句类似于在选择列表中使用非窗口化聚合函数；即，满足WHERE子句条件的所有行都形成一个组。如果满足HAVING子句中的条件，则返回一行；但是，如果不满足条件，则不返回任何行。

优秀答案推荐

Try this approach, first create a subquery that calculates the distance alias and then filter and order the results in the outer query.

尝试这种方法，首先创建一个计算距离别名的子查询，然后对外部查询中的结果进行过滤和排序。


SELECT *
FROM (
    SELECT rentalid, createdDate, votecount AS distance
    FROM rental 
    WHERE longitude = ? AND latitude = ?
) AS subquery
WHERE distance < 25
ORDER BY distance
LIMIT 30 OFFSET 0;

Hope it works :)

希望它能奏效：)



You get this error because you are using HAVING on an alias distance that you defined in the SELECT clause and this is not supported. So you have to use the WHERE clause instead of HAVING to filter rows based on the distance alias;

出现此错误是因为您正在SELECT子句中定义的别名距离上使用HAVING，而这是不受支持的。因此，您必须使用WHERE子句而不是HAVING来根据距离别名过滤行;


SELECT rentalid, createdDate, votecount AS distance
FROM rental 
WHERE longitude = ? AND latitude = ? AND votecount < 25
ORDER BY distance
LIMIT 0 OFFSET 30;

更多回答

Although this query addresses the reference to distance, it's a poor approach since It adds complexity in the form of an unnecesary subquery. A better approach is to use a simple query with AND votecount < 25 in the WHERE clause. Adding a subquery just to allow reference to an aliased column name doesn't improve clarity and actually increases the cognitive load on the reader since it becomes necessary to search the query to find the definition of distance.

尽管该查询解决了对距离的引用，但它是一种糟糕的方法，因为它以不必要的子查询的形式增加了复杂性。更好的方法是在WHERE子句中使用一个简单的查询，其中AND VOTECUNT<25。仅仅为了允许引用别名列名而添加子查询并不能提高清晰度，反而会增加读者的认知负担，因为必须搜索查询才能找到距离的定义。

25 4 0
文章推荐： MDX Distinct members(MDX不同成员)
文章推荐： How to get firebase notication dbhelper to adapter set?(如何获取Firebase通知的DBhelper到适配器集？)
文章推荐： How to create a bindinglist with dapper on vb.net(如何在vb.net上用Dapper创建绑定列表)
文章推荐： Trouble setting Attachments with C# MS Graph API(使用C#MS Graph API设置附件时出现问题)
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com