{"id":122,"date":"2019-02-28T10:19:51","date_gmt":"2019-02-28T02:19:51","guid":{"rendered":"http:\/\/adeqing.com\/?p=122"},"modified":"2019-02-28T13:29:32","modified_gmt":"2019-02-28T05:29:32","slug":"mysql-%e6%9f%a5%e5%87%ba%e4%b8%80%e5%bc%a0%e8%a1%a8%e4%b8%ad%e9%87%8d%e5%a4%8d%e7%9a%84%e6%89%80%e6%9c%89%e8%ae%b0%e5%bd%95%e6%95%b0%e6%8d%ae","status":"publish","type":"post","link":"https:\/\/adeqing.com\/index.php\/2019\/02\/28\/develop\/mysql\/122\/","title":{"rendered":"mysql \u67e5\u51fa\u4e00\u5f20\u8868\u4e2d\u91cd\u590d\u7684\u6240\u6709\u8bb0\u5f55\u6570\u636e"},"content":{"rendered":"
sql \u67e5\u51fa\u4e00\u5f20\u8868\u4e2d\u91cd\u590d\u7684\u6240\u6709\u8bb0\u5f55\u6570\u636e<\/strong><\/pre>\n
1.\u8868\u4e2d\u6709id\u548ctitle \u4e24\u4e2a\u5b57\u6bb5\uff0c\u67e5\u8be2\u51fatitle\u91cd\u590d\u7684\u6240\u6709\u6570\u636e<\/strong><\/p>\n
\n
\n
\n
\n
\n
select * from ithome a where (a.title) in (select title from ithome group by title having count(*) > 1)<\/code><\/div>\n
<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/blockquote>\n<\/div>\n
2\u3001\u67e5\u8be2\u51fa\u6240\u6709\u6570\u636e\u8fdb\u884c\u5206\u7ec4\u4e4b\u540e\uff0c\u548c\u91cd\u590d\u6570\u636e\u7684\u91cd\u590d\u6b21\u6570\u7684\u67e5\u8be2\u6570\u636e\uff0c\u5148\u5217\u4e0b\uff1a<\/p>\n
\n
\n
select count(id) as '\u91cd\u590d\u6b21\u6570',title from ithome group by title having count(*)>1 order by title desc<\/code><\/div>\n<\/blockquote>\n<\/div>\n
3\u3001\u4e00\u4e0b\u4e3a \u67e5\u770b\u522b\u4eba\u7684 \u7ed3\u679c\uff0c\u73b0\u5217\u4e0b\uff1a\u67e5\u8be2\u53ca\u5220\u9664\u91cd\u590d\u8bb0\u5f55\u7684\u65b9\u6cd5\u5927\u5168<\/p>\n
1\u3001\u67e5\u627e\u8868\u4e2d\u591a\u4f59\u7684\u91cd\u590d\u8bb0\u5f55\uff0c\u91cd\u590d\u8bb0\u5f55\u662f\u6839\u636e\u5355\u4e2a\u5b57\u6bb5\uff08peopleId\uff09\u6765\u5224\u65ad<\/p>\n
\n
\n
\n
\n
\n
select * from ithome
\nwhere title in (select title from ithome group by title having count(title) > 1)<\/code><\/div>\n<\/blockquote>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
2\u3001\u5220\u9664\u8868\u4e2d\u591a\u4f59\u7684\u91cd\u590d\u8bb0\u5f55\uff0c\u91cd\u590d\u8bb0\u5f55\u662f\u6839\u636e\u5355\u4e2a\u5b57\u6bb5\uff08peopleId\uff09\u6765\u5224\u65ad\uff0c\u53ea\u7559\u6709rowid\u6700\u5c0f\u7684\u8bb0\u5f55<\/p>\n
\n
\n
delete<\/code> from<\/code> people <\/code><\/div>\n
where<\/code> peopleId <\/code>in<\/code> (<\/code>select<\/code> peopleId <\/code>from<\/code> people <\/code>group<\/code> by<\/code> peopleId <\/code>having<\/code> count<\/code>(peopleId) > 1)<\/code><\/div>\n
and<\/code> rowid <\/code>not<\/code> in<\/code> (<\/code>select<\/code> min<\/code>(rowid) <\/code>from<\/code> people <\/code>group<\/code> by<\/code> peopleId <\/code>having<\/code> count<\/code>(peopleId )>1)<\/code><\/div>\n<\/blockquote>\n<\/div>\n
3\u3001\u67e5\u627e\u8868\u4e2d\u591a\u4f59\u7684\u91cd\u590d\u8bb0\u5f55\uff08\u591a\u4e2a\u5b57\u6bb5\uff09<\/p>\n
\n
\n
\n
\n
\n
select<\/code> * <\/code>from<\/code> vitae a<\/code><\/div>\n
where<\/code> (a.peopleId,a.seq) <\/code>in<\/code> (<\/code>select<\/code> peopleId,seq <\/code>from<\/code> vitae <\/code>group<\/code> by<\/code> peopleId,seq <\/code>having<\/code> count<\/code>(*) > 1)<\/code><\/div>\n<\/blockquote>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
4\u3001\u5220\u9664\u8868\u4e2d\u591a\u4f59\u7684\u91cd\u590d\u8bb0\u5f55\uff08\u591a\u4e2a\u5b57\u6bb5\uff09\uff0c\u53ea\u7559\u6709rowid\u6700\u5c0f\u7684\u8bb0\u5f55<\/p>\n
\n
\n
\n
\n
\n
delete<\/code> from<\/code> vitae a<\/code><\/div>\n
where<\/code> (a.peopleId,a.seq) <\/code>in<\/code> (<\/code>select<\/code> peopleId,seq <\/code>from<\/code> vitae <\/code>group<\/code> by<\/code> peopleId,seq <\/code>having<\/code> count<\/code>(*) > 1)<\/code><\/div>\n
and<\/code> rowid <\/code>not<\/code> in<\/code> (<\/code>select<\/code> min<\/code>(rowid) <\/code>from<\/code> vitae <\/code>group<\/code> by<\/code> peopleId,seq <\/code>having<\/code> count<\/code>(*)>1)<\/code><\/div>\n<\/blockquote>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
5\u3001\u67e5\u627e\u8868\u4e2d\u591a\u4f59\u7684\u91cd\u590d\u8bb0\u5f55\uff08\u591a\u4e2a\u5b57\u6bb5\uff09\uff0c\u4e0d\u5305\u542browid\u6700\u5c0f\u7684\u8bb0\u5f55<\/p>\n
\n
\n
\n
\n
select<\/code> * <\/code>from<\/code> vitae a<\/code><\/div>\n
where<\/code> (a.peopleId,a.seq) <\/code>in<\/code> (<\/code>select<\/code> peopleId,seq <\/code>from<\/code> vitae <\/code>group<\/code> by<\/code> peopleId,seq <\/code>having<\/code> count<\/code>(*) > 1)<\/code><\/div>\n
and<\/code> rowid <\/code>not<\/code> in<\/code> (<\/code>select<\/code> min<\/code>(rowid) <\/code>from<\/code> vitae <\/code>group<\/code> by<\/code> peopleId,seq <\/code>having<\/code> count<\/code>(*)>1)<\/code><\/div>\n<\/blockquote>\n<\/div>\n<\/div>\n<\/div>\n
(\u4e8c)<\/p>\n
\u6bd4\u65b9\u8bf4<\/p>\n
\u5728A\u8868\u4e2d\u5b58\u5728\u4e00\u4e2a\u5b57\u6bb5\u201cname\u201d\uff0c<\/p>\n
\u800c\u4e14\u4e0d\u540c\u8bb0\u5f55\u4e4b\u95f4\u7684\u201cname\u201d\u503c\u6709\u53ef\u80fd\u4f1a\u76f8\u540c\uff0c<\/p>\n
\u73b0\u5728\u5c31\u662f\u9700\u8981\u67e5\u8be2\u51fa\u5728\u8be5\u8868\u4e2d\u7684\u5404\u8bb0\u5f55\u4e4b\u95f4\uff0c\u201cname\u201d\u503c\u5b58\u5728\u91cd\u590d\u7684\u9879\uff1b<\/p>\n
\n
\n
\n
<\/div>\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
Select<\/code> Name<\/code>,<\/code>Count<\/code>(*) <\/code>From<\/code> A <\/code>Group<\/code> By<\/code> Name<\/code> Having<\/code> Count<\/code>(*) > 1<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n
\u5982\u679c\u8fd8\u67e5\u6027\u522b\u4e5f\u76f8\u540c\u5927\u5219\u5982\u4e0b:<\/p>\n
\n
\n
\n
<\/div>\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
Select<\/code> Name<\/code>,sex,<\/code>Count<\/code>(*) <\/code>From<\/code> A <\/code>Group<\/code> By<\/code> Name<\/code>,sex <\/code>Having<\/code> Count<\/code>(*) > 1<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n
(\u4e09)<\/p>\n
\u65b9\u6cd5\u4e00<\/p>\n
\n
\n
\n
<\/div>\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n<\/td>\n
\n
\n
declare<\/code> @<\/code>max<\/code> integer<\/code>,@id <\/code>integer<\/code><\/div>\n
declare<\/code> cur_rows <\/code>cursor<\/code> local<\/code> for<\/code><\/div>\n
select<\/code> \u4e3b\u5b57\u6bb5,<\/code>count<\/code>(*) <\/code>from<\/code> \u8868\u540d <\/code>group<\/code> by<\/code> \u4e3b\u5b57\u6bb5 <\/code>having<\/code> count<\/code>(*) >;<\/code><\/div>\n
open<\/code> cur_rows <\/code>fetch<\/code> cur_rows <\/code>into<\/code> @id,@maxwhile @@fetch_status=0<\/code><\/div>\n
begin<\/code><\/div>\n
select<\/code> @<\/code>max<\/code> = @<\/code>max<\/code> -1<\/code><\/div>\n
set<\/code> rowcount @<\/code>max<\/code><\/div>\n
delete<\/code> from<\/code> \u8868\u540d <\/code>where<\/code> \u4e3b\u5b57\u6bb5 = @id<\/code><\/div>\n
fetch<\/code> cur_rows <\/code>into<\/code> @id,@maxend<\/code><\/div>\n
close<\/code> cur_rows<\/code><\/div>\n
set<\/code> rowcount 0<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n
\u65b9\u6cd5\u4e8c\uff02\u91cd\u590d\u8bb0\u5f55\uff02\u6709\u4e24\u4e2a\u610f\u4e49\u4e0a\u7684\u91cd\u590d\u8bb0\u5f55\uff0c\u4e00\u662f\u5b8c\u5168\u91cd\u590d\u7684\u8bb0\u5f55\uff0c\u4e5f\u5373\u6240\u6709\u5b57\u6bb5\u5747\u91cd\u590d\u7684\u8bb0\u5f55\uff0c\u4e8c\u662f\u90e8\u5206\u5173\u952e\u5b57\u6bb5\u91cd\u590d\u7684\u8bb0\u5f55\uff0c\u6bd4\u5982Name\u5b57\u6bb5\u91cd\u590d\uff0c\u800c\u5176\u4ed6\u5b57\u6bb5\u4e0d\u4e00\u5b9a\u91cd\u590d\u6216\u90fd\u91cd\u590d\u53ef\u4ee5\u5ffd\u7565\u3002<\/p>\n
1\u3001\u5bf9\u4e8e\u7b2c\u4e00\u79cd\u91cd\u590d\uff0c\u6bd4\u8f83\u5bb9\u6613\u89e3\u51b3\uff0c\u4f7f\u7528<\/p>\n
\n
\n
\n
<\/div>\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
select<\/code> distinct<\/code> * <\/code>from<\/code> tableName<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n
\u5c31\u53ef\u4ee5\u5f97\u5230\u65e0\u91cd\u590d\u8bb0\u5f55\u7684\u7ed3\u679c\u96c6\u3002<\/p>\n
\u5982\u679c\u8be5\u8868\u9700\u8981\u5220\u9664\u91cd\u590d\u7684\u8bb0\u5f55\uff08\u91cd\u590d\u8bb0\u5f55\u4fdd\u75591\u6761\uff09\uff0c<\/p>\n
\u53ef\u4ee5\u6309\u4ee5\u4e0b\u65b9\u6cd5\u5220\u9664<\/p>\n
\n
\n
select<\/code> distinct<\/code> * <\/code>into<\/code> #Tmp <\/code>from<\/code> tableName<\/code><\/div>\n
drop<\/code> table<\/code> tableName<\/code><\/div>\n
select<\/code> * <\/code>into<\/code> tableName <\/code>from<\/code> #Tmp<\/code><\/div>\n
drop<\/code> table<\/code> #Tmp<\/code><\/div>\n<\/blockquote>\n<\/div>\n
\u53d1\u751f\u8fd9\u79cd\u91cd\u590d\u7684\u539f\u56e0\u662f\u8868\u8bbe\u8ba1\u4e0d\u5468\u4ea7\u751f\u7684\uff0c\u589e\u52a0\u552f\u4e00\u7d22\u5f15\u5217\u5373\u53ef\u89e3\u51b3\u3002<\/p>\n
2\u3001\u8fd9\u7c7b\u91cd\u590d\u95ee\u9898\u901a\u5e38\u8981\u6c42\u4fdd\u7559\u91cd\u590d\u8bb0\u5f55\u4e2d\u7684\u7b2c\u4e00\u6761\u8bb0\u5f55\uff0c\u64cd\u4f5c\u65b9\u6cd5\u5982\u4e0b\u3000\u3000\u5047\u8bbe\u6709\u91cd\u590d\u7684\u5b57\u6bb5\u4e3aName,Address\uff0c\u8981\u6c42\u5f97\u5230\u8fd9\u4e24\u4e2a\u5b57\u6bb5\u552f\u4e00\u7684\u7ed3\u679c\u96c6<\/p>\n
\n
\n
select<\/code> identity(<\/code>int<\/code>,1,1) <\/code>as<\/code> autoID, * <\/code>into<\/code> #Tmp <\/code>from<\/code> tableName<\/code><\/div>\n
select<\/code> min<\/code>(autoID) <\/code>as<\/code> autoID <\/code>into<\/code> #Tmp2 <\/code>from<\/code> #Tmp <\/code>group<\/code> by<\/code> Name<\/code>,autoID<\/code><\/div>\n
select<\/code> * <\/code>from<\/code> #Tmp <\/code>where<\/code> autoID <\/code>in<\/code>(<\/code>select<\/code> autoID <\/code>from<\/code> #tmp2)\u3000<\/code><\/div>\n<\/blockquote>\n<\/div>\n
\u6700\u540e\u4e00\u4e2aselect\u5373\u5f97\u5230\u4e86Name\uff0cAddress\u4e0d\u91cd\u590d\u7684\u7ed3\u679c\u96c6\uff08\u4f46\u591a\u4e86\u4e00\u4e2aautoID\u5b57\u6bb5\uff0c\u5b9e\u9645\u5199\u65f6\u53ef\u4ee5\u5199\u5728select\u5b50\u53e5\u4e2d\u7701\u53bb\u6b64\u5217\uff09<\/p>\n
(\u56db)\u67e5\u8be2\u91cd\u590d<\/p>\n
\n
select<\/code> * <\/code>from<\/code> tablename <\/code>where<\/code> id <\/code>in<\/code> (<\/code>select<\/code> id <\/code>from<\/code> tablenamegroup <\/code>by<\/code> idhaving <\/code>count<\/code>(id) > 1)<\/code><\/div>\n<\/blockquote>\n
\u5bf9\u4e00\u4e2a\u5b57\u6bb5\u67e5\u627e\u91cd\u590d\u8bb0\u5f55<\/p>\n
\"\"<\/p>\n
\u6839\u636esample_code\u5b57\u6bb5\u627e\u5230\u91cd\u590d\u8bb0\u5f55<\/p>\n
\n
\n
\n
\n
\n
SELECT<\/code> * <\/code>FROM<\/code> tb_table <\/code>WHERE<\/code> sample_code <\/code>IN<\/code>( <\/code>SELECT<\/code> sample_code <\/code>FROM<\/code> tb_table <\/code>GROUP<\/code> BY<\/code> sample_code <\/code>HAVING<\/code> COUNT<\/code>(sample_code) > 1 )<\/code><\/div>\n<\/blockquote>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\u5bf9\u591a\u4e2a\u5b57\u6bb5\u67e5\u627e\u91cd\u590d\u8bb0\u5f55\uff08\u8fd9\u91cc\u4ee52\u4e2a\u4e3a\u4f8b\uff09<\/p>\n
\"\"<\/p>\n
\u6839\u636ename\u548ccode\u5b57\u6bb5\u627e\u5230\u91cd\u590d\u8bb0\u5f55<\/p>\n
\n
\n
\n
\n
SELECT<\/code> * <\/code>from<\/code> (<\/code>SELECT<\/code> *, CONCAT(<\/code>name<\/code>,code) <\/code>as<\/code> nameAndCode <\/code>from<\/code> tb_table) t <\/code>WHERE<\/code> t.nameAndCode <\/code>in<\/code><\/div>\n
(<\/code><\/div>\n
SELECT<\/code> nameAndCode <\/code>from<\/code> (<\/code>SELECT<\/code> CONCAT(<\/code>name<\/code>,code) <\/code>as<\/code> nameAndCode <\/code>from<\/code> tb_table) tt <\/code>GROUP<\/code> BY<\/code> nameAndCode <\/code>HAVING<\/code> count<\/code>(nameAndCode) > 1<\/code><\/div>\n
)<\/code><\/div>\n<\/blockquote>\n<\/div>\n<\/div>\n<\/div>\n
 <\/p>\n
\u7b80\u5355\u65b9\u6cd5\u5b9e\u73b0<\/p>\n
create table ithome_copy1314 select * from ithome
\nwhere id in (
\nselect max(id) from ithome group by title
\n)<\/p>\n
drop table ithome
\nALTER TABLE ithome_copy1314 RENAME TO ithome;<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
mysql \u67e5\u51fa\u4e00\u5f20\u8868\u4e2d\u91cd\u590d\u7684\u6240\u6709\u8bb0\u5f55\u6570\u636e<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"_links":{"self":[{"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/posts\/122"}],"collection":[{"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/comments?post=122"}],"version-history":[{"count":12,"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/posts\/122\/revisions"}],"predecessor-version":[{"id":134,"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/posts\/122\/revisions\/134"}],"wp:attachment":[{"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/media?parent=122"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/categories?post=122"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/adeqing.com\/index.php\/wp-json\/wp\/v2\/tags?post=122"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}