时间戳

首页 > 解决方案 > 如何在多对多关系中获得总数？

python - 如何在多对多关系中获得总数？

问题描述

我正在使用 wxpython 和 sqlite3 构建一个简单的歌曲管理器。

有以下几张表

歌曲和文件具有一对一的关系。

歌曲和标签具有多对多关系。

歌曲和播放列表具有多对多关系。

您可以在下面看到我正在使用的表查询：

create_songs_table_query = """ CREATE TABLE IF NOT EXISTS songs (
                                song_id integer PRIMARY KEY AUTOINCREMENT,
                                title text NOT NULL,
                                artist text NOT NULL,
                                added_timestamp integer NOT NULL,
                                file_id INTEGER NULL,
                                    FOREIGN KEY (file_id)
                                    REFERENCES files (file_id)
                                    ON DELETE CASCADE
                    ); """

create_files_table_query = """ CREATE TABLE IF NOT EXISTS files (
                                        file_id integer PRIMARY KEY AUTOINCREMENT,
                                        filename text NULL,
                                        size integer NULL,
                                        song_id INTEGER NOT NULL,
                                            FOREIGN KEY (song_id)
                                            REFERENCES songs (song_id)
                                            ON DELETE CASCADE                                
                                ); """

create_tags_table_query = """CREATE TABLE IF NOT EXISTS tags (
                                 tag_id integer PRIMARY KEY AUTOINCREMENT,
                                 tag_text  text NOT NULL,
                                 tag_timestamp integer NULL,

                                ); """

create_songs_tags_table_query = """CREATE TABLE IF NOT EXISTS songs_tags (
                                    song_tag_id  integer PRIMARY KEY AUTOINCREMENT,
                                    song_id INTEGER NOT NULL,
                                            FOREIGN KEY (song_id)
                                            REFERENCES songs (song_id)
                                            ON DELETE CASCADE,  
                                    tag_id INTEGER NOT NULL,
                                            FOREIGN KEY (tag_id)
                                            REFERENCES tags (tag_id)
                                            ON DELETE CASCADE  
                                    ); """

create_playlists_table_query = """CREATE TABLE IF NOT EXISTS playlists (
                                      playlist_id  integer PRIMARY KEY AUTOINCREMENT,
                                      playlist_title text NOT NULL,
                                      created_timestamp  INTEGER NOT NULL,
                                      updated_timestamp  INTEGER NULL,
                                    ); """

create_songs_playlists__table_query = """CREATE TABLE IF NOT EXISTS songs_playlists (
                                            song_playlist_id integer PRIMARY KEY AUTOINCREMENT,
                                            song_id INTEGER NOT NULL,
                                                    FOREIGN KEY (song_id)
                                                    REFERENCES songs (song_id)
                                                    ON DELETE CASCADE,
                                            playlist_id INTEGER NOT NULL,
                                                        FOREIGN KEY (playlist_id)
                                                        REFERENCES playlists (playlist_id)
                                                        ON DELETE CASCADE  
                                        ); """

我正在尝试获取每个播放列表总共有多少首歌曲，包括是否有任何播放列表有 0 首歌曲。

我使用以下查询似乎返回了所需的结果：

SELECT playlists.playlist_id, playlists.playlist_title, COUNT(songs.song_id) as total
FROM playlists 
LEFT OUTER JOIN songs_playlists 
ON playlists.playlist_id = songs_playlists.playlist_id 
LEFT OUTER JOIN songs
ON songs_playlists.song_id = songs.song_id
GROUP BY (songs.song_id)
ORDER BY total DESC

虽然，我不确定它是否完全正确，或者可能有更简单或更有效的方法来做到这一点。

标签： pythonsqlsqlitegroup-bycount

解决方案

我正在尝试获取每个播放列表总共有多少首歌曲，包括是否有任何播放列表有 0 首歌曲。

您确实可以从playliststable 开始，然后带songs_playlistsa left join，然后聚合。但是，您不需要songs表格来获得您想要的结果，更重要的是，您需要group by播放列表，而不是按歌曲：

select p.playlist_id, p.playlist_title, count(sp.playlist_id) no_songs
from playlists p
left join songs_playlists sp on sp.playlist_id = p.playlist_id
group by p.playlist_id, p.playlist_title

相关子查询也可能是一种可接受的方法，因为它避免了外部聚合的需要：

select
    p.*,
    (select count(*) from songs_playlists sp where sp.playlist_id = p.playlist_id) no_songs
from playlists p

推荐阅读