给出了两个表,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 |
+----------+---------+----------+-----------+-----------+--------------+
2条答案
按热度按时间hfsqlsce1#
如果我理解正确,您需要条件聚合:
pxq42qpu2#