Mysql的两个表

Question

这是得到SUM和COUNT值我的表

表1：订单

id total order_date 
1 200 2016-04-22 
2 300 2016-04-22 

表2：order_products

id order product_id qty 
    1 1  1  2 
    2 1  2  1 
    3 2  2  2 
    4 2  1  2 

而我的结果应该be

tot_order total_amount prd_qty 
2  500    7 

和我的查询是

SELECT COUNT(ddo.id) AS tot_order, 
      SUM(ddo.total) AS total_amount, 
      (SELECT SUM(dop.qty) 
      FROM order_products dop 
      WHERE dop.order=ddo.id) AS prd_qty 
    FROM orders ddo 
    WHERE DATE(ddo.`order_date`) BETWEEN '2016-04-22' AND '2016-04-22' 

我可以得到total_order和TOTAL_AMOUNT，但如何让prd_qty？

感谢

Answer 1

看起来像你想总结order_products到orders然后总结结果。如果你想单独orders他们prd_qty只需运行内部查询

SELECT COUNT(*) tot_order, 
     SUM(os.total) total_amount, 
     SUM(os.o_prd_qty) prd_qty 
    FROM (  
    SELECT o.id, 
      o.total, 
      SUM(op.qty) o_prd_qty 
     FROM orders o 
     JOIN order_products op 
     ON op.order = o.id 
    WHERE o.order_date >= :start_date 
     AND o.order_date < :end_date + INTERVAL 1 DAY 
    GROUP BY o.id 
     ) os 

：

我会按照在查询这个概念。

我还修复了日期逻辑，以便优化器可以使用order_date上的索引。通常，如果您在比较的一侧将函数列封装在函数中，则该列上的任何索引都不可用。

作为一个便笺，我会花很长时间仔细看看你的命名约定。每个人都有自己的喜好，但是你应该符合以下问题：

• 缩写？ - tot，total，prd，product，qty
• 外键追加_id？ - order_products.order，order_products.product_id
• 包括列名中的表名？ - orders.total，orders.order_date

继约定使你的数据库更容易导航和使用。否则，在编写每个查询时它会回到模式！

我个人一般不会缩写，请追加_id，并且不要在列中包含表名。

Answer 2

也许你缺少GROUP BY条款

(SELECT SUM(dop.qty) FROM order_products dop WHERE dop.order=ddo.id GROUP BY dop.order) 

Answer 3

您应该使用子查询计算prd_qty：

SELECT SUM(dop.qty) as prd_qty, dop.order 
FROM order_products dop 
GROUP BY dop.order 

据统计prd_qty每个订单。
并全面查询：

SELECT COUNT(ddo.id) AS tot_order, 
     SUM(ddo.total) AS total_amount, 
     sum(op.prd_qty) as prd_qty 
FROM orders ddo 
     JOIN 
     (
      SELECT SUM(dop.qty) as prd_qty, dop.order 
      FROM order_products dop 
      GROUP BY dop.order 
     ) as op ON (op.order = ddo.id) 
WHERE DATE(ddo.`order_date`) BETWEEN '2016-04-22' AND '2016-04-22' 

Answer 4

此查询会为你工作。

SELECT COUNT(od.`id`) AS tot_order, SUM(od.`total`) AS total_amount, tablea.prd_qty 
FROM orders od JOIN (
       SELECT SUM(op.`prd_qty`) AS prd_qty 
       FROM orders od INNER JOIN order_products op 
       ON od.`id`=op.`order` 
       WHERE DATE(od.`order_date`) BETWEEN '2016-04-22' AND '2016-04-22' 
) tablea 
WHERE DATE(od.`order_date`) BETWEEN '2016-04-22' AND '2016-04-22' 

要获得prd_qty，你必须运行一个子查询我的小括号加join后。

Answer 5

最简单的查询来获得答案将是，这将通过黄油刀。

但是，如果您为列顺序（id）创建索引，它会更加美观。如果已经将不再需要主键列索引 索引只有当你认为你的数据将在数

select count(o.id) as tot_order , 
sum(o.total) as total_amount, 
(select sum(p.qty) from orders o join order_product p on o.id=p.order) as prd_qty  
from orders o; 

Answer 6

增长较高这应该很好地工作适合你。

select sum(order_id) orders, sum(total) total, sum(qty) prd_qty 
from (select count(o.id) order_id, sum(o.total) total, 
(select sum(qty) from order_products op where o.id = op.`order`) qty 
from orders o group by o.id) x; 

相当简单。