mysql 查询语句去重 通过sql去重

url 在表中具有唯一性；

由于定时任务的多进程/代码的多线程/定时任务的调度/脚本的执行时间，导致，破坏了唯一性；

SELECT COUNT(1) FROM test_error_tmp

UNION ALL

SELECT COUNT(DISTINCT url) FROM test_error_tmp;

COUNT(1)

40972

40910

差62

SELECT * FROM (

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1;

;

c url

2 b2b.88152.com/show-2677485.html

2 b2b.88152.com/show-2989753.html

2 b2b.88152.com/show-2994375.html

2 b2b.88152.com/show-3071983.html

2 haikou.kuyiso.com/huizhan/29251188x.htm

2 info.123cha.com/show_1007121.html

2 info.b2b168.com/s168-48159518.html

2 info.b2b168.com/s168-58451934.html

2 info.b2b168.com/s168-60677934.html

2 lanzhou.kuyiso.com/canyin/1328.htm

2 site.leshou.com/s/39010445.html

2 site.leshou.com/s/40131383.html

2 site.leshou.com/s/45062793.html

2 site.leshou.com/s/45714581.html

2 site.leshou.com/s/46021681.html

2 site.leshou.com/s/49557679.html

2 site.leshou.com/s/50777239.html

2 site.leshou.com/s/52407278.html

2 wap.ailaba.org/sell/182965618167809.html

2 www.01ny.cn/xinxi/25324778.html

2 www.01ny.cn/xinxi/37941846.html

2 www.05188.com/fenlei/22739365.html

2 www.51sole.com/b2b/pd_26691448.htm

2 www.587766.com/gongqiu/show_2096363.html

2 www.afinance.cn/fenlei/25435008.html

2 www.afinance.cn/fenlei/37074884.html

2 www.afinance.cn/fenlei/38574413.html

2 www.ailaba.org/sell/179521059297846.html

2 www.ailaba.org/sell/183760178499696.html

2 www.bzcm.net/xinxi/29968353.html

2 www.cnhan.com/fenlei/34281043.html

2 www.cntrades.com/b2b/hnszbjh/sell/itemid-85394203.html

2 www.gtobal.com/?m=trade&c=detail&a=preview&id=4930157402&type=2

2 www.gtobal.com/?m=trade&c=detail&a=preview&id=5060488002&type=2

2 www.hizh.cn/xinxi/10229031.html

2 www.hizh.cn/xinxi/3524483.html

2 www.kuyiso.com/html/101/ershoushebei/18348452x.htm

2 www.kuyiso.com/html/21/zhiyepx/29045579x.htm

2 www.laiwunews.cn/xinxi/13358851.html

2 www.laiwunews.cn/xinxi/18715417.html

2 www.laiwunews.cn/xinxi/18774478.html

2 www.laiwunews.cn/xinxi/18917168.html

2 www.laiwunews.cn/xinxi/19054895.html

2 www.laiwunews.cn/xinxi/20974600.html

2 www.laiwunews.cn/xinxi/20975830.html

2 www.laiwunews.cn/xinxi/21115975.html

2 www.laiwunews.cn/xinxi/21361687.html

2 www.laiwunews.cn/xinxi/21416448.html

2 www.laiwunews.cn/xinxi/21512940.html

2 www.laiwunews.cn/xinxi/25450210.html

2 www.laiwunews.cn/xinxi/27248072.html

2 www.laiwunews.cn/xinxi/27340981.html

2 www.qhnews.com/bianmin/30874042.html

2 www.qhnews.com/bianmin/32388347.html

2 www.qhnews.com/bianmin/32550280.html

2 www.qixiangwang.cn/fenlei/20773619.html

2 www.qiyegu.com/sell/201412/13/deatil_10657611.html

2 www.sooshong.com/chxchx/offerdetail-150433811437054.html

2 www.waaku.com/infoview11788636.html

2 www.yuqinge.com/xinxi/4683460.html

2 www.zznews.cn/xinxi/21212448.html

2 yt.qu114.com/zhuanli/37012081.html

恰好共计62条

现在通过sql删除62条，注意不是62*2条

删除每个url出现晚的数据行

先选出主键id，再执行删除

SELECT id,url FROM test_error_tmp WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

)

;

id url

2204 b2b.88152.com/show-2677485.html

8647 b2b.88152.com/show-2677485.html

