代码之家  ›  专栏  ›  技术社区  ›  stack

如何减少子查询中的连接?

  •  0
  • stack  · 技术社区  · 7 年前

    SELECT q.type,
          q.author_id author,
          q.deleted,
          q.deleter_id,
          COUNT(DISTINCT a.id) answers_num,
          COUNT(DISTINCT v.id) votes_num,
    FROM qanda q
    LEFT  JOIN qanda a ON q.id = a.related
    LEFT  JOIN votes v on q.id = v.post_id
    WHERE q.id = ?
    

    它工作得很好。我只需要再加一件事(返回一个布尔值 EXISTS SELECT 陈述这:

    EXISTS (SELECT a.id, sum(vot.value) total_votes
            FROM qanda q
            LEFT JOIN qanda a on q.id = a.related
            LEFT JOIN votes vot ON a.id = vot.post_id
            WHERE q.type = 0         -- this is a question
              AND a.related = 1315   -- getting answers
            GROUP BY a.id
            HAVING total_votes
            ORDER BY total_votes DESC
            LIMIT1
    ) as HasAUpvotedAnswer
    

    好的,它也很好用。但我担心表现。正如你所见 JOIN

    0 回复  |  直到 6 年前
        1
  •  1
  •   ScaisEdge    7 年前

    在代码中,y可以使用单个查询,使用条件求和

    SELECT q.type,
          q.author_id author,
          q.deleted,
          q.deleter_id,
          COUNT(DISTINCT a.id) answers_num,
          COUNT(DISTINCT v.id) votes_num,
          IF (SUM(
            CASE WHEN q.type  = 0 AND a.related = 1315
              THEN v.value  else  0 END) > 0,1,0) 
          HasAUpvotedAnswer
    FROM qanda q
    LEFT  JOIN qanda a ON q.id = a.related
    LEFT  JOIN votes v on q.id = v.post_id
    WHERE q.id = ?