mariadb SQL选择的行的日期比FK的另一个条目的日期晚

我有一个关于Maria DB 10.4.24的SQL问题要问。
我有一张表格，上面有志愿者持有的急救证书的类型/版本。该证书的某些版本比过去颁发的以前的类型更新。
版本并不重要，只要每年至少更新一份急救证书。
一些志愿者在完成新的课程后，同时获得了两个证书（第二版），留下一个被遗弃的旧（版本1），但这仍保留在表中的记录中......或者一些志愿者将使用旧版本或新版本，因为新志愿者将从版本2开始，或年龄较大的志愿者将使用旧的版本1证书，他们将不断更新......他们很懒......
问题是我需要一种方法来知道那些同时拥有这两个版本的人，最近更新的版本的名称是什么，是版本一还是版本二，
对于那些只有一个版本的证书，我仍然需要知道版本。
表名为：志愿者奖励列包括：志愿者标识、奖励名称、奖励到期日期
我在查询中选择了所有这三列，但只想看到返回的最新急救证书行，因为我不需要返回两个奖项，只需要返回最近更新的证书、证书的日期和名称......以及成员ID。
另一个问题是，在这个表中除了急救之外还有其他类型的奖励，所以一个人在这个表中可能有5个条目，我只关注急救奖励版本1或2，以及其中（如果有两个）最晚到期的一个。
所以-我用的是

SELECT volunteer_id, award_name, MAX(award_expiry_date)
FROM volunteer_awards
WHERE volunteer_awards.award_name= "first aid ver1"
OR volunteer_awards.award_name= "first aid ver2"
AND volunteer_id = "123456789";

我很好奇，这种查询是否需要IF语句或MAX（日期）来表示日期？
有人能给我指个方向吗？
谢谢

您的想法应该可行，但您将看到两个证书的最大值（如果它们有两个）。一旦您知道最大日期是什么，就可以使用新的连接将其清除，只获得相关的行。
让我们来看看它是如何工作的。
(NOTE--在这些例子中，我已经在SQL Server中编写并测试了所有这些。对于其他RDBMS可能需要一些小的调整，但大部分是在大多数平台上工作的标准SQL语法）。

A）创建一些测试数据：）

DROP TABLE IF EXISTS #volunteer_awards;
CREATE TABLE #volunteer_awards (volunteer_id INT, award_name NVARCHAR(30), award_expiry_date DATE);
INSERT INTO #volunteer_awards
VALUES
    (1, 'first aid ver1', '20220131'), -- expected
    (2, 'first aid ver2', '20220131'), -- expected
    (3, 'first aid ver1', '20220131'),
    (3, 'first aid ver1', '20220228'), -- expected
    (4, 'first aid ver1', '20220131'),
    (4, 'first aid ver1', '20220228'),
    (4, 'first aid ver2', '20220228'),
    (4, 'first aid ver1', '20220331'),
    (4, 'first aid ver2', '20220331'), -- expected
    (4, 'other         ', '20221231');

B）获取志愿者身份证及其最近的证书日期

SELECT 
    volunteer_id, 
    MAX(award_expiry_date) AS MaxOfAwardExpiryDate
FROM #volunteer_awards
WHERE 
    award_name= 'first aid ver1'
    OR award_name= 'first aid ver2'
GROUP BY volunteer_id

| 志愿者标识|最大奖励截止日期|
| - -|- -|
| 一个|2022年1月31日|
| 2个|2022年1月31日|
| 三个|2022年2月28日|
| 四个|2022年3月31日|

C）使用此数据与在该日期颁发的证书进行匹配

SELECT T2.*
FROM
(
    -- Just the volunteer id with their max certificate date
    SELECT 
        volunteer_id, 
        MAX(award_expiry_date) AS MaxOfAwardExpiryDate
    FROM #volunteer_awards
    WHERE 
        award_name= 'first aid ver1'
        OR award_name= 'first aid ver2'
    GROUP BY volunteer_id
) T1
INNER JOIN #volunteer_awards T2
ON T1.volunteer_id = T2.volunteer_id
AND T1.MaxOfAwardExpiryDate = T2.award_expiry_date

| 志愿者标识|奖励名称|最大奖励截止日期|
| - -|- -|- -|
| 一个|急救ver 1| 2022年1月31日|
| 2个|急救ver 2| 2022年1月31日|
| 三个|急救ver 1| 2022年2月28日|
| 四个|急救ver 1| 2022年3月31日|
| 四个|急救ver 2| 2022年3月31日|

最终调整：如果存在两个具有相同到期日期的证书，则仅获取最新的证书

我们为志愿者#4设置了两行--此人有两个证书，都在同一天到期，而且都是最新的。我们可以利用first aid ver2“大于”first aid ver1这一事实，再次选择最大值......（但是，在更复杂的情况下，我们可能会使用一个查找表来连接，以告诉我们哪个奖项是最新版本）。

SELECT T1.volunteer_id, MAX(award_name) AS award_name, T2.award_expiry_date
FROM
(
    SELECT 
        volunteer_id, 
        MAX(award_expiry_date) AS MaxOfAwardExpiryDate
    FROM #volunteer_awards
    WHERE 
        award_name= 'first aid ver1'
        OR award_name= 'first aid ver2'
    GROUP BY volunteer_id
) T1
INNER JOIN #volunteer_awards T2
ON T1.volunteer_id = T2.volunteer_id
AND T1.MaxOfAwardExpiryDate = T2.award_expiry_date
GROUP BY T1.volunteer_id, T2.award_expiry_date

| 志愿者标识|奖励名称|奖励到期日|
| - -|- -|- -|
| 一个|急救ver 1| 2022年1月31日|
| 2个|急救ver 2| 2022年1月31日|
| 三个|急救ver 1| 2022年2月28日|
| 四个|急救ver 2| 2022年3月31日|

另一个使用Windows函数的解决方案

大多数现代RDBMS现在都支持window functions，如row_number()。通过这些查询，我们可以对数据进行分区、排序和编号--在本例中，我们可以通过志愿者对证书及其到期日期进行排序。前面的示例使用了基于集合的逻辑和简单查询--这对解决问题很有用，只是为了习惯这种“用SQL思考”的方式。但是现在我们可以编写更优雅和更强大的SQL来获得同样的结果，所以这里有一个例子。我鼓励花大量的时间学习和尝试SQL窗口函数来获得它们--它们是对任何人的SQL技能的一个很好的补充。

SELECT 
    T1.volunteer_id, 
    T1.award_name, 
    T1.award_expiry_date
FROM
(

    SELECT 
        ROW_NUMBER() OVER (
            PARTITION BY volunteer_id 
            ORDER BY award_expiry_date desc, award_name DESC) AS seq,
        *
        FROM #volunteer_awards
        WHERE 
            award_name= 'first aid ver1'
            OR award_name= 'first aid ver2'
) T1
WHERE T1.seq =  1;

| 志愿者标识|奖励名称|奖励到期日|
| - -|- -|- -|
| 一个|急救ver 1| 2022年1月31日|
| 2个|急救ver 2| 2022年1月31日|
| 三个|急救ver 1| 2022年2月28日|
| 四个|急救ver 2| 2022年3月31日|
此查询和上一个查询获得的结果相同。

mariadb SQL选择的行的日期比FK的另一个条目的日期晚

1条答案

A）创建一些测试数据：）

B）获取志愿者身份证及其最近的证书日期

C）使用此数据与在该日期颁发的证书进行匹配

最终调整：如果存在两个具有相同到期日期的证书，则仅获取最新的证书

另一个使用Windows函数的解决方案

相关问题

热门标签

最新问答