mysql - Group by Mysql 查询问题

Question

过去几天我一直被这个 MySQL 查询难住了，在我把我的电脑扔出窗外之前寻求一些专家的帮助，哈哈。

我有一个包含发票的表格；发票编号列名称是 Trans_ref$$(主键)。

每张发票都链接到一个作业，列名是 OPSREF$$_ORIGINAL(外键)。

两列都在同一个表中。

一个工作可以有多个发票。

现在的问题是我正在尝试创建一个查询，该查询将在工作组中按顺序 (Trans_ref$$) 识别第一张发票，如果第一张发票具有日期范围，则输出该工作 (OPSREF$$ _原来的)。发票编号是连续且唯一的编号。

我曾尝试使用 case/min group by 语句来识别数据范围内作业组中的第一张发票，但是此查询并未隔离日期范围内的第一张发票，而只是输出第一张或第二张或工作组中与日期范围匹配的第 3 个发票……我希望这是有道理的。

我已经包含了表格的屏幕截图和所需的输出，如果有人你好过去一天我一直被这个查询难倒，在我把我的电脑扔出窗外之前寻求一些专家的帮助，大声笑。

我有一个包含发票的表格；发票编号列名称是 Trans_ref$$(主键)。

每张发票都链接到一个作业，列名是 OPSREF$$_ORIGINAL(外键)。

两列都在同一个表中。

一个工作可以有多个发票。

现在的问题是我正在尝试创建一个查询来识别作业组中的第一张发票 (Trans_ref$$)，如果该第一张发票具有日期范围，则输出作业 (OPSREF$$_ORIGINAL) .顺序/唯一编号中的发票编号。

我曾尝试使用 case/min group by 语句来识别数据范围内作业组中的第一张发票，但是此查询并未隔离日期范围内的第一张发票，而只是输出第一张或第二张或工作组中与日期范围匹配的第 3 个发票……我希望这是有道理的。

这是我用过的代码

SET @START_DATE := "2014-08-27";
SET @END_DATE:= "2014-09-02";

select 
count(TRANS_REF$$),
Case 
when min(TRANS_REF$$) and INVOICE_DATE_D8>=@START_DATE and INVOICE_DATE_D8<=@END_DATE
then OPSREF$$_ORIGINAL
end as OPSREF$$_ORIGINAL
from INVOICE_HEADER_UNS
where 
TRANSACTION_STATUS = 9
and Tmode$$="06"
group by OPSREF$$_ORIGINAL

所以要分阶段分解这个过程......

<强>1。获取 INVOICE_HEADER_UNS 表中的每个 OPSREF$$_ORIGINAL 组

Date Range - Start 27/08/2014 - End 02/09/2014                  

TABLE - INVOICE_HEADER_UNS                  

TRANS_REF$$ INVOICE_DATE_D8    OPSREF$$_ORIGINAL                
---------------------------------------------
|   1       |    26/08/2014 |  ABC          | 
|   2       |    02/08/2014 |  ABC          |  
---------------------------------------------
|   3       |    28/08/2014 |  DDD          |  
|   4       |    09/09/2014 |  DDD          |  
---------------------------------------------
|   5       |   01/01/2013  |  JJJ          |
|   6       |   21/12/2013  |  JJJ          |
---------------------------------------------
|   7       |   01/09/2014  |  LLL          |
---------------------------------------------

<强>2。在每个 OPSREF$$_ORIGINAL 组中，查看 TRANS_REF$$ 序列并找到该 OPSREF$$_ORIGINAL 组中的第一个 TRANS_REF$$。

Date Range - Start 27/08/2014 - End 02/09/2014                  

TABLE - INVOICE_HEADER_UNS                  

TRANS_REF$$ INVOICE_DATE_D8    OPSREF$$_ORIGINAL           Is First inv?                
--------------------------------------------------------
|   1       |    26/08/2014 |  ABC                      |  yes           
|   2       |    02/08/2014 |  ABC                      |  no           
--------------------------------------------------------
|   3       |    28/08/2014 |  DDD                      |  yes           
|   4       |    09/09/2014 |  DDD                      |  no            
---------------------------------------------------------
|   5       |   01/01/2013  |  JJJ                      |  yes               
|   6       |   21/12/2013  |  JJJ                      |  no            
---------------------------------------------------------
|   7       |   01/09/2014  |  LLL                      |  yes               
---------------------------------------------------------

