Sql原始查询无法正常工作

Question

我在查询中使用了except子句，但是我得到了如下错误：

查询的文本格式为：

select songs.id, songs.name, songs.artist, songs.album, songs.albumart, songs."albumartThumbnail", cpr.votes, is_requested from (select id, name, artist, album, albumart, "albumartThumbnail" from (select song_id from core_plsongassociation where playlist_id in (1477)) as sinpl
                        left join songs on sinpl.song_id=id where explicit=False
                    ) as songs left join
                    (select song_id, votes, bool_or(thirdpartyuser_id=177) as is_requested from
                        (select * from core_priorityrequests where client_id=2876 and is_played=False
                        ) as clpr left join core_priorityrequests_third_party_user on clpr.id=priorityrequests_id
                        group by priorityrequests_id, song_id, votes
                ) as cpr on songs.id=cpr.song_id 
                EXCEPT
                (select core_blockedsongs_song.song_id from core_blockedsongs 
                join core_blockedsongs_song on core_blockedsongs.id=core_blockedsongs_song.blockedsongs_id where core_blockedsongs.unblock_flag = 'f' and core_blockedsongs.client_id=2870);

上述错误的原因是由于查询中以下和以上的列与except子句不同，即，在except上方写入的查询中的列比except子句后面只有1（一）列的查询中的列多

是否有人可以建议任何变通方法/解决方案，作为对以中编写的查询的改进，除非我不必添加其中的所有列

更新1：

我有我的模型更新如下，现在歌曲字段是外键，早些时候它是多对多字段

  class BlockSong(models.Model):
      client = models.ForeignKey('Client')
      user= models.ForeignKey(settings.AUTH_USER_MODEL)      
      playlist = models.ForeignKey('Playlist', blank=True, null=True)
      song = models.ForeignKey('Song')
      unblock_flag = models.BooleanField(default=False)

答案的问题是，下面答案中给出的查询给出了一个错误

select core_blockedsongs_song.song_id 
  from core_blockedsongs join core_blockedsongs_song 
  on core_blockedsongs.id=core_blockedsongs_song.blockedsongs_id 

当一个字段从多到多字段更新为外键时，您能建议查询中需要做哪些更改吗。此处，歌曲字段已更新

Answer 1

您可以在第二个查询中添加伪null列，以便允许执行语句，如：

EXCEPT
      (select core_blockedsongs_song.song_id, null, null,.... from core_blockedsongs

但我认为这不是你想要的，因为：

EXCEPT returns all rows that are in the result of query1 but not in the result of query2

这意味着将比较整行。
我理解的是，您希望从第一次查询的结果中排除第二次查询返回的所有song_id。
如果是这种情况，那么您可以像这样使用NOT IN：

select songs.id, songs.name, songs.artist, songs.album, songs.albumart, songs."albumartThumbnail", 
       cpr.votes, is_requested 
from (
  select id, name, artist, album, albumart, "albumartThumbnail" 
  from (
    select song_id 
    from core_plsongassociation 
    where playlist_id in (1477)
    ) as sinpl left join songs 
    on sinpl.song_id=id 
    where explicit=False
  ) as songs left join (
    select song_id, votes, bool_or(thirdpartyuser_id=177) as is_requested 
    from (
      select * 
      from core_priorityrequests 
      where client_id=2876 and is_played=False
    ) as clpr left join core_priorityrequests_third_party_user 
    on clpr.id=priorityrequests_id
    group by priorityrequests_id, song_id, votes
) as cpr on songs.id=cpr.song_id 
where songs.id not in (
  select core_blockedsongs_song.song_id 
  from core_blockedsongs join core_blockedsongs_song 
  on core_blockedsongs.id=core_blockedsongs_song.blockedsongs_id 
  where core_blockedsongs.unblock_flag = 'f' and core_blockedsongs.client_id=2870
);

或者使用第二个查询的LEFT JOIN筛选出匹配行：

select songs.id, songs.name, songs.artist, songs.album, songs.albumart, songs."albumartThumbnail", 
       cpr.votes, is_requested 
from (
  select id, name, artist, album, albumart, "albumartThumbnail" 
  from (
    select song_id 
    from core_plsongassociation 
    where playlist_id in (1477)
    ) as sinpl left join songs 
    on sinpl.song_id=id 
    where explicit=False
  ) as songs left join (
    select song_id, votes, bool_or(thirdpartyuser_id=177) as is_requested 
    from (
      select * 
      from core_priorityrequests 
      where client_id=2876 and is_played=False
    ) as clpr left join core_priorityrequests_third_party_user 
    on clpr.id=priorityrequests_id
    group by priorityrequests_id, song_id, votes
) as cpr on songs.id=cpr.song_id 
left join (
  select core_blockedsongs_song.song_id 
  from core_blockedsongs join core_blockedsongs_song 
  on core_blockedsongs.id=core_blockedsongs_song.blockedsongs_id 
  where core_blockedsongs.unblock_flag = 'f' and core_blockedsongs.client_id=2870
) as c on c.song_id = songs.id
where c.song_id is null;