銳化時查詢未找到值

我正在嘗試加入兩個數據集並找到最近的認證日期

我以下2個數據集

認證

• (1, ‘Judge Level 1’, ‘Judge’, 10, 2),
• (2, ‘Judge Level 2’, ‘Judge’, 20, 2),
• (3, ‘Judge Level 3’, ‘judge’, 30, 2),
• (4, ‘Olympic Judge’, ‘Olympic Judge’, 30, 2),
• (5, ‘初級領導’, ‘領導力’, 10, 1),
• (6, ‘實習生’, ‘領導力’, 10, 1),
• (7, ‘導師’, ‘領導力’, 10, 1);

members_certifications

• (1000052, 7, ‘2016-07-01’),
• (1000053, 1, ‘2016-07-01’),
• (1000053, 3, ‘2018-07-01’),
• (1000053, 4, ‘2019-07-01’),
• (1000053, 5, ‘2020-07-01’),
• (1000059, 1, ‘2016-07-01’),
• (1000059, 2, ‘2017-07-01’),
• (1000059, 5, ‘2016-07-04’),
• (1000059, 6, ‘2016-07-01’),
• (1000059, 6, ‘2016-07-02’);

我希望結果集是

• 1000059, 初級組長, 領導, 10, 2016-09-04
• 1000059, 法官,法官二級, 20, 2016-09-04

我創建了一個確切的範例http://sqlfiddle.com/#!9/20c30b1/3/1 MySql 5.6 版

當我通過設置特定的 id（在本例中為 100059）來減少查詢時，第一個查詢會產生一個大型數據集，我得到 0 個結果。

還有一種查詢效率更高或體積更小的方法來創建此查詢。

CREATE TABLE `certifications` (
 `id` int(11) NOT NULL,
 `title` varchar(254) NOT NULL,
 `type` varchar(254) NOT NULL,
 `sort_order` int(11) NOT NULL,
 `expiration_interval` tinyint(4) NOT NULL DEFAULT '2'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `members_certifications` (
 `member_id` int(11) NOT NULL,
 `certification_id` int(11) NOT NULL,
 `effective_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

查詢 1

SELECT members_certifications.member_id AS member_id,certifications.title AS title,
certifications.type AS type,certifications.sort_order AS sort_order,
MaxTable.effective_date AS effective_date FROM members_certifications, certifications,
(SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,
MAX(members_certifications.effective_date) AS effective_date   
FROM     members_certifications, certifications  
WHERE     members_certifications.certification_id = certifications.id   
GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable 
WHERE  members_certifications.certification_id = certifications.id
AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date 
ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title;

查詢二（0 個結果）

SELECT members_certifications.member_id AS member_id,certifications.title AS title,certifications.type AS type,certifications.sort_order AS sort_order,MaxTable.effective_date AS effective_date FROM members_certifications, certifications,  (SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,     MAX(members_certifications.effective_date) AS effective_date   FROM     members_certifications, certifications   WHERE     members_certifications.certification_id = certifications.id   GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable WHERE  members_certifications.certification_id = certifications.id  AND members_certifications.member_id = 100059  AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title ;

我不確定你在問什麼，但我建議你使用別名和 ANSI 連接來查詢：

SELECT mc1.member_id AS member_id
     ,c1.title AS title
     ,c1.type AS type
     ,c1.sort_order AS sort_order
     ,MaxTable.effective_date AS effective_date 
FROM members_certifications mc1
JOIN certifications c1
   ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
          , c2.type AS type
          , MAX(mc2.effective_date) AS effective_date   
     FROM members_certifications mc2
        , certifications c2 
     WHERE mc2.certification_id = c2.id   
     GROUP BY c2.type, mc2.member_id   
    ) MaxTable 
   ON c1.type = MaxTable.type  
  AND mc1.member_id = MaxTable.member_id  
  AND mc1.effective_date = MaxTable.effective_date 
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

關於小提琴中的第二個查詢，兩者之間存在細微差別：

1000059 and 100059

因此，如果您將其更改為：

SELECT mc1.member_id AS member_id
     ,c1.title AS title
     ,c1.type AS type
     ,c1.sort_order AS sort_order
     ,MaxTable.effective_date AS effective_date 
FROM members_certifications mc1
JOIN certifications c1
   ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
          , c2.type AS type
          , MAX(mc2.effective_date) AS effective_date   
     FROM members_certifications mc2
        , certifications c2 
     WHERE mc2.certification_id = c2.id   
     GROUP BY c2.type, mc2.member_id   
    ) MaxTable 
   ON c1.type = MaxTable.type  
  AND mc1.member_id = MaxTable.member_id  
  AND mc1.effective_date = MaxTable.effective_date 
WHERE mc1.member_id = 1000059   
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

這會給你預期的答案嗎？

MySQL 8+ 支持效率更高的視窗函式。您可以使用視窗函式來確定 max_effective_date，而不是對子選擇進行連接，然後使用它進行過濾：

SELECT member_id, title, type, sort_order, effective_date
FROM (
   SELECT mc1.member_id AS member_id
         ,c1.title AS title
         ,c1.type AS type
         ,c1.sort_order AS sort_order
         ,mc1.effective_date  
         ,Max(effective_date) over (partition by member_id, c2.type) AS max_effective_date 
  FROM members_certifications mc1
  JOIN certifications c1
       ON mc1.certification_id = c1.id
) AS T
WHERE max_effective_date = effective_date 
ORDER BY member_id, sort_order, type, title; 

引用自：https://dba.stackexchange.com/questions/270462