<强>3。查看组的有序序列中的第一个发票并检查该行的 INVOICE_DATE_D8 并查看它是否在日期范围内 (27/08/2014 - 02/09/2014)

Date Range - Start 27/08/2014 - End 02/09/2014                  

TABLE - INVOICE_HEADER_UNS                  

TRANS_REF$$ INVOICE_DATE_D8    OPSREF$$_ORIGINAL            Is First inv?    In date range?         
--------------------------------------------------------
|   1       |    26/08/2014 |  ABC                      |  yes              no

--------------------------------------------------------
|   3       |    28/08/2014 |  DDD                      |  yes              yes

---------------------------------------------------------
|   5       |   01/01/2013  |  JJJ                      |  yes              no

---------------------------------------------------------
|   7       |   01/09/2014  |  LLL                      |  yes              yes

<强>4。每行是否符合标准(即第一张发票和日期 - 是 + 是)？如果正确，则为该行输出不同的 OPSREF$$_ORIGINAL

这是上面示例的所需输出

Date Range - Start 27/08/2014 - End 02/09/2014                  

TABLE - INVOICE_HEADER_UNS                  

OPSREF$$_ORIGINAL         Is First inv?    In date range?           
------------------------
|  DDD                  |  yes                yes
------------------------
|  LLL                  |  yes                yes
------------------------

基于此，您能建议我如何做到这一点吗？谢谢

这是表定义；

表名 - INVOICE_HEADER_UNS

**TRANS_REF$$**    char(7) - Primary key

INVOICE_DATE_D8    datatime                 

OPSREF$$_ORIGINAL    char(11) 

---

16/09/2014 - 这是似乎使用 DRapp 语法返回正确结果的查询，但是需要以某种方式优化查询，因为当我将其作为子查询绑定(bind)到更大的查询时，它会导致我的 MYSQL工作台崩溃。在对查询进行解释后，它似乎正在通过 28,309 行进行索引。如何在不影响结果的情况下优化此查询？

我已经尝试将以下内容添加到 where 子句中，但是它会向整个查询输出添加更多记录，但会索引 979 行/

set @START_DATE := '2014-08-27';
set @END_DATE := '2014-09-02';

SELECT

if (min(ihu.INVOICE_DATE_D8)>= @START_DATE and min(ihu.INVOICE_DATE_D8)<= @END_DATE     ihu.opsref$$_original, null) as firstinvoiceindaterange

from
INVOICE_HEADER_UNS ihu

join CONSIGNMENT_ALL_HEADER_UNS on ihu.OUR_REF = CONSIGNMENT_ALL_HEADER_UNS.OPSREF$$

where ihu.TRANSACTION_STATUS = 9 and ihu.Tmode$$="06"

AND CONSIGNMENT_ALL_HEADER_UNS.CONS_TYPE$$ <>'7'

AND CONSIGNMENT_ALL_HEADER_UNS.CONS_TYPE$$ in ('3' , '5', '9')

#and ihu.INVOICE_DATE_D8>= @START_DATE and ihu.INVOICE_DATE_D8<= @END_DATE

group by

ihu.opsref$$_original

Answer 1

我认为这应该对您有所帮助，您可以根据需要进行调整。

SELECT
      ihu.opsref$$_original,
      MIN( ihu.TRANS_REF$$ ) MinRefForEntireJob,
      MIN( ihu.INVOICE_DATE_D8 ) MinDateForEntireJob,
      MIN( IF(    ihu.INVOICE_DATE_D8 >= @START_DATE
              AND ihu.INVOICE_DATE_D8 <= @END_DATE, ihu.TRANS_REF$$, null )) as MinRefWithinDateRange,
      MIN( IF(    ihu.INVOICE_DATE_D8 >= @START_DATE
              AND ihu.INVOICE_DATE_D8 <= @END_DATE, ihu.INVOICE_DATE_D8, null )) as MinDateWithinDateRange
   from
      INVOICE_HEADER_UNS ihu,
      ( select @START_DATE := '2014-08-27',
               @END_DATE := '2014-09-02' ) sqlvars
   group by
      ihu.opsref$$_original

一旦你看到这些结果，那么你应该能够添加一个像这样的 HAVING 子句

   HAVING
      MinDateWithinDateRange IS NOT NULL