sql查询,如果存在则返回特定标签(如果不存在则返回0)

mnemlml8  于 2021-06-15  发布在  Mysql
关注(0)|答案(2)|浏览(396)

给出了两个表,tag和media。mysql>从媒体中选择*;

+----+---------+----------+
| id | name    | duration |
+----+---------+----------+
| 1  | cat.mp4 | 3.4      |
| 2  | dog.mp4 | 8        |
+----+---------+----------+

mysql>select*from标签;

+----+----------+-------+--------+------------+
| id | media_id | type  | value  | confidence |
+----+----------+-------+--------+------------+
| 1  | 1        | LABEL | cat    | 0.9        |
| 2  | 1        | LABEL | person | 0.6        |
| 3  | 1        | TEXT  | kitty  | 0.95       |
| 4  | 2        | LABEL | dog    | 0.8        |
| 5  | 2        | LABEL | person | 0.75       |
| 6  | 2        | TEXT  | food   | 0.7        |
+----+----------+-------+--------+------------+

我需要通过连接两个表来获得输出表,这两个表给出了media\u id、name、duration和tag中值的label,这样如果值是cat,cat的置信度将插入label\u cat列,否则将插入0。像这样:

+----------+---------+----------+-----------+-----------+--------------+
| media_id | name    | duration | label_cat | label_dog | label_person |
+----------+---------+----------+-----------+-----------+--------------+
| 1        | cat.mp4 | 3.4      | 0.9       | 0         | 0.6          |
| 2        | dog.mp4 | 8        | 0         | 0.8       | 0.75         |
+----------+---------+----------+-----------+-----------+--------------+
cigdeys3

cigdeys31#

如果我理解正确,您需要条件聚合:

select m.id, m.name, m.duration,
       max(case when t.value = 'cat' then t.confidence end) as label_cat,
       max(case when t.value = 'dog' then t.confidence end) as label_dog,
       max(case when t.value = 'person' then t.confidence end) as label_person
from media m left join
     tag t
     on m.id = t.media_it
group by m.id, m.name, m.duration
oprakyz7

oprakyz72#

Select t.media_id,m.name,m.duration,
     case "label_cat " when t.value ='cat' then 
    t.confidence else 0 end case,
    case "label_dog" when t.value ='dog' then 
    t.confidence else 0 end case,
     case "label_person" when t.value ='person' then 
    t.confidence else 0 end case 
    from
    tag t right join media m on t.id=m.id
   group by t.media_id

相关问题