常用Sql查询

多表查询分组排序

1.select MenuDetails.Name,COUNT(LunchOrders.MenuDetailID) from MenuDetails,LunchOrders where (MenuDetails.LunchSupplierID='000000000000000001' or MenuDetails.LunchSupplierID='000000000000000022')and IsActive=1 and LunchOrders.CreateTime>'2012-2-12 15:17:14' and LunchOrders.CreateTime<'2012-3-13 15:17:14' and LunchOrders.MenuDetailID=MenuDetails.ID and LunchOrders.OrderState=2 group by MenuDetails.Name order by COUNT(LunchOrders.MenuDetailID) desc

查询最近一个月数据

2.select MenuDetails.Name,COUNT(LunchOrders.MenuDetailID) from MenuDetails,LunchOrders where (MenuDetails.LunchSupplierID='000000000000000001' or MenuDetails.LunchSupplierID='000000000000000022')and IsActive=1 and datediff(dd,LunchOrders.CreateTime,getDate())<=30 and LunchOrders.MenuDetailID=MenuDetails.ID and LunchOrders.OrderState=2 group by MenuDetails.Name order by COUNT(LunchOrders.MenuDetailID) desc