ORDER BY子句中的列无效,因为它不包含在聚合函数或GROUP BY子句中
问题描述:
我在尝试使用ORDER BY
子句时在SELECT
查询中收到错误。当我删除ORDER BY
它工作正常。ORDER BY子句中的列无效,因为它不包含在聚合函数或GROUP 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)
收到以下错误
列“tbl_New_Web_Amc.Web_Amc_End_Date”是在ORDER BY子句中无效,因为它不是在聚合函数或包含GROUP BY子句。
我在哪里出错了。如何纠正它?请帮忙。
答
ORDER BY命令需要确切的列存在于选择查询,以及,按照自成在SQL Server中按顺序执行命令是在SELECT命令之后执行的。从我可以在查询中收集,你已经使用:
convert(varchar,Web_Amc_End_Date,106)
SELECT子句中
,并
convert(datetime,Web_Amc_End_Date,106)
在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 EndDate
+0
但我需要结束日期转换成特定的格式。以便它以正确的顺序显示结果。 – Navy
@marc_s我已经在集团采取的每列By子句 – Navy