mysql - 如何在第一个日期高于引用日期的情况下选择第一个日期、上一个日期、最晚日期

Question

我想从表 1 中选择最新日期、第二个最新日期和第一个日期，其中第一个日期高于另一个表 2 中的引用日期。并且该引用日期也应该是该表 2 中的最新日期。我有一个解决方案，应该是。但问题是，如果表 1 中只有 1 条记录，解决方案将不会返回输出。表格示例:

表 1

Reg ID | DateOfAI   | byTechnician
2GP001 | 2015-01-13 | 31
2GP001 | 2015-02-18 | 31
2GP001 | 2017-11-10 | 45
2GP001 | 2017-11-30 | 32
2GP044 | 2017-11-30 | 28
2GP001 | 2017-12-23 | 32

表2

Reg ID | DateOfCalving   | DryOffDate
2GP001 | 2016-01-14      | 
2GP070 | 2016-01-14      |
2GP065 | 2017-04-08      | 
2GP001 | 2017-04-12      | 

我的预期输出是:

Reg ID | LatestDateOfCalving   | 1stDateOfAI  | PreviousAIDate  | LastestAIDate 
2GP001 | 2017-04-12            | 2017-11-10   | 2017-11-30      | 2017-12-23

我从月球来回四处搜索……仍然没有运气。这些是我用过的查询

第一个:

    SELECT b.actualDam,COUNT(x.actualDam) AS ilanba, max(b.breedDate) AS huli, max(x.breedDate) AS nex,MIN(x.breedDate) AS una,IFNULL(c.calvingDate,NULL) AS nganak,r.*,h.herdID,a.animalID,a.regID, IFNULL(a.dateOfBirth,NULL) AS buho
  FROM x_animal_breeding_rec b
  LEFT JOIN x_animal_calving_rec c ON b.recID=c.brecID 
  LEFT JOIN x_herd_animal_rel r ON b.actualDam=r.animal 
  LEFT JOIN x_herd h ON r.herd=h.herdID 
  LEFT JOIN x_animal_main_info a ON b.actualDam=a.animalID 
  JOIN x_animal_breeding_rec x ON b.actualDam = x.actualDam AND x.breedDate < b.breedDate  
  WHERE h.herdID = ? AND x.mateType = ? AND x.recFlag = ? GROUP BY b.actualDam

---

我试过的第二个是这段代码:

    SELECT b.recID
     , b.actualDam
     , b.breedDate
     , min(b.breedDate) AS una     
     , max(b.breedDate) AS huli
     , COUNT(b.actualDam) AS sundot
     , b.mateType
     , b.recFlag
     , a.animalID
     , a.regID     
     , h.*      
  FROM
     ( SELECT c.recID, c.actualDam           
            , c.breedDate            
            , c.mateType
            , c.recFlag
            , CASE WHEN @prev=c.recID THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=c.recID prev
            FROM x_animal_breeding_rec c        
            , ( SELECT @prev:=null,@i:=0 ) vars
            ORDER BY c.recID,c.breedDate DESC
     ) b 
     LEFT JOIN x_animal_main_info a ON b.actualDam=a.animalID 
     LEFT JOIN x_herd_animal_rel h ON b.actualDam=h.animal            
     WHERE i <= 2 GROUP BY b.actualDam HAVING h.herd = ? AND b.mateType = ? AND b.recFlag = ? ORDER BY b.breedDate DESC

这里的另一个问题是第一个解决方案返回错误计数。第二个解决方案返回正确的计数，但是返回了错误的日期。我希望你能给我一个主意。提前致谢。

Answer 1

以下查询回答了您的问题:

SELECT
    RegID,
    LatestDateOfCalving,
    MIN(DateOfAI) AS 1stDateOfAI,
    REPLACE(SUBSTRING_INDEX(GROUP_CONCAT(DateOfAI ORDER BY DateOfAI DESC), ',', 2), CONCAT(MAX(DateOfAI), ','), '') AS PreviousAIDate,
    MAX(DateOfAI) AS LatestAIDate
FROM (
    SELECT
        t1.RegID,
        LatestDateOfCalving,
        DateOfAI,
        IF(DateOfAI >= LatestDateOfCalving, 1, 0) AS dates
    FROM table1 AS t1
    INNER JOIN (
        SELECT
            RegID,
            MAX(DateOfCalving) AS LatestDateOfCalving
        FROM table2 GROUP BY RegID
    ) AS tt2 ON t1.RegID = tt2.RegID) AS x
WHERE dates = 1
GROUP BY RegID
HAVING COUNT(dates) >= 3;

输出:

+--------+---------------------+-------------+----------------+--------------+
| RegID  | LatestDateOfCalving | 1stDateOfAI | PreviousAIDate | LatestAIDate |
+--------+---------------------+-------------+----------------+--------------+
| 2GP001 | 2017-04-12          | 2017-11-10  | 2017-11-30     | 2017-12-23   |
+--------+---------------------+-------------+----------------+--------------+

DEMO

在子查询中，我们从 table2 中选择 RegID 和 LatestDateOfCalving 以获得引用日期。然后将其加入 table1 并标记记录 DateOfAI 是否大于或等于 LatestDateOfCalving (IF(DateOfAI >= LatestDateOfCalving, 1, 0))。我们在外部查询中使用此子查询(SELECT RegID, LatestDateOfCalving, MIN(DateOfAI) AS 1stDateOfAI, MAX(DateOfAI) AS LatestAIDate, ...)并仅选择那些包含DateOfAI 的记录 在 LatestDateOfCalving 或之后(WHERE dates = 1，其中 1 是条件为真的标志)并且至少有 3 条记录(有计数(日期)>= 3)。在外部查询中，我使用 REPLACE(SUBSTRING_INDEX(GROUP_CONCAT(...))) 结构从逗号 (,) 分隔的日期列表。