blocks|key|3776515|text|这是一个操作顺序问题:代码方面，我们必须先添加歌曲，然后才能将它们添加到PlayListSongs中。但是，从用户的角度来看，我们可以允许他们在更新列表的同时将歌曲添加到列表中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3776516|PlayList+(ID,+Name)
1+My+List+1
2+My+List+2

Song+(ID,+Name)
1+Song+A
2+Song+B
3+Song+C


PlayListSongs+(PlayListID,+SongID),+(or+what+you+called+songLog)
1+1
1+2
1+3
2+3|code-block|syntax|javascript|3776517|添加歌曲时，必须先将它们添加到“歌曲”表中，获取ID，然后将关系添加到播放列表中。在用户界面中，您可以同时为用户执行此操作。但是，通过允许他们将歌曲添加到现有的播放列表中，您必须在封面下先将列表添加到“歌曲”表中，然后将其添加到playListSongs中.图示如下。|3776518|事件用户在他们的PlayList“我的列表1”中添加一首新歌“宋D”|3776519|它们与输入所需信息的UI交互。|ordered-list-item|3776520|应用程序调用首先将歌曲信息保存到歌曲表的过程。这导致了“四宋D”的出现和参赛。|3776521|该过程捕获创建的新ID，并与当前选定的播放列表一起使用该ID在PlayListSongs+(1,4)中放置一个条目。|3776522|3776523|3776524|事件-用户只想添加一堆歌曲1。我们保存歌曲信息，使用进入歌曲表。|3776525|事件-用户希望创建一个带有歌曲1的新播放列表。我们在playlist+2中插入一个新记录。我们允许用户选择现有歌曲(这只会导致条目3+)。我们允许用户动态创建新歌曲，添加到歌曲表中，并自动添加到所选播放列表中。|3776526|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|11|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|12|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|13|8|@]|9|@]|A|$]]|$1|N|3|O|5|M|7|14|8|@]|9|@]|A|$]]|$1|P|3|Q|5|M|7|15|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|17|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|18|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|19|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|Y|$]]

This is an order of operation question: Codewise we must first add songs before they can add them to the PlayListSongs. However from the users perspective we may allow them to add songs to lists at the same time we are updating the list.

<pre><code>PlayList (ID, Name)
1 My List 1
2 My List 2

Song (ID, Name)
1 Song A
2 Song B
3 Song C


PlayListSongs (PlayListID, SongID), (or what you called songLog)
1 1
1 2
1 3
2 3
</code></pre>

When adding songs, you must first Add them to the songs table get an ID and then add the relationship to the playlist. From a user interface you could do this all at once for the user. By allowing them to add songs to an existing playlist, However, you MUST, under the covers, first add the list to the songs table, then add it to the playListSongs... illustration below.

Event - user adds a new song "Song D" to their PlayList "My List 1"

<ol>
<li>They interact with the UI entering in needed information</li>
<li>The application calls a procedure which first saves the song information to the song table. This results in and entry "4 Song D"</li>
<li>The procedure captures the NEW ID created and uses it along with the currently selected playlist to put an entry in PlayListSongs (1,4)</li>
</ol>

Event - User just wants to add a bunch of songs
 1. We save the song information use enters into song table