2293 b2b.88152.com/show-2989753.html

8426 b2b.88152.com/show-2989753.html

2318 b2b.88152.com/show-2994375.html

8654 b2b.88152.com/show-2994375.html

2220 b2b.88152.com/show-3071983.html

8662 b2b.88152.com/show-3071983.html

1264 haikou.kuyiso.com/huizhan/29251188x.htm

8477 haikou.kuyiso.com/huizhan/29251188x.htm

1935 info.123cha.com/show_1007121.html

8603 info.123cha.com/show_1007121.html

1887 info.b2b168.com/s168-48159518.html

9577 info.b2b168.com/s168-48159518.html

3000 info.b2b168.com/s168-58451934.html

8474 info.b2b168.com/s168-58451934.html

9428 info.b2b168.com/s168-60677934.html

9431 info.b2b168.com/s168-60677934.html

2210 lanzhou.kuyiso.com/canyin/1328.htm

8674 lanzhou.kuyiso.com/canyin/1328.htm

1909 site.leshou.com/s/39010445.html

9076 site.leshou.com/s/39010445.html

1867 site.leshou.com/s/40131383.html

9089 site.leshou.com/s/40131383.html

1885 site.leshou.com/s/45062793.html

9086 site.leshou.com/s/45062793.html

1859 site.leshou.com/s/45714581.html

9090 site.leshou.com/s/45714581.html

1889 site.leshou.com/s/46021681.html

9084 site.leshou.com/s/46021681.html

1900 site.leshou.com/s/49557679.html

9081 site.leshou.com/s/49557679.html

1873 site.leshou.com/s/50777239.html

9088 site.leshou.com/s/50777239.html

1904 site.leshou.com/s/52407278.html

9079 site.leshou.com/s/52407278.html

2992 wap.ailaba.org/sell/182965618167809.html

8475 wap.ailaba.org/sell/182965618167809.html

1610 www.01ny.cn/xinxi/25324778.html

9539 www.01ny.cn/xinxi/25324778.html

9536 www.01ny.cn/xinxi/37941846.html

9543 www.01ny.cn/xinxi/37941846.html

2282 www.05188.com/fenlei/22739365.html

9288 www.05188.com/fenlei/22739365.html

1913 www.51sole.com/b2b/pd_26691448.htm

8449 www.51sole.com/b2b/pd_26691448.htm

1230 www.587766.com/gongqiu/show_2096363.html

8480 www.587766.com/gongqiu/show_2096363.html

2275 www.afinance.cn/fenlei/25435008.html

9304 www.afinance.cn/fenlei/25435008.html

10328 www.afinance.cn/fenlei/37074884.html

10329 www.afinance.cn/fenlei/37074884.html

1917 www.afinance.cn/fenlei/38574413.html

8440 www.afinance.cn/fenlei/38574413.html

1924 www.ailaba.org/sell/179521059297846.html

8421 www.ailaba.org/sell/179521059297846.html

7276 www.ailaba.org/sell/183760178499696.html

9114 www.ailaba.org/sell/183760178499696.html

1222 www.bzcm.net/xinxi/29968353.html

8481 www.bzcm.net/xinxi/29968353.html

2954 www.cnhan.com/fenlei/34281043.html

8476 www.cnhan.com/fenlei/34281043.html

1276 www.cntrades.com/b2b/hnszbjh/sell/itemid-85394203.html

8428 www.cntrades.com/b2b/hnszbjh/sell/itemid-85394203.html

2262 www.gtobal.com/?m=trade&c=detail&a=preview&id=4930157402&type=2

9414 www.gtobal.com/?m=trade&c=detail&a=preview&id=4930157402&type=2

9450 www.gtobal.com/?m=trade&c=detail&a=preview&id=5060488002&type=2

9452 www.gtobal.com/?m=trade&c=detail&a=preview&id=5060488002&type=2

2271 www.hizh.cn/xinxi/10229031.html

9311 www.hizh.cn/xinxi/10229031.html

2043 www.hizh.cn/xinxi/3524483.html

9360 www.hizh.cn/xinxi/3524483.html

9566 www.kuyiso.com/html/101/ershoushebei/18348452x.htm

9583 www.kuyiso.com/html/101/ershoushebei/18348452x.htm

9515 www.kuyiso.com/html/21/zhiyepx/29045579x.htm

