Sql原始查询不能正常工作

Question

我在查询中使用了get子句，但是我得到一个错误，如下所示：

查询的文本格式是：

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);

出现上述错误的原因是查询中的列(低于)和上的列()(除了子句)中的列不同，也就是说，在仅包含1(1)列的just子句之后，所写查询中的列比查询后面的列更多。

除了我不需要在查询中添加所有列之外，还有人可以提出任何解决办法/解决方案作为查询的改进吗？

更新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

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

除外，返回query1结果中的所有行，但不返回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;