mysql - 获取表中存在的每个日期的第一个和最后一个记录号

Question

我试图显示每一天的发票，因此为此我在创建日期使用了分组依据，在小计上使用了总和。我是这样做的:

  SELECT 
         `main_table`.*, 
         SUM(subtotal) AS `total_sales` 
  FROM 
        `sales_invoice` AS `main_table` 
  GROUP BY 
        DATE_FORMAT(created_at, "%m-%y")

它的工作原理，但我还想为每个日期获取 Invoice # from 和 Invoice # to。是否可以通过单个查询来完成？

编辑:

表结构:

------------------------------------------------
| id | inoice_no | created_at           | subtotal

| 1  | 34        | 2015-03-17 05:55:27  | 5 
| 2  | 35        | 2015-03-17 12:35:00  | 7
| 3  | 36        | 2015-03-20 01:40:00  | 3
| 4  | 37        | 2015-03-20 07:05:13  | 6 
| 5  | 38        | 2015-03-20 10:25:23  | 1
| 6  | 39        | 2015-03-24 12:00:00  | 6
------------------------------------------------

输出

---------------------------------------------------------------
| id | inoice_no | created_at           | subtotal | total_sales

| 2  | 35        | 2015-03-17 12:35:00  | 7        | 12
| 5  | 38        | 2015-03-20 10:25:23  | 1        | 10
| 6  | 39        | 2015-03-24 12:00:00  | 6        | 6
-----------------------------------------------------------------

我的期望

---------------------------------------------------------------
| id | inoice_no | created_at           | subtotal | total_sales | in_from | in_to

| 2  | 35        | 2015-03-17 12:35:00  | 7        | 12          | 34      | 35
| 5  | 38        | 2015-03-20 10:25:23  | 1        | 10          | 36      | 38
| 6  | 39        | 2015-03-24 12:00:00  | 6        | 6           | 39      | 39
-----------------------------------------------------------------

Answer 1

如果您的发票号码是整数，那么下面的查询将为您提供您想要的结果:

SELECT DATE_FORMAT(A.created_at, "%m-%y") AS InvoiceDate, 
       MIN(A.invoiveNo) AS FromInvoiceNo, 
       MAX(A.invoiveNo) AS ToInvoiceNo, 
       SUM(A.subtotal) AS total_sales 
FROM sales_invoice AS A 
GROUP BY InvoiceDate;