问题描述
我想从连接在一起的3个不同的桌子中选择3列。
我有一张专辑表，有genid，genreid和名字，一个流派表有genreid和名字，我把它们加在一起如下：
Hi,
I want to select 3 columns from 3 different tables that are joined together.
I have an albums table that has albumid, genreid and name, and a genre table has genreid and name and Ive joined them together as follows:
SELECT a.albumid, a.name, g.name as genrename
FROM albums a
JOIN genre g
ON a.genreid=g.genreid
ORDER BY a.name;
但是，我还有一个名为track的表，其中包含与各自专辑相关联的曲目（由albumid链接，存在在两个表中）。在曲目表中，每个曲目都有歌曲的持续时间。
我想要实现的是，我已经拥有的SQL，我想得到一个所有曲目持续时间的总和加在一起以获得该专辑的总数，然后可以显示为专辑持续时间。
这样的东西，但有某种用于计算专辑持续时间的子查询：
However, I have another table called tracks that contains the tracks linked with their respective album (linked by albumid which is present in both tables). in the tracks table each track has a duration for the song.
What I am trying to achieve is, with the SQL I already have, I would like to get a sum of all the track durations added together to get a total for that album, which can then be displayed as album duration.
Something like this, but with some kind of subquery to calculate album duration:
SELECT a.albumid, a.name, g.name as genrename
FROM albums a
JOIN genre g
ON a.genreid=g.genreid
JOIN tracks t
ON a.albumid=t.albumid
ORDER BY a.name;推荐答案
SELECT a.albumid, a.name, g.name as genrename, sum(t.duration) as albumduration
FROM albums a
JOIN genre g
ON a.genreid=g.genreid
JOIN tracks t
ON a.albumid=t.albumid
GROUP BY a.albumid, a.name, g.name
ORDER BY a.name;
这篇关于如何从数据库中共享类似ID的多个值中获取总金额？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！