我有一个关于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(日期)来表示日期?
有人能给我指个方向吗?
谢谢
1条答案
按热度按时间klr1opcd1#
您的想法应该可行,但您将看到两个证书的最大值(如果它们有两个)。一旦您知道最大日期是什么,就可以使用新的连接将其清除,只获得相关的行。
让我们来看看它是如何工作的。
(NOTE--在这些例子中,我已经在SQL Server中编写并测试了所有这些。对于其他RDBMS可能需要一些小的调整,但大部分是在大多数平台上工作的标准SQL语法)。
A)创建一些测试数据:)
B)获取志愿者身份证及其最近的证书日期
| 志愿者标识|最大奖励截止日期|
| - -|- -|
| 一个|2022年1月31日|
| 2个|2022年1月31日|
| 三个|2022年2月28日|
| 四个|2022年3月31日|
C)使用此数据与在该日期颁发的证书进行匹配
| 志愿者标识|奖励名称|最大奖励截止日期|
| - -|- -|- -|
| 一个|急救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
这一事实,再次选择最大值......(但是,在更复杂的情况下,我们可能会使用一个查找表来连接,以告诉我们哪个奖项是最新版本)。| 志愿者标识|奖励名称|奖励到期日|
| - -|- -|- -|
| 一个|急救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技能的一个很好的补充。| 志愿者标识|奖励名称|奖励到期日|
| - -|- -|- -|
| 一个|急救ver 1| 2022年1月31日|
| 2个|急救ver 2| 2022年1月31日|
| 三个|急救ver 1| 2022年2月28日|
| 四个|急救ver 2| 2022年3月31日|
此查询和上一个查询获得的结果相同。