mysql查找重复字段的两种方法。

为了查找字段是否重复的字段内容。

SELECT `Formats_text_url`, COUNT(*) as count FROM `home_page_info_update` GROUP BY `Formats_text_url` HAVING count>1;

　　

上面这条语句能够查寻出重复字段的字段内容，但不能显式全部字段的信息，所以做了改进

 

查寻字段重复内容
SELECT * FROM `home_page_info_update` 
		WHERE `Formats_text_url` IN 
					(SELECT `Formats_text_url` FROM `home_page_info_update` GROUP BY `Formats_text_url` HAVING COUNT(`Formats_text_url`)>1);

SELECT * FROM `home_page_info_update`
		INNER JOIN
				(SELECT `Formats_text_url` FROM `home_page_info_update` GROUP BY `Formats_text_url` HAVING COUNT(`Formats_text_url`)>1) AS tb2 USING(`Formats_text_url`);


查寻sec_insider_info_detail中的重复字段.
SELECT * FROM `sec_insider_info_detail`
    INNER JOIN
		     (SELECT `info_url` FROM `sec_insider_info_detail` GROUP BY `info_url` HAVING COUNT(`info_url`)>1) AS 
				 tb2 USING(`info_url`) ORDER BY `sec_insider_info_detail`.`reporteddatetime` DESC;

SELECT * FROM `sec_insider_info_detail`
    WHERE `info_url` IN
		     (SELECT `info_url` FROM `sec_insider_info_detail` GROUP BY `info_url` HAVING COUNT(`info_url`)>1);

　　

上面分别通过两种方式选出重复字段的所有字段信息，一种采用子查寻集，然后通过IN的方式查出需要的查寻结构。

一种通过将子查询定义成一份新的表数据，通过两个表的连表查寻来显式需要的结果。[会稍微多一些字段信息]

 

两种方式，第二种方式的查寻效果快很多，推荐第二种使用连表查寻。