Event - User wants to create a new playlist with songs
 1. We insert a new record in PlayList
 2. We allow user to select existing songs (this just results in entries
 3. we allow user to dynamically create new songs add to song table and automatically add to playlist selected.

blocks|key|3776547|text|对于您的交叉表，您可能有如下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3776548|歌曲|offset|length|style|BOLD|3776549|id
title
artist
length|code-block|syntax|javascript|3776550|播放列表|3776551|id
title|3776552|playlists_songs|3776553|song_id
playlist_id
order|3776554|对于应用程序中的每一首歌曲，都有一行。对于每个播放列表，将有一行。对于任何给定播放列表中的歌曲，都会有多行。一旦在各自的表中有了歌曲和播放列表行，就可以将歌曲添加到播放列表中。要做到这一点，您需要将一行插入到playlists_songs表中，并为添加操作使用song_id+(它们将不同)和playlist_id+(它们将是相同的)。此外，该表将歌曲的顺序存储在播放列表中，以便以后对它们进行排序。查询可能如下所示：|CODE|3776555|INSERT+INTO+playlists_songs+(song_id,+playlist_id,+order)+VALUES+(1,+1,+1)
INSERT+INTO+playlists_songs+(song_id,+playlist_id,+order)+VALUES+(2,+1,+2)
INSERT+INTO+playlists_songs+(song_id,+playlist_id,+order)+VALUES+(3,+1,+3)
INSERT+INTO+playlists_songs+(song_id,+playlist_id,+order)+VALUES+(4,+1,+4)|3776556|上面的歌曲将添加id为1、2、3和4的歌曲到id为1的播放列表中。要获得播放列表1的歌曲，可以执行以下操作：|3776557|SELECT+s.*
FROM+songs+AS+s
++++INNER+JOIN+playlists_songs+AS+p+
++++++++ON+p.song_id+=+s.song_id
WHERE+p.playlist_id+=+1
ORDER+BY+p.order|3776558|entityMap^0|0|0|2|0|0|0|4|0|0|0|F|0|0|B|2|N|4|2W|F|3M|7|42|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|16|8|@$D|17|E|18|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|19|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1A|8|@$D|1B|E|1C|F|G]]|9|@]|A|$]]|$1|O|3|P|5|J|7|1D|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1E|8|@$D|1F|E|1G|F|G]]|9|@]|A|$]]|$1|S|3|T|5|J|7|1H|8|@]|9|@]|A|$K|L]]|$1|U|3|V|5|6|7|1I|8|@$D|1J|E|1K|F|G]|$D|1L|E|1M|F|G]|$D|1N|E|1O|F|G]|$D|1P|E|1Q|F|W]|$D|1R|E|1S|F|W]]|9|@]|A|$]]|$1|X|3|Y|5|J|7|1T|8|@]|9|@]|A|$K|L]]|$1|Z|3|10|5|6|7|1U|8|@]|9|@]|A|$]]|$1|11|3|12|5|J|7|1V|8|@]|9|@]|A|$K|L]]|$1|13|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]]|14|$]]

For your intersection tables you may have something like:

songs

<pre><code>id
title
artist
length
</code></pre>

playlists

<pre><code>id
title
</code></pre>

playlists_songs

<pre><code>song_id
playlist_id
order
</code></pre>

For each song in your application there will be one row. For each playlist there will be one row. For the songs in any given playlist there will be multiple rows. Once you have rows for songs and playlists in their respective tables you can add songs to a playlist. To do so you need to insert a row into the playlists_songs table with the <code>song_id</code> (these will be different) and the <code>playlist_id</code> (these will be the same) for the adding operation. Additionally the table stores the order for the song in the playlist so that you can sort them later. The queries might look like:

<pre><code>INSERT INTO playlists_songs (song_id, playlist_id, order) VALUES (1, 1, 1)
INSERT INTO playlists_songs (song_id, playlist_id, order) VALUES (2, 1, 2)
INSERT INTO playlists_songs (song_id, playlist_id, order) VALUES (3, 1, 3)
INSERT INTO playlists_songs (song_id, playlist_id, order) VALUES (4, 1, 4)
</code></pre>

The above will add songs with id of 1, 2, 3, and 4 to the playlist with id of 1. To get the songs for playlist 1 you could then do something like:

<pre><code>SELECT s.*
FROM songs AS s
 INNER JOIN playlists_songs AS p 
 ON p.song_id = s.song_id
WHERE p.playlist_id = 1
ORDER BY p.order
</code></pre>

blocks|key|3776579|text|让我们把这个问题分解一下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3776580|你有歌。|3776581|CREATE+TABLE+Songs
(
++SongID+int,
++SongName+varchar(50)
);|code-block|syntax|javascript|3776582|你有播放列表的信息。(播放列表的名字，是谁创建的，随便吧)。|3776583|CREATE+TABLE+PlaylistDef
(
++PlaylistID+int,
++PlaylistName+varchar(50)++
);|3776584|你的歌都在播放列表里。歌曲可以在多个播放列表中。|3776585|CREATE+TABLE+Playlist
(
++PlaylistID+int,
++SongID+int
);|3776586|让我们填充表：|3776587|INSERT+INTO+Songs
(SongID,+SongName)
VALUES
(1,+'A'),
(2,+'B'),
(3,+'C'),
(4,+'D'),
(5,+'E');

