php - GROUP BY 查询仅显示与 html 表中的日期关联的一行

Question

我尝试了一个查询来在我的表中用不同的行显示相同日期的项目，但日期应该只显示一次。像这样。

Date: 15-09-2015
      ItemId:1  quantity:6 size:XL
      ItemId:1  quantity:4 size:Small
      ItemId:2  quantity:6 size:XL
Date: 16-09-2015
      ItemId:3  quantity:9 size:XL
      ItemId:1  quantity:4 size:Small
      ItemId:2  quantity:6 size:XL

我尝试了以下查询。对于每个日期，它只返回一行。

SELECT orders.date, order_detail.quantity, order_detail.price, order_detail.color, order_detail.size, customers.name, products.product_name, products.product_image FROM order_detail JOIN orders ON orders.serial=order_detail.orderid JOIN customers ON customers.serial=orders.customerid JOIN products ON products.productid=order_detail.productid WHERE customers.email='abc@yahoo.com' GROUP BY orders.date ORDER BY orders.date

请提出任何建议。我怎样才能在我的 html 表格中显示这样的内容。

Answer 1

Group by 查找聚合函数，即 sum('field_name')。您将需要为 order.date 然后子查询获取不同的值(不确定括号是否在正确的位置)。

SELECT DISTINCT orders.date, (SELECT order_detail.quantity, order_detail.price, order_detail.color, order_detail.size, customers.name, products.product_name, products.product_image FROM order_detail JOIN orders ON orders.serial=order_detail.orderid JOIN customers ON customers.serial=orders.customerid JOIN products ON products.productid=order_detail.productid WHERE customers.email='abc@yahoo.com') GROUP BY orders.date ORDER BY orders.date