目录

目的:

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对多的问题！