我应该使用什么 SQL 操作？

发布于 2024-10-13 06:48:43 字数 402 浏览 0 评论 0原文

我有两个表：

TABLE 'songs'
song_id   --some other columns about this song
-------
1
2
3

TABLE 'song_ownership'
user_id      song_id
-------      -------
28           1
28           3
538          1
234          2

我有兴趣执行查询，在给定 user_id 的情况下，我返回他们拥有的所有歌曲。例如，您可以看到用户 28 拥有两首歌曲。

顺便说一句，这是我所知道的如何规范化该表的最好方法，并且愿意接受有关如何更传统地存储该数据的建议（我刚刚学习 SQL）。这是典型的桌子设置吗？

原文

I have two tables:

TABLE 'songs'
song_id   --some other columns about this song
-------
1
2
3

TABLE 'song_ownership'
user_id      song_id
-------      -------
28           1
28           3
538          1
234          2

I'm interested in performing a query where given a user_id I'm returned all of the songs that they own. You can see that user 28 owns two songs, for example.

Incidentally, this is the best I know how to normalize this table and am open to suggestions on how to store this data more conventionally (I'm just learning SQL). Is this a typical table setup?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

但可醉心 2024-10-20 06:48:43

select songs.*
from songs
inner join song_ownership on song_ownership.song_id = songs.song_id and     
song_ownership.user_id=@user_id

假设同一用户不能两次拥有同一首歌曲。到目前为止，您的标准化看起来很好！您的 Song_ownership 表将是一个“多对多”表，并且（如果歌曲-用户关联是唯一的），您可以在两列上放置复合主键，并且您的用户将位于单独的表中。

select songs.*
from songs
inner join song_ownership on song_ownership.song_id = songs.song_id and     
song_ownership.user_id=@user_id

Assuming that the same user can't own the same song twice. Your normalization looks fine so far! Your song_ownership table will be a "many-to-many" table, and (if a song-user association is unique), you can put a compound primary key on both columns, and your users will be in a separate table.

回复收藏 0 原文

零時差 2024-10-20 06:48:43

select * -- choose only the columns you're interested to, like user id and song name
from songs
     inner join song_ownership on song_ownership.song_id=songs.song_id
where song_ownership.user_id=?

select * -- choose only the columns you're interested to, like user id and song name
from songs
     inner join song_ownership on song_ownership.song_id=songs.song_id
where song_ownership.user_id=?

回复收藏 0 原文

~没有更多了~