mysql - 当两个行号相同时选择值

Question

当两个行号值按顺序相同时，我需要根据日期确定选择哪个值。

这里有相同的 DDL 和 sqlfiddle，以准确显示我的表的结构。

CREATE TABLE otis_pos (
 `Line Number` int(255),
 `Contract Number` varchar(255),
 `Date Promised` date,
 `Item Number` varchar(255)
) ENGINE=MyISAM;

CREATE TABLE `otis rev link` (
 RoytecP_N varchar(255),
 Otis_PN varchar(255)
) ENGINE=MyISAM;

INSERT INTO `otis_pos`
VALUES
  ('1','648756','2020-01-24',''),
  ('2','648756','2020-01-24','ACA21600DV998'),
  ('3','648756','2020-01-24','AAA174AKV96'),
  ('4','648756','2020-01-24','ACA21600DT998'),
  ('5','648756','2020-01-24','AAA174AKV97'),
  ('1','648752','2020-01-24',''),
  ('2','648752','2020-01-24','AAA21600HZ974'),
  ('3','648752','2020-01-24','AAA21600HY998');

INSERT INTO `otis rev link`
VALUES
('OTHWC00DV998','ACA21600DV998'),
('9O04174AKV96-B','AAA174AKV96'),
('OTHWC00DT998','ACA21600DT998'),
('OTHW600HZ974','AAA21600HZ974'),
('89OTHW600HZ974','AAA21600HZ974'),
('OTHW600HY998','AAA21600HY998'),
('89OTHW600HY998','AAA21600HY998'),
('','');

http://sqlfiddle.com/#!9/2ccf800/1

这是我尝试过的:

SELECT DISTINCT `Line Number`
              , `Contract Number`
              , `Date Promised`
              , `roytecp_n`
           FROM otis_pos
          RIGHT 
           JOIN `otis rev link` 
             on otis_pos.`Item Number` = `otis rev link`.otis_pn
          WHERE otis_pos.`Contract Number` <> '' 
    AND (`Date Promised` <= CURRENT_DATE() + INTERVAL 28 DAY 
    AND (roytecp_n Like 'OTH%' 
        OR roytecp_n Like '9O%' 
        OR roytecp_n Like '81%'
        OR roytecp_n is NULL
  ) 
  OR 
  ( `Date Promised` > CURRENT_DATE() + INTERVAL 28 DAY
    AND (roytecp_n Like '88%' 
        OR roytecp_n Like '89%' 
        OR roytecp_n IS NULL
    )
  )
)

以下是当值大于或等于 current_date + 28 天时需要返回数据的方式:

Line Number    Contract Number    Date Promised    roytecp_n
   1               648752          2020-01-24    
   2               648752          2020-01-24      89OTHW600HZ974
   3               648752          2020-01-24      89OTHW600HY998
   1               648756          2020-01-24    
   2               648756          2020-01-24      89OTHW600DV998
   3               648756          2020-01-24      9O04174AKV96-B
   4               648756          2020-01-24      OTHWC00DT998  

以下是当值小于或等于 current_date + 28 天时需要返回数据的方式

Line Number    Contract Number    Date Promised    roytecp_n
   1               648752          2020-01-24    
   2               648752          2020-01-24      OTHW600HZ974
   3               648752          2020-01-24      OTHW600HY998
   1               648756          2020-01-24    
   2               648756          2020-01-24      OTHW600DV998
   3               648756          2020-01-24      9O04174AKV96-B
   4               648756          2020-01-24      OTHWC00DT998

Answer 1

假设表中的 auto_increment 列 otis rev link是 id，获取 Date Promised 的结果小于或等于 current_date + 28 天:

SELECT p.`Line Number`
    , p.`Contract Number`
    , p.`Date Promised`
    , l.`roytecp_n`
FROM otis_pos p
RIGHT JOIN (
  SELECT l.otis_pn, MIN(l.id) AS id
  FROM `otis rev link`
  WHERE (roytecp_n Like 'OTH%' 
  OR roytecp_n Like '9O%' 
  OR roytecp_n Like '81%'
  OR roytecp_n is NULL)
  GROUP BY l.roytecp_n) m
ON o.`Item Number` = m.otis_pn
INNER JOIN `otis rev link` l
ON l.id=m.id
WHERE p.`Contract Number` <> '' 
AND p.`Date Promised` <= CURRENT_DATE() + INTERVAL 28 DAY;