如果语句包含UNION，INTERSECT或EXCEPT运算符，则ORDER by items必须出现在选择列表中。

，[secondpartyen] ，[secondparty] ，[firstpartyen] ，[firstparty] ，[apartemetnnoen] ，[apartemetnno] ，[Bulidingnoen] ，[Bulidingno] ，[No] FROM [All_Vech]。[dbo]。[MALL_Residential] 其中CONVERT（date，amountdateen，103）< CONVERT（日期，GETDATE（），103） UNION SELECT [Spare6] ，[Spare5] ，[Spare4] ，[Spare3] ，[Spare2] ，[Spare1] ，[详情] ，[注意] ，[文件] ，[termsofpaymneten] ，[termsofpaymnet] ，[aprtmentstatusen] ，[aprtmentstatus] ，将（varchar，amountdateen，103）转换为amountdateen ，将（varchar，amountdate，103）转换为amountdate ，[amountleft] ， [已付款] ，[租金] ，[noofpayemtsen] ，[noofpayemts] ，[来源] ] ，[来源] ，[iddateen] ，[iddate] ，[idnumber] ，将（varchar，contractenen，103）转换为contractenen ，convert（varchar，cont racten，103）as contracten ，convert（varchar，contractstarten，103）作为contractstarten ，convert（varchar，contractstart，103）as contractstart ，[contractnoen] ，[contractno] ，[nationalityen] ，[国籍] ，[secondpartyen] ，[secondparty] ，[firstpartyen] ，[firstparty] ，[ apartemetnnoen] ，[apartemetnno] ，[Bulidingnoen] ，[Bulidingno] ，[No]来自[All_Vech]。[dbo]。[Shop_Reem] 其中CONVERT（date，amountdateen，103）< CONVERT（日期，GETDATE（），103） 按月订购（金额），年（金额） - --------------- ---------------->错误在这里 END 解决方案 您似乎至少有6个具有完全相同模式的表。这通常是您的数据库设计需要引起注意的明确信号。 从我所看到的所有这些信息可以（并且应该）存储在一个表。添加另一个指示BuildingType的列 - 它将包含诸如Safa，Sharfya，Commercial_Building之类的值 - 或者甚至更好地成为包含建筑类型详细信息的表的外键。 其次你不应该使用，其中CONVERT（date，amountdateen，103）< CONVERT（date，GETDATE（），103）使用 DATEDIFF [ ^ ]。在将信息传递回调用程序之前，您也不应该转换日期 - 允许GUI层解释DATE类型而不是可能不明确的字符串。 为了克服你的问题你可以在SELECT列表中包含Month（amountdateen）和YEAR（amountdateen），但解决数据库设计问题要好得多。 ORDER by amountdateen DESC Hello, I have error when I try order by, I am using union * ( i try to use order by date mm and YYYY to sort date from oldest to earlest)What I have tried:SELECT [Spare6] ,[Spare5] ,[Spare4] ,[Spare3] ,[Spare2] ,[Spare1] ,[details] ,[note] ,[documents] ,[termsofpaymneten] ,[termsofpaymnet] ,[aprtmentstatusen] ,[aprtmentstatus] ,convert(varchar,amountdateen,103) as amountdateen ,convert(varchar,amountdate,103) as amountdate ,[amountleft] ,[amountpaid] ,[rent] ,[noofpayemtsen] ,[noofpayemts] ,[sourceen] ,[source] ,[iddateen] ,[iddate] ,[idnumber] ,convert(varchar, contractenen, 103) as contractenen ,convert(varchar, contracten, 103) as contracten ,convert (varchar,contractstarten ,103)as contractstarten ,convert (varchar,contractstart ,103)as contractstart ,[contractnoen] ,[contractno] ,[nationalityen] ,[nationality] ,[secondpartyen] ,[secondparty] ,[firstpartyen] ,[firstparty] ,[apartemetnnoen] ,[apartemetnno] ,[Bulidingnoen] ,[Bulidingno] ,[No] FROM [All_Vech].[dbo].[Bulding_Safa] where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103) UNION SELECT [Spare6] ,[Spare5] ,[Spare4] ,[Spare3] ,[Spare2] ,[Spare1] ,[details] ,[note] ,[documents] ,[termsofpaymneten] ,[termsofpaymnet] ,[aprtmentstatusen] ,[aprtmentstatus] ,convert(varchar,amountdateen,103) as amountdateen ,convert(varchar,amountdate,103) as amountdate ,[amountleft] ,[amountpaid] ,[rent] ,[noofpayemtsen] ,[noofpayemts] ,[sourceen] ,[source] ,[iddateen] ,[iddate] ,[idnumber] ,convert(varchar, contractenen, 103) as contractenen ,convert(varchar, contracten, 103) as contracten ,convert (varchar,contractstarten ,103)as contractstarten ,convert (varchar,contractstart ,103)as contractstart ,[contractnoen] ,[contractno] ,[nationalityen] ,[nationality] ,[secondpartyen] ,[secondparty] ,[firstpartyen] ,[firstparty] ,[apartemetnnoen] ,[apartemetnno] ,[Bulidingnoen] ,[Bulidingno] ,[No] FROM [All_Vech].[dbo].[Bulding_Sharfya] where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103) UNION SELECT [Spare6] ,[Spare5] ,[Spare4] ,[Spare3] ,[Spare2] ,[Spare1] ,[details] ,[note] ,[documents] ,[termsofpaymneten] ,[termsofpaymnet] ,[aprtmentstatusen] ,[aprtmentstatus] ,convert(varchar,amountdateen,103) as amountdateen ,convert(varchar,amountdate,103) as amountdate ,[amountleft] ,[amountpaid] ,[rent] ,[noofpayemtsen] ,[noofpayemts] ,[sourceen] ,[source] ,[iddateen] ,[iddate] ,[idnumber] ,convert(varchar, contractenen, 103) as contractenen ,convert(varchar, contracten, 103) as contracten ,convert (varchar,contractstarten ,103)as contractstarten ,convert (varchar,contractstart ,103)as contractstart ,[contractnoen] ,[contractno] ,[nationalityen] ,[nationality] ,[secondpartyen] ,[secondparty] ,[firstpartyen] ,[firstparty] ,[apartemetnnoen] ,[apartemetnno] ,[Bulidingnoen] ,[Bulidingno] ,[No] FROM [All_Vech].[dbo].[Commercial_Building] where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103) UNION SELECT [Spare6] ,[Spare5] ,[Spare4] ,[Spare3] ,[Spare2] ,[Spare1] ,[details] ,[note] ,[documents] ,[termsofpaymneten] ,[termsofpaymnet] ,[aprtmentstatusen] ,[aprtmentstatus] ,convert(varchar,amountdateen,103) as amountdateen ,convert(varchar,amountdate,103) as amountdate ,[amountleft] ,[amountpaid] ,[rent] ,[noofpayemtsen] ,[noofpayemts] ,[sourceen] ,[source] ,[iddateen] ,[iddate] ,[idnumber] ,convert(varchar, contractenen, 103) as contractenen ,convert(varchar, contracten, 103) as contracten ,convert (varchar,contractstarten ,103)as contractstarten ,convert (varchar,contractstart ,103)as contractstart ,[contractnoen] ,[contractno] ,[nationalityen] ,[nationality] ,[secondpartyen] ,[secondparty] ,[firstpartyen] ,[firstparty] ,[apartemetnnoen] ,[apartemetnno] ,[Bulidingnoen] ,[Bulidingno] ,[No] FROM [All_Vech].[dbo].[Mall_Reem] where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103) UNION SELECT [Spare6] ,[Spare5] ,[Spare4] ,[Spare3] ,[Spare2] ,[Spare1] ,[details] ,[note] ,[documents] ,[termsofpaymneten] ,[termsofpaymnet] ,[aprtmentstatusen] ,[aprtmentstatus] ,convert(varchar,amountdateen,103) as amountdateen ,convert(varchar,amountdate,103) as amountdate ,[amountleft] ,[amountpaid] ,[rent] ,[noofpayemtsen] ,[noofpayemts] ,[sourceen] ,[source] ,[iddateen] ,[iddate] ,[idnumber] ,convert(varchar, contractenen, 103) as contractenen ,convert(varchar, contracten, 103) as contracten ,convert (varchar,contractstarten ,103)as contractstarten ,convert (varchar,contractstart ,103)as contractstart ,[contractnoen] ,[contractno] ,[nationalityen] ,[nationality] ,[secondpartyen] ,[secondparty] ,[firstpartyen] ,[firstparty] ,[apartemetnnoen] ,[apartemetnno] ,[Bulidingnoen] ,[Bulidingno] ,[No] FROM [All_Vech].[dbo].[MALL_Residential ] where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103)UNION SELECT [Spare6] ,[Spare5] ,[Spare4] ,[Spare3] ,[Spare2] ,[Spare1] ,[details] ,[note] ,[documents] ,[termsofpaymneten] ,[termsofpaymnet] ,[aprtmentstatusen] ,[aprtmentstatus] ,convert(varchar,amountdateen,103) as amountdateen ,convert(varchar,amountdate,103) as amountdate ,[amountleft] ,[amountpaid] ,[rent] ,[noofpayemtsen] ,[noofpayemts] ,[sourceen] ,[source] ,[iddateen] ,[iddate] ,[idnumber] ,convert(varchar, contractenen, 103) as contractenen ,convert(varchar, contracten, 103) as contracten ,convert (varchar,contractstarten ,103)as contractstarten ,convert (varchar,contractstart ,103)as contractstart ,[contractnoen] ,[contractno] ,[nationalityen] ,[nationality] ,[secondpartyen] ,[secondparty] ,[firstpartyen] ,[firstparty] ,[apartemetnnoen] ,[apartemetnno] ,[Bulidingnoen] ,[Bulidingno] ,[No] FROM [All_Vech].[dbo].[Shop_Reem] where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103) ORDER BY Month(amountdateen), YEAR(amountdateen) -------------------------------------> error is here END 解决方案 You appear to have at least 6 tables with exactly the same schema. This is usually a sure sign that your database design needs some attention.From what I can see all of this information could be (and should be) stored in a single table. Add another column that indications the "BuildingType" - it will contain values such as "Safa", "Sharfya", "Commercial_Building" - or even better be a foreign key to a table containing details of the building types.Secondly you should not use where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103) Use DATEDIFF[^]. You should also not be converting dates before passing information back to the calling program - allow the GUI layer to interpret a DATE type instead of a string that can be ambiguous.To get over your problem you could just include Month(amountdateen) and YEAR(amountdateen) in your SELECT lists but it would be far better to address the issues with your database design. ORDER by amountdateen DESC 这篇关于如果语句包含UNION，INTERSECT或EXCEPT运算符，则ORDER by items必须出现在选择列表中。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！