9522 www.kuyiso.com/html/21/zhiyepx/29045579x.htm

1293 www.laiwunews.cn/xinxi/13358851.html

8416 www.laiwunews.cn/xinxi/13358851.html

2213 www.laiwunews.cn/xinxi/18715417.html

8670 www.laiwunews.cn/xinxi/18715417.html

3181 www.laiwunews.cn/xinxi/18774478.html

8299 www.laiwunews.cn/xinxi/18774478.html

2259 www.laiwunews.cn/xinxi/18917168.html

9417 www.laiwunews.cn/xinxi/18917168.html

2280 www.laiwunews.cn/xinxi/19054895.html

9293 www.laiwunews.cn/xinxi/19054895.html

1282 www.laiwunews.cn/xinxi/20974600.html

8427 www.laiwunews.cn/xinxi/20974600.html

2217 www.laiwunews.cn/xinxi/20975830.html

8664 www.laiwunews.cn/xinxi/20975830.html

2256 www.laiwunews.cn/xinxi/21115975.html

9422 www.laiwunews.cn/xinxi/21115975.html

2211 www.laiwunews.cn/xinxi/21361687.html

8672 www.laiwunews.cn/xinxi/21361687.html

2214 www.laiwunews.cn/xinxi/21416448.html

8669 www.laiwunews.cn/xinxi/21416448.html

2272 www.laiwunews.cn/xinxi/21512940.html

9403 www.laiwunews.cn/xinxi/21512940.html

2267 www.laiwunews.cn/xinxi/25450210.html

9407 www.laiwunews.cn/xinxi/25450210.html

2290 www.laiwunews.cn/xinxi/27248072.html

9387 www.laiwunews.cn/xinxi/27248072.html

2288 www.laiwunews.cn/xinxi/27340981.html

9390 www.laiwunews.cn/xinxi/27340981.html

3825 www.qhnews.com/bianmin/30874042.html

8303 www.qhnews.com/bianmin/30874042.html

9559 www.qhnews.com/bianmin/32388347.html

9572 www.qhnews.com/bianmin/32388347.html

2541 www.qhnews.com/bianmin/32550280.html

8442 www.qhnews.com/bianmin/32550280.html

2564 www.qixiangwang.cn/fenlei/20773619.html

8288 www.qixiangwang.cn/fenlei/20773619.html

2946 www.qiyegu.com/sell/201412/13/deatil_10657611.html

8478 www.qiyegu.com/sell/201412/13/deatil_10657611.html

1992 www.sooshong.com/chxchx/offerdetail-150433811437054.html

8614 www.sooshong.com/chxchx/offerdetail-150433811437054.html

1299 www.waaku.com/infoview11788636.html

8414 www.waaku.com/infoview11788636.html

2297 www.yuqinge.com/xinxi/4683460.html

9257 www.yuqinge.com/xinxi/4683460.html

2215 www.zznews.cn/xinxi/21212448.html

8666 www.zznews.cn/xinxi/21212448.html

9425 yt.qu114.com/zhuanli/37012081.html

9426 yt.qu114.com/zhuanli/37012081.html

2204 b2b.88152.com/show-2677485.html

8647 b2b.88152.com/show-2677485.html

2293 b2b.88152.com/show-2989753.html

8426 b2b.88152.com/show-2989753.html

2318 b2b.88152.com/show-2994375.html

8654 b2b.88152.com/show-2994375.html

2220 b2b.88152.com/show-3071983.html

8662 b2b.88152.com/show-3071983.html

1264 haikou.kuyiso.com/huizhan/29251188x.htm

8477 haikou.kuyiso.com/huizhan/29251188x.htm

1935 info.123cha.com/show_1007121.html

8603 info.123cha.com/show_1007121.html

1887 info.b2b168.com/s168-48159518.html

9577 info.b2b168.com/s168-48159518.html

3000 info.b2b168.com/s168-58451934.html

8474 info.b2b168.com/s168-58451934.html

9428 info.b2b168.com/s168-60677934.html

9431 info.b2b168.com/s168-60677934.html

2210 lanzhou.kuyiso.com/canyin/1328.htm

8674 lanzhou.kuyiso.com/canyin/1328.htm

1909 site.leshou.com/s/39010445.html

9076 site.leshou.com/s/39010445.html

1867 site.leshou.com/s/40131383.html

