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

阳光穿透心脏的1/2处 2022-11-15 14:18 104阅读 0赞

**目录**

目的:

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

![20210401212823451.png][]

[20210401212823451.png]: /images/20221022/8c411a4d3dab47d1bfe86c5290bce92c.png