时间戳

首页 > 解决方案 > MySQL：我需要根据某些条件返回行

mysql - MySQL：我需要根据某些条件返回行

问题描述

我有 3 张桌子：工作、录音、发布

1 个作品可以有多个录音，1 个录音仅出现在 1 个版本中

表：工作

+---------+-----------+
| work_id | name      |
+---------+-----------+
| 1       | Hello     | 
| 3       | Luna      | 
| 4       | Feel good | 
| 5       | My self   | 
+---------+-----------+

表：记录

+---------------------------------------------------------------------+
| recording_id | work_id | release_id | name        | is_art | is_vid |
+---------------------------------------------------------------------+
| 45           | 1       | 45         | Hello4      | 1      | 0      |
| 78           | 3       | 67         | Luna5       | 1      | 0      |
| 23           | 5       | 128        | My self (r) | 1      | 0      |
| 95           | 5       | 156        | My self II  | 1      | 0      |
| 17           | 4       | 67         | Luna67      | 1      | 0      |
+---------------------------------------------------------------------+

表：发布

+--------------------------------------------+
| release_id | name    | year | month | day  |
+--------------------------------------------+
| 45         | Yo      | 1998 | 12    | NULL |
| 67         | Testing | 1967 | 3     | 3    |
| 128        | Maybe   | 2018 | 10    | 21   |
| 156        | Again   | 2018 | 10    | NULL |
+--------------------------------------------+

基本上，对于每个work，我想返回recordingwhereis_art = 1和is_vid = 0ANDrelease是最旧的（最旧的年、月和日）。我可能是 arecording release可以具有相同的year,month和day. 在那种情况下，我想我需要找到一个唯一的标识符，所以我会选择最新的release_id

结果集应如下所示：

+---------+---------------------------------------+
| work_id | name      | recording_id | name       |
+---------+---------------------------------------+
| 1       | Hello     | 45           | Hello4     |
| 3       | Luna      | 78           | Luna5      |
| 4       | Feel good | 17           | Luna67     |
| 5       | My self   | 23           | My self (r)|
+---------+---------------------------------------+

到目前为止，我创建了这个查询，但老实说，作为一个新手，我知道这一切都错了。它返回重复的行。我觉得我需要使用group by和子查询，但经过 2 天的搜索和测试，我无法创建解决方案......我快疯了

样本数据 1

| work_id | work_name           | recording_id | release_id | rec_name                                            | year | month | day |
|---------|---------------------|--------------|------------|-----------------------------------------------------|------|-------|-----|
|     201 | Me ha dicho la luna |          253 |          5 | Me ha dicho la luna                                 | 1998 |     4 |  22 |
|     201 | Me ha dicho la luna |          579 |        528 | Me ha dicho la luna (Moonlight Radio Edit)          | 1998 |       |     |
|     201 | Me ha dicho la luna |          580 |        528 | Me ha dicho la luna (Luna llena Ambience Mix)       | 1998 |       |     |
|     201 | Me ha dicho la luna |          581 |        528 | Me ha dicho la luna (Extended Callejuela's Version) | 1998 |       |     |
|     201 | Me ha dicho la luna |          582 |        528 | Me ha dicho la luna (Stoned Baby Free Version)      | 1998 |       |     |
|     201 | Me ha dicho la luna |          252 |          1 | Me ha dicho la luna (con Chayanne)                  | 2006 |       |     |

样本数据 2

| work_id | work_name  | recording_id | release_id | rec_name                                                | year | month | day |
|---------|------------|--------------|------------|---------------------------------------------------------|------|-------|-----|
|     401 | Si amanece |          397 |         26 | Si amanece                                              | 1978 |     7 |   1 |
|     401 | Si amanece |          634 |        309 | Si amanece                                              | 1978 |     7 |   1 |
|     401 | Si amanece |          396 |        257 | Si amanece (con el Mariachi Oro y Plata de Pepe Chávez) | 1979 |       |     |
|     401 | Si amanece |          564 |        188 | Si amanece                                              | 2001 |       |     |
|     401 | Si amanece |          394 |        213 | Si amanece                                              | 2001 |       |     |
|     401 | Si amanece |          395 |          1 | Si amanece                                              | 2006 |       |     |
|     401 | Si amanece |          638 |        295 | Si amanece                                              |      |       |     |

标签： mysqlsqljoinsql-order-bymysql-5.7

解决方案

这是为您的示例数据生成预期结果的查询：

select
    w.work_id,
    w.name work_name,
    r.recording_id,
    r.name recording_name
from work w
inner join recording r 
    on r.recording_id = (
        select r1.recording_id 
        from recording r1 
        inner join releases l1 on l1.release_id = r1.release_id
        where r1.work_id = w.work_id and r1.is_art = 1 and r1.is_vid = 0
        order by -l1.year desc, -l1.month desc, -l1.day desc, r1.release_id desc
        limit 1
    )

这通过将work表与连接起来recording，使用相关的子查询来选择正确的行。从您的示例数据和结果来看，您似乎希望null在对行进行排序时将 s 放在首位：这不是 MySQL 中的默认行为，因此我们使用了一种技巧，即排序依据- <column_name> desc（将nulls 放在首位，同时尊重升序种类）。

注意：release是MySQL 中的保留字，所以我改为命名该表releases（否则，您需要用反引号括起来）。

DB Fiddle 上的演示：

工作编号 | 工作名称 | 录音ID | 录音名称
------: | :-------- | ------------: | :-------------
      1 | 你好 | 45 | 你好4        
      3 | 露娜 | 78 | 月神5         
      5 | 我的自我 | 23 | 我的自己（r）

或者，如果您正在运行 MySQL 8.0，您可以使用row_number()来识别正确的记录。根据您的数据集，这可能会或可能不会表现得更好：

select work_id, work_name, recording_id, recording_name
from (
    select
        w.work_id,
        w.name work_name,
        r.recording_id,
        r.name recording_name,
        row_number() over(
            partition by r.work_id 
            order by -l.year desc, -l.month desc, -l.day desc, r.release_id desc
        ) rn
    from work w
    inner join recording r 
        on r.work_id = w.work_id
        and r.is_art = 1
        and r.is_vid = 0
    inner join releases l 
        on l.release_id = r.release_id
) t
where rn = 1

DB Fiddle 上的演示（与上述结果相同）

推荐阅读