如何通过条件从数据库中排列 - 另一个表中的另一个存在

Question

我有两个表notifications和archived_notifications。

通知

id | title | caption | user_id

存档通知

notification_id | user_id

如果我想从 notifications表中获得行中的行， archived_notifications表中没有行，例如 notification_id = notification.id | user_id = notification.user_id。

现在我有一些喜欢的

cursor.execute("SELECT * FROM notifications AS n WHERE "
                   "({}=%s AND (SELECT COUNT(*) FROM archvies WHERE {}=n.id AND {}=%s) = 0) "
                   "OR "
                   "({}=%s AND (SELECT COUNT(*) FROM archvies WHERE {}=n.id AND {}=%s) = 0) "
                   "ORDER by id DESC LIMIT %s OFFSET %s"
                   .format(Keys.USER_ID, Keys.NOTIFICATION_ID, Keys.USER_ID, Keys.DIRECTION, Keys.NOTIFICATION_ID,
                           Keys.USER_ID),
                   [str(user_id), str(user_id), NotificationsClasses.GLOBAL, str(user_id), int(limit), int(offset)])

Answer 1

通常，您可以使用"不存在"中的任何一个，而不是左JON CON JOIN子句。具体来说，您似乎还有许多其他不清楚的条件，例如方向 的处理和字符串格式，从未知键变量的列名称。

下面显示了NOT EXISTS选项，并尝试翻译当前代码。适应实际需求：

sql = '''SELECT * FROM notifications AS n 
         WHERE n.user_id = %s
           AND NOT EXISTS
              (SELECT 1 FROM archives a
               WHERE a.user_id = %s
                 AND n.id = a.notification_id)
         ORDER by n.id DESC 
         LIMIT %s 
         OFFSET %s
      '''
cursor.execute(sql, [str(user_id), NotificationsClasses.GLOBAL, int(limit), int(offset)])