SQL将第二个表中的行计数添加到主查询 [英] SQL add rows count from a second table to the main query

问题描述

我正在尝试改进(不是很多)简单查询:

I'm trying to improve a (not so much) simple query:

• 我需要从表A中检索每一行.
• 然后将表A与表B连接起来，以便获得所需的所有数据.
• 同时，我需要使用表C中的count()添加一个额外的列.

类似的东西:

SELECT a.*,
       (SELECT Count(*)
        FROM   table_c c
        WHERE  c.a_id = a.id) AS counter,
       b.*
FROM   table_a a
       LEFT JOIN table_b b
              ON b.a_id = a.id  

这可以，但是，实际上，我只是在进行2个查询，我需要改进它，因此它只能执行一个查询(如果可能的话).

This works, ok, but in reality, I'm just making 2 queries and I need to improve this so it only do one (if, its even possible).

有人知道我该怎么做到吗?

Anyone knows how can I achive that?

推荐答案

最简单的方法可能就是将相关的子查询移到子查询中.

The simplest approach is likely to just move the correlated sub-query into a sub-query.

注意:许多优化程序都非常有效地处理了相关子查询.您的示例查询可能非常合理.

SELECT
  a.*,
  b.*,
  c.row_count
FROM
  table_a   a
LEFT JOIN
  table_b   b
    ON b.a_id = a.id
LEFT JOIN
(
  SELECT
    a_id,
    Count(*)   row_count
  FROM
    table_c
  GROUP BY
    a_id
)
  c
    ON c.a_id = a.id

另一个注意事项: SQL是一个表达式，不会直接执行，而是使用嵌套循环，哈希联接等将其转换为一个计划.不要假设有两个查询是一件坏事.在这种情况下，与单个查询相比，然后使用GROUP BY和COUNT(DISTINCT).

Another Note: SQL is an expression, it is not executed directly, it is translated into a plan using nest loops, hash joins, etc. Do not assume that having two queries is a bad thing. In this case my example may significantly minimise the number of reads compared to a single query and then use of GROUP BY and COUNT(DISTINCT).

这篇关于SQL将第二个表中的行计数添加到主查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！