select distinct tmd.*,tmsc.Customer_Code,tmsc.Customer_Name,tmsc.ContractBeginDate,tmsc.ContractEndDate,tmsc.Brand_Id,
tmsc.ShoppeContract_Id 
from T_Mall_DecorateContract as tmd inner join T_Mall_ShoppeContract as tmsc on  
tmd.Customer_Id=tmsc.Customer_Id
where (DecorateContract_Id in ('40d20697-7d1c-4d4e-a62f-8a3b4010810f','b18c47c4-f50a-460b-aa03-1515140c49ec') )  
AND tmsc.ShoppeContract_Id in (
select MAX(ShoppeContract_Id) as ShoppeContract_Id
from T_Mall_ShoppeContract 
where Customer_Id in(select Customer_Id from T_Mall_Customer) group by Customer_Id
)

------解决方案--------------------
AND tmsc.ShoppeContract_Id in (select MAX(ShoppeContract_Id) as ShoppeContract_Id from T_Mall_ShoppeContract where
 Customer_Id in(select Customer_Id from T_Mall_Customer) group by Customer_Id order by ContractEndDate desc)  

你这么一段是子查询，语句没有编译通过吧！order 附近有语法错误什么的，这个必须要去掉，排序放到最后，不要放在in(...)的括号中。

select *
from ...
where ... and [] in ()
order by ...

------解决方案--------------------
怎么在最后一个嵌套的字句中排序啊，你应该是要直接放最后排序的吧
select distinct tmd.*,tmsc.Customer_Code,tmsc.Customer_Name,tmsc.ContractBeginDate,
tmsc.ContractEndDate,tmsc.Brand_Id,tmsc.ShoppeContract_Id  
from T_Mall_DecorateContract as tmd inner join T_Mall_ShoppeContract as tmsc on 
tmd.Customer_Id=tmsc.Customer_Id  
where  
(DecorateContract_Id in ('40d20697-7d1c-4d4e-a62f-8a3b4010810f','b18c47c4-f50a-460b-aa03-1515140c49ec') ) 
AND tmsc.ShoppeContract_Id in  
(select MAX(ShoppeContract_Id) as ShoppeContract_Id  
from T_Mall_ShoppeContract  
where Customer_Id in (select Customer_Id from T_Mall_Customer) group by Customer_Id)
order by tmsc.ContractEndDate desc