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

w6lpcovy 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(402)

给出了两个表，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         |
+----------+---------+----------+-----------+-----------+--------------+

sql mysql Join ifnull

来源：https://stackoverflow.com/questions/53905114/sql-query-to-return-specific-labels-if-exist-0-if-it-does-not-exist

2条答案

按热度按时间

hfsqlsce1#

如果我理解正确，您需要条件聚合：

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

赞(0）回复(0）举报 2021-06-16

pxq42qpu2#

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

赞(0）回复(0）举报 2021-06-16

我来回答

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

2条答案

相关问题

热门标签

最新问答