9089 site.leshou.com/s/40131383.html

1885 site.leshou.com/s/45062793.html

9086 site.leshou.com/s/45062793.html

1859 site.leshou.com/s/45714581.html

9090 site.leshou.com/s/45714581.html

1889 site.leshou.com/s/46021681.html

9084 site.leshou.com/s/46021681.html

1900 site.leshou.com/s/49557679.html

9081 site.leshou.com/s/49557679.html

1873 site.leshou.com/s/50777239.html

9088 site.leshou.com/s/50777239.html

1904 site.leshou.com/s/52407278.html

9079 site.leshou.com/s/52407278.html

2992 wap.ailaba.org/sell/182965618167809.html

8475 wap.ailaba.org/sell/182965618167809.html

1610 www.01ny.cn/xinxi/25324778.html

9539 www.01ny.cn/xinxi/25324778.html

9536 www.01ny.cn/xinxi/37941846.html

9543 www.01ny.cn/xinxi/37941846.html

2282 www.05188.com/fenlei/22739365.html

9288 www.05188.com/fenlei/22739365.html

1913 www.51sole.com/b2b/pd_26691448.htm

8449 www.51sole.com/b2b/pd_26691448.htm

1230 www.587766.com/gongqiu/show_2096363.html

8480 www.587766.com/gongqiu/show_2096363.html

2275 www.afinance.cn/fenlei/25435008.html

9304 www.afinance.cn/fenlei/25435008.html

10328 www.afinance.cn/fenlei/37074884.html

10329 www.afinance.cn/fenlei/37074884.html

1917 www.afinance.cn/fenlei/38574413.html

8440 www.afinance.cn/fenlei/38574413.html

1924 www.ailaba.org/sell/179521059297846.html

8421 www.ailaba.org/sell/179521059297846.html

7276 www.ailaba.org/sell/183760178499696.html

9114 www.ailaba.org/sell/183760178499696.html

1222 www.bzcm.net/xinxi/29968353.html

8481 www.bzcm.net/xinxi/29968353.html

2954 www.cnhan.com/fenlei/34281043.html

8476 www.cnhan.com/fenlei/34281043.html

1276 www.cntrades.com/b2b/hnszbjh/sell/itemid-85394203.html

8428 www.cntrades.com/b2b/hnszbjh/sell/itemid-85394203.html

2262 www.gtobal.com/?m=trade&c=detail&a=preview&id=4930157402&type=2

9414 www.gtobal.com/?m=trade&c=detail&a=preview&id=4930157402&type=2

9450 www.gtobal.com/?m=trade&c=detail&a=preview&id=5060488002&type=2

9452 www.gtobal.com/?m=trade&c=detail&a=preview&id=5060488002&type=2

2271 www.hizh.cn/xinxi/10229031.html

9311 www.hizh.cn/xinxi/10229031.html

2043 www.hizh.cn/xinxi/3524483.html

9360 www.hizh.cn/xinxi/3524483.html

9566 www.kuyiso.com/html/101/ershoushebei/18348452x.htm

9583 www.kuyiso.com/html/101/ershoushebei/18348452x.htm

9515 www.kuyiso.com/html/21/zhiyepx/29045579x.htm

9522 www.kuyiso.com/html/21/zhiyepx/29045579x.htm

1293 www.laiwunews.cn/xinxi/13358851.html

8416 www.laiwunews.cn/xinxi/13358851.html

2213 www.laiwunews.cn/xinxi/18715417.html

8670 www.laiwunews.cn/xinxi/18715417.html

3181 www.laiwunews.cn/xinxi/18774478.html

8299 www.laiwunews.cn/xinxi/18774478.html

2259 www.laiwunews.cn/xinxi/18917168.html

9417 www.laiwunews.cn/xinxi/18917168.html

2280 www.laiwunews.cn/xinxi/19054895.html

9293 www.laiwunews.cn/xinxi/19054895.html

1282 www.laiwunews.cn/xinxi/20974600.html

8427 www.laiwunews.cn/xinxi/20974600.html

2217 www.laiwunews.cn/xinxi/20975830.html

8664 www.laiwunews.cn/xinxi/20975830.html

2256 www.laiwunews.cn/xinxi/21115975.html

9422 www.laiwunews.cn/xinxi/21115975.html

2211 www.laiwunews.cn/xinxi/21361687.html

