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

cmssoen2  于 2022-11-08  发布在  其他
关注(0)|答案(1)|浏览(131)

我有一个关于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(日期)来表示日期?
有人能给我指个方向吗?
谢谢

klr1opcd

klr1opcd1#

您的想法应该可行,但您将看到两个证书的最大值(如果它们有两个)。一旦您知道最大日期是什么,就可以使用新的连接将其清除,只获得相关的行。
让我们来看看它是如何工作的。
(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日|
此查询和上一个查询获得的结果相同。

相关问题