INSERT+INTO+PlaylistDef
(PlaylistID,+PlaylistName)
VALUES
(1,+'a'),
(2,+'b');


INSERT+INTO+Playlist
(PlaylistID,+SongID)
VALUES
(1,+1),
(1,+2),
(1,+3),
(1,+4),
(2,+2),
(2,+3),
(2,+4),
(2,+5);|3776588|然后，您现在可以运行类似的东西来查看特定的播放列表。|3776589|SELECT+*+FROM+Playlist+WITH+(NOLOCK)+WHERE+PlaylistID+=+2|3776590|它会把这个还回去|3776591|PLAYLISTID++SONGID
2+++++++++++2
2+++++++++++3
2+++++++++++4
2+++++++++++5|3776592|这个有用吗？|3776593|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|18|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|19|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1A|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|1B|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|1C|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|1D|8|@]|9|@]|A|$]]|$1|S|3|T|5|F|7|1E|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|1F|8|@]|9|@]|A|$]]|$1|W|3|X|5|F|7|1G|8|@]|9|@]|A|$G|H]]|$1|Y|3|Z|5|6|7|1H|8|@]|9|@]|A|$]]|$1|10|3|11|5|F|7|1I|8|@]|9|@]|A|$G|H]]|$1|12|3|13|5|6|7|1J|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|15|$]]

Lets break down the problem a little bit.

You've got songs.

<pre><code>CREATE TABLE Songs
(
 SongID int,
 SongName varchar(50)
);
</code></pre>

You've got playlist information. (Name of the playlist, who created it, blahwhatever).

<pre><code>CREATE TABLE PlaylistDef
(
 PlaylistID int,
 PlaylistName varchar(50) 
);
</code></pre>

You've got songs who are in playlists. Songs can be in multiple playlists.

<pre><code>CREATE TABLE Playlist
(
 PlaylistID int,
 SongID int
);
</code></pre>

Lets populate the tables:

<pre><code>INSERT INTO Songs
(SongID, SongName)
VALUES
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E');

INSERT INTO PlaylistDef
(PlaylistID, PlaylistName)
VALUES
(1, 'a'),
(2, 'b');


INSERT INTO Playlist
(PlaylistID, SongID)
VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(2, 2),
(2, 3),
(2, 4),
(2, 5);
</code></pre>

Then you can now run something like this to see a specific playlist.

<pre><code>SELECT * FROM Playlist WITH (NOLOCK) WHERE PlaylistID = 2
</code></pre>

And it'll return this:

<pre><code>PLAYLISTID SONGID
2 2
2 3
2 4
2 5
</code></pre>

Does this help?

With a many to many relationship, an intersection table is used to resolve this to two one-to-many relationships. My problem here is that I currently have 2 tables, "Playlist" and "song". Each song be present in many playlists, and each playlist can contain many songs. To resolve this add a third table, "songlog" to resolve the many to many. 

I understand this much, however, when it comes to adding values into the database, I'm a tad confused. For example, how would I add some (e.g 4) songs into the playlist table, using the intersection table? Also, how would I then add the details of which songs are in which playlist?
Have I got this very wrong, is this a true many to many, or am I barking up the wrong tree entirely?

I'm very confused, so a clear and basic explanation of this area of design may help! Many thanks.

SQL: many to many resolution and intersection entities

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

对于多到多的关系，相交表被用来解决这两个一对多的关系。我的问题是，我现在有两个表，“播放列表”和“歌曲”。每首歌都出现在许多播放列表中，每一首播放列表都可以包含许多歌曲。为了解决这个问题，添加了第三个表，“歌曲日志”来解决多到多的问题。但是，当涉及到向数据库中添加值时，我有点困惑。例如，如何使用相交表将一些(例如4)歌...

问SQL:多到多分辨率和交叉实体
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL:多到多分辨率和交叉实体EN