8672 www.laiwunews.cn/xinxi/21361687.html

2214 www.laiwunews.cn/xinxi/21416448.html

8669 www.laiwunews.cn/xinxi/21416448.html

2272 www.laiwunews.cn/xinxi/21512940.html

9403 www.laiwunews.cn/xinxi/21512940.html

2267 www.laiwunews.cn/xinxi/25450210.html

9407 www.laiwunews.cn/xinxi/25450210.html

2290 www.laiwunews.cn/xinxi/27248072.html

9387 www.laiwunews.cn/xinxi/27248072.html

2288 www.laiwunews.cn/xinxi/27340981.html

9390 www.laiwunews.cn/xinxi/27340981.html

3825 www.qhnews.com/bianmin/30874042.html

8303 www.qhnews.com/bianmin/30874042.html

9559 www.qhnews.com/bianmin/32388347.html

9572 www.qhnews.com/bianmin/32388347.html

2541 www.qhnews.com/bianmin/32550280.html

8442 www.qhnews.com/bianmin/32550280.html

2564 www.qixiangwang.cn/fenlei/20773619.html

8288 www.qixiangwang.cn/fenlei/20773619.html

2946 www.qiyegu.com/sell/201412/13/deatil_10657611.html

8478 www.qiyegu.com/sell/201412/13/deatil_10657611.html

1992 www.sooshong.com/chxchx/offerdetail-150433811437054.html

8614 www.sooshong.com/chxchx/offerdetail-150433811437054.html

1299 www.waaku.com/infoview11788636.html

8414 www.waaku.com/infoview11788636.html

2297 www.yuqinge.com/xinxi/4683460.html

9257 www.yuqinge.com/xinxi/4683460.html

2215 www.zznews.cn/xinxi/21212448.html

8666 www.zznews.cn/xinxi/21212448.html

9425 yt.qu114.com/zhuanli/37012081.html

9426 yt.qu114.com/zhuanli/37012081.html

SELECT MIN(id),url FROM test_error_tmp WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

) GROUP BY url

;

MIN(id) url

2204 b2b.88152.com/show-2677485.html

2293 b2b.88152.com/show-2989753.html

2318 b2b.88152.com/show-2994375.html

2220 b2b.88152.com/show-3071983.html

1264 haikou.kuyiso.com/huizhan/29251188x.htm

1935 info.123cha.com/show_1007121.html

1887 info.b2b168.com/s168-48159518.html

3000 info.b2b168.com/s168-58451934.html

9428 info.b2b168.com/s168-60677934.html

2210 lanzhou.kuyiso.com/canyin/1328.htm

1909 site.leshou.com/s/39010445.html

1867 site.leshou.com/s/40131383.html

1885 site.leshou.com/s/45062793.html

1859 site.leshou.com/s/45714581.html

1889 site.leshou.com/s/46021681.html

1900 site.leshou.com/s/49557679.html

1873 site.leshou.com/s/50777239.html

1904 site.leshou.com/s/52407278.html

2992 wap.ailaba.org/sell/182965618167809.html

1610 www.01ny.cn/xinxi/25324778.html

9536 www.01ny.cn/xinxi/37941846.html

2282 www.05188.com/fenlei/22739365.html

1913 www.51sole.com/b2b/pd_26691448.htm

1230 www.587766.com/gongqiu/show_2096363.html

2275 www.afinance.cn/fenlei/25435008.html

10328 www.afinance.cn/fenlei/37074884.html

1917 www.afinance.cn/fenlei/38574413.html

1924 www.ailaba.org/sell/179521059297846.html

7276 www.ailaba.org/sell/183760178499696.html

1222 www.bzcm.net/xinxi/29968353.html

2954 www.cnhan.com/fenlei/34281043.html

1276 www.cntrades.com/b2b/hnszbjh/sell/itemid-85394203.html

2262 www.gtobal.com/?m=trade&c=detail&a=preview&id=4930157402&type=2

9450 www.gtobal.com/?m=trade&c=detail&a=preview&id=5060488002&type=2

2271 www.hizh.cn/xinxi/10229031.html

2043 www.hizh.cn/xinxi/3524483.html

9566 www.kuyiso.com/html/101/ershoushebei/18348452x.htm

9515 www.kuyiso.com/html/21/zhiyepx/29045579x.htm

1293 www.laiwunews.cn/xinxi/13358851.html

