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

Question

I tried a query to show items of same date in my table with different rows but date should be displayed only once. Something like this.

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

I tried the following query. It just returns me only one row for each date.

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='[e-mail]' GROUP BY orders.date ORDER BY orders.date

Any suggestions please. How can i display like this in my html table.

Answer 1

Group by looks for an aggregate function i.e. sum('field_name'). You will need to get the distinct values for the order.date then subquery (not sure the parentheses are in the right spot).

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='[e-mail]') GROUP BY orders.date ORDER BY orders.date