目录

​​目的:​​

​​1、查询不精准虽然SQL真的是很好看，很简洁！​​

​​2、修改版本之后的SQL，更为精准​​

​​3、最后优化的的版本关联，把Group by单独的作为一个整体​​

目的:

今天遇到一个问题，就是一张表是根据news_id来关联多条记录，然后另外一张表希望使用Left JOIN进行关联查询操作，遇到了一个坑，就是Left JOIN 与GROUP BY冲突，需要着实注意。查询解决了好久才算是放弃了最简SQL，使用比较靠谱的SQL来解决这1个问题！

1、查询不精准虽然SQL真的是很好看，很简洁！

SELECT
  count(DISTINCT tnpf.news_id)
FROM
  tbm_news_info tni,
  tbm_news_popular_feelings tnpf
LEFT JOIN tbm_extension_tag extension_tag ON extension_tag.news_id = tnpf.news_id
WHERE
  tnpf.news_id = tni.id
AND tnpf.create_time BETWEEN '2021-04-01 00:00:00'
AND '2021-04-01 23:59:59'
AND tnpf.handle_status = 0
AND extension_tag.tag_type = 1
GROUP BY
  extension_tag.news_id;

2、修改版本之后的SQL，更为精准

SELECT
  tnpf.*
FROM
  tbm_news_info tni,
  tbm_news_popular_feelings tnpf
LEFT JOIN (
  SELECT
    news_id,
    tag_type
  FROM
    tbm_extension_tag extension_tag
  WHERE
    1 = 1
AND extension_tag.tag_type  = 1
  GROUP BY
    news_id
) t1 ON t1.news_id = tnpf.news_id
WHERE
  tnpf.news_id = tni.id
AND tnpf.create_time BETWEEN '2021-03-01 00:00:00'
AND '2021-04-01 23:59:59'
AND tnpf.handle_status = 0

and  t1.news_id   is  null

3、最后优化的的版本关联，把Group by单独的作为一个整体

SELECT
  tnpf.id,
  tnpf.news_id,
  tnpf.handle_status,
  tnpf.publish_status,
  tnpf.create_time,
  tnpf.updater_id,
  tnpf.update_time,
  tnpf.publish_user_id,
  tnpf.publish_date,
  tni.src_publish_date,
  tni.title,
  tni.ds_source_name,
  tni.media_source,
  tni.ds_news_columns,
  tni.author,
  tni.`level` AS info_level,
  tni.ds_source_type,
  tnpf.pub_sentiment_news_type
FROM
  tbm_news_info tni,
  tbm_news_popular_feelings tnpf
LEFT JOIN (
  SELECT
    news_id
  FROM
    tbm_extension_tag extension_tag
  WHERE
    1 = 1
  AND extension_tag.tag_type = 1
  GROUP BY
    news_id
) t1 ON t1.news_id = tnpf.news_id
WHERE
  tnpf.news_id = tni.id
AND tnpf.create_time BETWEEN '2021-04-01 00:00:00'
AND '2021-04-01 23:59:59'
AND tnpf.handle_status =0
AND t1.news_id IS NULL
ORDER BY
  tnpf.priority DESC,
  tnpf.create_time DESC
LIMIT 0,10;

这样查询效果真的很好的解决了1对多的问题！

Left JOIN 与GROUP BY冲突解决办法，俩张表，一张表一对多怎么查询又精准_关联查询