sql - ORDER BY 子句中的列无效，因为它未包含在聚合函数或 GROUP BY 子句中

Question

当我尝试使用 ORDER BY 子句时，SELECT 查询出现错误。当我删除 ORDER BY 时，它工作正常。

这是我的查询:

Select  tbl_User.CompanyName as CompanyName,
    tbl_User.ResellerName as ResellerName,
    tbl_New_Web_Amc.Domain_Name,
    tbl_Website_Type.Website_type as [Type],
    convert(varchar,Web_Amc_Start_Date,106) as StartDate,
    convert(varchar,Web_Amc_End_Date,106) as EndDate,
    tbl_New_Web_Amc.Web_Amc_Amount as Amount,
    tbl_Billing.CompanyName as BillingCompany,
    tbl_Billing.ContactPerson1,
    tbl_Billing.Mobile1,
    tbl_Billing.ContactPerson2,
    tbl_Billing.Mobile2,
    tbl_Billing.ContactPerson3,
    tbl_Billing.Mobile3,
    tbl_Billing.Telephone1,
    tbl_Billing.Telephone2,
    tbl_Billing.Telephone3,
    tbl_Billing.EmailId1,
    tbl_Billing.EmailId2,
    tbl_Billing.EmailId3
from tbl_New_Web_Amc 
   join tbl_User on tbl_New_Web_Amc.Customer_Id=tbl_User.UserId 
   join tbl_Domain_Details on tbl_New_Web_Amc.Customer_Id=tbl_Domain_Details.Customer_Id
   join tbl_Billing on tbl_New_Web_Amc.Customer_Id=tbl_Billing.User_ID
   join tbl_Website_Type on tbl_Website_Type.Type_Id=tbl_New_Web_Amc.Web_Amc_Site_Type
   where WebsiteAmc_Id is not null
group by tbl_User.CompanyName ,
    tbl_User.ResellerName ,
    tbl_New_Web_Amc.Domain_Name,
    tbl_Website_Type.Website_type,
    convert(varchar,Web_Amc_End_Date,106),
    convert(varchar,Web_Amc_Start_Date,106),
    tbl_New_Web_Amc.Web_Amc_Amount,
    tbl_Billing.CompanyName,
    tbl_Billing.ContactPerson1,
    tbl_Billing.ContactPerson2,
    tbl_Billing.ContactPerson3,
    tbl_Billing.Telephone1,
    tbl_Billing.Telephone2,
    tbl_Billing.Telephone3,
    tbl_Billing.Mobile1,
    tbl_Billing.Mobile2,
    tbl_Billing.Mobile3,
    tbl_Billing.EmailId1,
    tbl_Billing.EmailId2,
    tbl_Billing.EmailId3
    order by convert(datetime,Web_Amc_End_Date,106)

出现以下错误

Column "tbl_New_Web_Amc.Web_Amc_End_Date" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

我错在哪里。怎么纠正？？请帮忙。

Answer 1

ORDER BY 命令也要求在选择查询中出现确切的列，因为根据 SQL Server 中的顺序，order by 命令在 SELECT 命令之后执行。根据我在您的查询中收集到的信息，您使用了:

convert(varchar,Web_Amc_End_Date,106)

在选择子句中，和

convert(datetime,Web_Amc_End_Date,106)

在 order by 子句中。

考虑将它们更改为相同的格式，您的查询将起作用