日期范围内商品销售额排行榜的SQL语句编写

一：涉及订单表，订单详细表

SQL:

SELECT productName '商品名称',SUM(price*number) '销售总额'

FROM t_orderdetail

WHERE orderID IN (SELECT id

FROM t_order

WHERE DATE(createdate)

BETWEEN '2014-01-01'

AND '2014-01-31')

GROUP BY productID

ORDER BY SUM(price*number) DESC

LIMIT 0,10

查询结果：

 

二：涉及订单表，订单详细表，商品表

功能：从商品表中获取商品相关信息， 从详细表中获取商品销售总额， 时间范围由订单表中的日期限制；按商品分组，按商品销售额降序排列，查询前十名。

SQL：

SELECT p.`name` 名称,p.`introduce` 简介,SUM(o.price*o.number) 总额

FROM t_product p

JOIN t_orderdetail o

ON p.`id` = o.`productID`

WHERE o.orderID IN (SELECT t_order.id

FROM t_order

WHERE DATE(t_order.createdate)

BETWEEN '2014-01-01' AND '2014-01-31')

GROUP BY o.productID

ORDER BY SUM(o.price*o.number) DESC

LIMIT 0,10

 

查询结果：

 

三：表

T_product:

 

 

T_order:

 

 

T_orderdetail:

 

四：页面显示排行