我有下面这样的表，我试图为获取日期范围数据以及价格总和编写单个查询。我的表定义如下
<<p> 销售表/strong>id_sale date time
1 2014-05-05 12.30 am
2 2014-05-06 10.30 am
3 2014-05-25 12.30 am
Sale Product表
id_sale_product id_sale price quantity id_product
1 1 10.00 1 1
2 1 20.00 1 2
3 2 20.00 3 5
4 3 20.00 4 6
我想按销售表中的日期进行筛选，并获得每个日期的价格*数量=总额的总和，例如2014-05-05、2014-05-06等
我试过下面的查询
$query = 'SELECT sp.`id_product_type`,sp.`id_product`,sp.`quantity`,sp.`price`,s.`date`
, SUM(IF(s.`date` BETWEEN "'.$datepickerFrom.'" AND "'.$datepickerTo.'",sp.`price` * sp.`quantity`,0)) AS A
FROM `'._DB_PREFIX_.'sale_product` sp
LEFT JOIN '._DB_PREFIX_.'sales s ON (sp.id_sale = s.id_sale)
WHERE (s.`date` BETWEEN "'.$datepickerFrom.'" AND "'.$datepickerTo.'")';
输出应该是:
Example: Date
01/01/2013 Rs.10000
02/01/2013 Rs.140000
03/01/2013 Rs.8000
Example: Month
January Rs. 2,00,000
Feburary Rs. 2,20,000
试试这个....
select a.date,sum(b.qty * b.price) As sum from sales As a, product As b where DATE_FORMAT(a.date,'%d-%m-%Y') between '27-05-2014' AND '28-05-2014' and a.id_sale=b.id_sale group by a.date