常用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

posted @ 2012-08-16 14:37  717806198  阅读(163)  评论(0)  编辑  收藏  举报