列XXXXX'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中

This query is in the ColdFusion, and it can be run for the old version ColdFusion, but can't run this query right now. Just reminds me Column "TUser.LastName" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

此查询位于ColdFusion中,可以为旧版本ColdFusion运行,但现在无法运行此查询。只是提醒我列“TUser.LastName”在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中

     <CFQUERY name="getUserList" datasource="#REQUEST.dsn#" maxrows="1">
        SELECT 
            TUser.userID as user_ID,
            Min(TUser.agencyID) as agencyID,
            Min(TUser.servicing_EntityID) as servicing_EntityID,
            Min(Tuser.userid) AS userID, 
            Min(Tuser.lastname) AS lastName, 
            Min(Tuser.firstname) AS firstName, 
            Min(Tuser.isSecondary) AS isSecondary, 
            Min(Tuser.corporate_ID) AS corporate_ID, 
            Min(Tuser.city) AS city, 
            Min(TUser.address1) AS address1,
            Min(Corporate_Client.client_name) AS client_name, 
            Min(TLocationProvState.abrev) AS abrev, 
            TUser_Loyalty.loyalty_ID AS loyalty_ID
        FROM TUser
        LEFT JOIN TLocationProvState ON (Tuser.provinceID = TLocationProvState.PROVSTATEID)
        LEFT JOIN Corporate_Client ON (Tuser.corporate_ID = Corporate_Client.client_ID)
        LEFT JOIN TUser_Loyalty ON (Tuser.userid = TUser_Loyalty.userID)
        LEFT JOIN TLoyalty ON (TUser_Loyalty.loyalty_ID = TLoyalty.loyalty_ID)
        WHERE (0=1)
        GROUP BY TUser.UserID, TUser_Loyalty.loyalty_ID
        ORDER BY TUser.LastName, TUser.FirstName, TUser.User_ID
        </CFQUERY>

2 个解决方案

#1

Your order by is invalid, Use the alias of the columns instead of the min name's

您的订单无效,请使用列的别名而不是最小名称

    WHERE (0=1)
    GROUP BY TUser.UserID, TUser_Loyalty.loyalty_ID
    ORDER BY LastName, FirstName, User_ID

Also odd... in order by you have Tuser.user_ID but select shows TUser.userID (no underscore)

也奇怪...按顺序你有Tuser.user_ID但是选择显示TUser.userID(没有下划线)

order of operation in the SQL has the order by generated after the select, thus column aliases are available.

SQL中的操作顺序具有在select之后生成的顺序,因此列别名可用。

#2

You are attempting to ORDER by fields that have not been designated as group fields. The fields will have to be returned in order to order by, therefore, the Query Executor needs to know how to handle all fields in a grouped statement.

您正尝试按未指定为组字段的字段进行排序。必须返回字段才能按顺序排序,因此,查询执行程序需要知道如何处理分组语句中的所有字段。

GROUP BY TUser.UserID, TUser_Loyalty.loyalty_ID, TUser.LastName, TUser.FirstName
ORDER BY TUser.User_ID, TUser.LastName, TUser.FirstName

GROUP BY TUser.UserID, TUser_Loyalty.loyalty_ID
ORDER BY TUser.User_ID, MIN(TUser.LastName), MIN(TUser.FirstName)

#1