2213 www.laiwunews.cn/xinxi/18715417.html

3181 www.laiwunews.cn/xinxi/18774478.html

2259 www.laiwunews.cn/xinxi/18917168.html

2280 www.laiwunews.cn/xinxi/19054895.html

1282 www.laiwunews.cn/xinxi/20974600.html

2217 www.laiwunews.cn/xinxi/20975830.html

2256 www.laiwunews.cn/xinxi/21115975.html

2211 www.laiwunews.cn/xinxi/21361687.html

2214 www.laiwunews.cn/xinxi/21416448.html

2272 www.laiwunews.cn/xinxi/21512940.html

2267 www.laiwunews.cn/xinxi/25450210.html

2290 www.laiwunews.cn/xinxi/27248072.html

2288 www.laiwunews.cn/xinxi/27340981.html

3825 www.qhnews.com/bianmin/30874042.html

9559 www.qhnews.com/bianmin/32388347.html

2541 www.qhnews.com/bianmin/32550280.html

2564 www.qixiangwang.cn/fenlei/20773619.html

2946 www.qiyegu.com/sell/201412/13/deatil_10657611.html

1992 www.sooshong.com/chxchx/offerdetail-150433811437054.html

1299 www.waaku.com/infoview11788636.html

2297 www.yuqinge.com/xinxi/4683460.html

2215 www.zznews.cn/xinxi/21212448.html

9425 yt.qu114.com/zhuanli/37012081.html

SELECT id FROM test_error_tmp

WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

)

AND id NOT IN (

SELECT MIN(id) FROM test_error_tmp WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

) GROUP BY url

)

;

id

8647

8426

8654

8662

8477

8603

9577

8474

9431

8674

9076

9089

9086

9090

9084

9081

9088

9079

8475

9539

9543

9288

8449

8480

9304

10329

8440

8421

9114

8481

8476

8428

9414

9452

9311

9360

9583

9522

8416

8670

8299

9417

9293

8427

8664

9422

8672

8669

9403

9407

9387

9390

8303

9572

8442

8288

8478

8614

8414

9257

8666

9426

[SQL]DELETE FROM test_error_tmp WHERE id IN (

SELECT id FROM test_error_tmp

WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

)

AND id NOT IN (

SELECT MIN(id) FROM test_error_tmp WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

) GROUP BY url

)

);

[Err] 1093 - You can't specify target table 'test_error_tmp' for update in FROM clause

SELECT * FROM

(

SELECT id FROM test_error_tmp

WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

)

AND id NOT IN (

SELECT MIN(id) FROM test_error_tmp WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

) GROUP BY url

)

) AS tab

id

8647

8426

8654

8662

8477

8603

9577

8474

9431

8674

9076

9089

9086

9090

9084

9081

9088

9079

8475

9539

9543

9288

8449

8480

9304

10329

8440

8421

9114

8481

8476

8428

9414

9452

9311

9360

9583

9522

8416

8670

8299

9417

9293

8427

8664

9422

8672

8669

9403

9407

9387

9390

8303

9572

8442

8288

8478

8614

8414

9257

8666

9426

[SQL]DELETE FROM test_error_tmp WHERE id IN

(

SELECT * FROM

(

SELECT id FROM test_error_tmp

WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

)

AND id NOT IN (

SELECT MIN(id) FROM test_error_tmp WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error_tmp GROUP BY url

) AS tab

WHERE c>1

) GROUP BY url

)

) AS tab

);

受影响的行: 62

时间: 0.403s

校验

SELECT COUNT(1) FROM test_error_tmp

UNION ALL

SELECT COUNT(DISTINCT url) FROM test_error_tmp;

COUNT(1)

40946

40946

1:1

完成。

该表在读写中，进行的删除。

SELECT COUNT(1) FROM test_error

UNION ALL

SELECT COUNT(DISTINCT url) FROM test_error;

DELETE FROM test_error WHERE id IN

(

SELECT * FROM

(

SELECT id FROM test_error

WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error GROUP BY url

) AS tab

WHERE c>1

)

AND id NOT IN (

SELECT MIN(id) FROM test_error WHERE url IN

(

SELECT url FROM

(

SELECT COUNT(1) AS c,url FROM test_error GROUP BY url

) AS tab

WHERE c>1

) GROUP BY url

)

) AS tab

) AND payoff_status=0;