需要帮助来理解具有多个联接条件的复杂查询 [英] Need help understanding a complex query with multiple join conditions

问题描述

我有一个要理解的查询.有人可以阐明此查询的详细信息吗?
我只在联接条件中使用过一个ON子句.该LEFT JOIN具有多个条件，很难理解.

I have a query that I am trying to understand. Can someone shed light on to the details of what this query does?
I've only ever used one ON clause in a join condition. This one has multiple conditions for the LEFT JOIN, making it tricky to understand.

INSERT INTO nop_tbl
          (q_date, community_id, newsletter_t, subscription_count)     
  SELECT date(now()), a.community_id, 
     a.newsletter_type, 
     count(a.subscriber_user_id)
   FROM
      newsletter_subscribers_main a
   LEFT OUTER JOIN nop_tbl b
       ON (a.community_id = b.community_id)
       AND (a.newsletter_type = b.newsletter_t)
       AND (a.created_at = b.q_date)
   WHERE b.q_date is null
   AND   b.mailing_list is null
GROUP BY a.community_id, a.newsletter_t, a.created_at

推荐答案

您的解释是

查询的目的是对newsletter_subscribers_main中每个(q_date, community_id, newsletter_t)的订阅计数，并将结果写入nop_tbl.
LEFT JOIN防止多次添加行.

The objective of the query is to count subscriptions per (q_date, community_id, newsletter_t) in newsletter_subscribers_main and write the result to nop_tbl.
The LEFT JOIN prevents that rows are added multiple times.

但是我也认为，查询效率低下，并且可能是错误的.

But I also think, the query is inefficient and probably wrong.

• 第二个WHERE子句:

AND   b.mailing_list is null

只是噪音，可以删除.如果为b.q_date is null，则保证该查询中的b.mailing_list为空.

is just noise and can be removed. If b.q_date is null, then b.mailing_list is guaranteed to be null in this query.

您不需要在JOIN条件周围加括号.

You don't need parentheses around JOIN conditions.

如果将subscriber_user_id定义为NOT NULL，则count(*)会执行相同的操作，并且更便宜.

If subscriber_user_id is defined NOT NULL, count(*) does the same, cheaper.

我怀疑在插入date(now())时按a.created_at分组可能是错误的.几乎没有任何意义.我的有根据的猜测(假设created_at是date类型):

I suspect that grouping by a.created_at, while you insert date(now()) is probably wrong. Hardly makes any sense. My educated guess (assuming that created_at is type date):

INSERT INTO nop_tbl
      (q_date, community_id, newsletter_t, subscription_count)     
SELECT a.created_at
      ,a.community_id
      ,a.newsletter_type
      ,count(*)
FROM   newsletter_subscribers_main a
LEFT   JOIN nop_tbl b ON a.community_id = b.community_id
                     AND a.newsletter_type = b.newsletter_t
                     AND a.created_at = b.q_date
WHERE  b.q_date IS NULL
GROUP  BY a.created_at, a.community_id, a.newsletter_t;

这篇关于需要帮助来理解具有多个联接条件的复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！