首页 技术 正文
技术 2022年11月19日
0 收藏 446 点赞 2,243 浏览 8891 个字

记得以前有个同事问过我这个,说是以前面试的时候碰到的问题,下面我介绍三种方法。

首先我们在这里创建一个测试表添加相应的测试数据。

create table test  (id number,name varchar(10));
  insert into  test values(1,’liufang’);
  insert into  test values(2,’xiaozhang’);
  insert into  test values(3,’dawei’);
  insert into  test values(4,’laotan’);
  insert into  test values(5,’laotan’);
  insert into  test values(6,’laotan’);
  insert into  test values(7,’dawei’);

1、根绝name相同 ID不同来的方式来判断(id必须唯一)

delete from test a where exists (select null from test b where b.name=a.name and b.id>a.id);

2、用rowid 来代替其中的id,比上面的方法更适用,没有字段唯一限制

delete from test a where exists (select null from test b where b.name=a.name and b.rowid>a.rowid);

3、  通过分析函数根据name 分组生成序号,然后删除序号大于1 的数据

(注:rder by 2,3根据查询的第二第三个字段升序排序查询)

select  rowid as rid,name,row_number() over(partition by name order by id) from test order by 2,3;

<img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAdkAAACpCAIAAADocNAgAAAWQ0lEQVR4nO2dvXLrPJKGeRl7Kb60DVWbbLqXcPKZ2WTiqc+hoqnaZDMnO+uqCVlOT6AJaMMw+ocNdIMkhPetLhXUIhsNCHzUpih6eTwe//Gf//V4PNZMf/7znx9f+tNf/vqAIAiCemp5gMUQBEFnCyyGIAg6X2AxBEHQ+QKLIQiCzpeJxTAYDAbravss/gVBEAR11j6LVwiCIKizUBdDEASdL9TFEARB52ufxRAEQVBvoS6GIAg6X2AxBEHQ+QKLIQiCzhdYDEEQdL7AYgiCoPMFFkMQBJ0vsBiCIOh8gcUQBEHnazAW//r1K6X07//9/4F27riOVz6TkFMzT+YBY+/dxUXevrFZ/D//9xFiYDHk0cyTCRZHCSwGiyGvZp5MsDhKYDFYDJl0v98/PnUvXpp5MsHiKAWweKnR1y5/tKULFkfpIutvFOUgBotzgcVRimHx46cU57quy/LHZg3pgsVVuhNqJF1k/Q2hAsT3+1uxwcyT6R/7stxy69GFLkv8ZVleX19XQ+lp77fYOIzFued2u0nOr12OqIuLOZqNxfeP+/3tfn/jcby7/u42WZMJjZZED4CGQ2I383/+8585iN/evCyOzfBchbA40aaBxbV/lNfG38CVItAqM+n19dX+tjLr9llZvCzL608pOH4+FicQ0yJu0+4h9Pb29gnID87u9w1LxnxioyVty6xY04XHo8+EP3H8CWIPi7cj8PH4PKpDkjxXThYvy+Ksi5dl+dvf/m1Zlv/9+99ZSwxtG0LaPdXF24fGgxAmR9zuqPNVui2J52Tx7Xa73W4bfzdtM3W73WZg8TeIHSxeFYA2oTM22qaudfGW6sd3kp8gpvW78Y/cr5Lq+/EJcOxhsXH4fhZ76uJixxwmVJYRbcvgJw+fl8XbfOWFMPU0sLj5T+kjZQHxaj6EGIA60Bkbjf4FSj3N+gHiz8m8syBebX9E5wj24LjT2Z6k2veiisVsCXxAXbxt0DYEymJdhklYUqWYPGEsXteV/Xxo+NDQdQEWf1yZxUYQrzWH0A+AOtAZHm35+kMvZ3HhUfTx8SG+dL9/bPllp3ruAohXdTIXUgtnCbZ8bGwT+HX+mnn0TOkWvGr3WhYnsCzL7Wv03vPFXVkcruWrsu7CYrv+yKxhGGCxIjuI1/G/uzOuNyVCzuIf7a0g/vj4xPEeiFcDLGgt/MgUiGM/iGuDVJ4rvw1XF//Mv2Wl0TjFGdQl9hxFLsVZO9RiGwuLd4/PWhb/xMVHGzuEaI0koherVYF4Hfy7u6/l+11hppVNy04pyHaRWmp8tjMQf1fHKohXQ128LGVdXLD4UXmygsVxCIhrQ9lB1pbYbhdLfxZ/rqTbbTvTxNjXtRaW4WTLdfsK8BbM4tzTfL549yjaZfFCvtykojg2sJhHyO7k9Ij2id0Mx7UgXgf/7i6QxbnWdf1E8McniD/uH2/3HRCvtro44bjIs0jYPgnN9NwNVRVQGru9BA6pizfaskqv1g4hxdcoTIgsxckDfl0nd2EWr3vfg1tYXJyh3lQ4h66Lv7H7cV+bQLwO/t2dhF0nizMEf4P4zfC+GOvilM8iXKNaxeK19ayCFKTtjIfC4mxc6Ui8JXDFni/eNbC4dH7tol1HYZw1S12cDkhPXZyUWGzZuJ/u97ccx9/tDixeL/ndHUVwYhzrZ8Wi+O3trRbEa11dzJfDyVM7FQ3fttHdWRAbcXz9unhxX0fxKcM5Css7uAzEYkVVdXEib7oSu60uTroIi9cCx/UgXiu/O/4GqA/EUdEKllW9msTXxR8f2yXEmxnzOasu3uR5R5S/0ox/sV3kfDHL4qhr2pbbLactr69tdofTl8VrzTVtXe9HsfwsgbdOE5cj6uKKkwldVeK4BsRr/XU8zvorNlpXFqdzx3adWBefLv91FM4ujmBxTlvJ9tZbHvAni5dIFtv1tUvH33osX6VxwWKpKLaz+Gr6xnEliNemayqdIPYUX4XYRWV8NSkBt+BvLYhX2xfx/eric+W5vjikiyNYHF0X56dPvz+V/Sxm1Xb58K7s1xcv3P0oWBCPy+J1w3E9iNczbi32tqcjk1nl64sbZPwNdF4XS/KkcYr8v7vzd7FDyb25rbimba8utgxnWZbb7fsz6fvDKYTFtM6lLG6uhXPhPm1Rmvk2j+Gq+rHAAfkcqXnuXxz1ISqGiDhH8Udh6xeL2Zc8wv2LowQWB2rmyZyHxb31zHUxWKzoIuvvOTTzZILFUep1vriTwOIoXWT9PYdmnkywOEpgMVgMeTXzZILFURqbxYF27riO10XW33No5skEi6M0GIshCIKeUnUs/tNf/gqDwWBXsN8vL9cx/3CqWfyAIAi6gH6/vJydwqdCMunIYva3Jc2Kjfb0WtfVvvGl5nbczKGD1UbAHgvsTBZbTn9sw7BseXy0GWScrl+/fl1tbsfN3KPl+24t3X+b9xw///v98tK2Y+0CA4s7RptB4xJt3MybVcCxKyufA8QrWHwFFtOfgdPfhrf9VPyZ1EY0OoHJn29AJzlwwj2ZFwmwo8gfqaTRsb0ow6dOZYoWlcVsZLaXqr6kbRZuAo3jsnuUcUmDKrbZWEz3YucwF1jsUhGNnXFpSSnvyhOrjWirYW6L1a/s1aaozGn+9JGVMqhi4PoMWBpSJvqWUX01bCPNoT7bei8Nw0ksNo4iF1jsElhcKw/R2KOIrU0U/J2SuZRMLYuLbTws1qEjZdLG4tq+pG0acq71SF3oM5+PNGexvmILgcUusbyQFlbxFCxWZGExfcqSSFn9R2ausKM3iynFdj1SJhYW+/ti99qNzOZc65HGxS42NkOFxfpbDBa7xFZAu+uM9U8iD9FWteZlibYLuCMzz58WbZYshaQZWLmxS9vQDVgPfSpFq41cdYw44zR49HEps5raxTkKZcUWAotdwjmKWsWyOEeYTqLTM6e56S9RxbKYRpZyq9qy+ERp64vdq/i4MuZ8NRYXo8gFFrsEFtcqimi0YSTR6Zk7Waw0alm8O0vKU0u7uS8LwY9nsbF35bs7fTWCxS6xVy/l7eKTUP9gnEFtRCvmMHn0V+n8n555kV7aK0+eFR1j7i8aKzcDRRw2GTYr5SmdWJpbbV/s5OzmT3fUh9+wDTu3dLD5NW10LzqiJLDYpae5qv8wtRHtCho3c49yfBzW19DCbz3A4jE0LtGOzHwh8kSDjhRY/LhBT6RtwY2ocTOHQvT75aVr/AFYbJRxGNZElyUwGpTr9fX17BQaNW7mkF8H3DPTuMCuco5C+rNuY/E/ggQW99O4RBs3c8gvsLh0SmfZwOJRxC647W11Ru79roHFMwssLp3Slx5tLF6WxcjiKk9KUmlr06T2VXz505ah5JGCe+IUW0oLDiyGrqxAFksLdVQWFzhuYPEWpAeLH1mhlzekffWnygbhLFac9jh6Vv1Y3Ftg8cyKYrFS5QzM4hzHB9TFbOkneZws1j00YEOGkodGptodu7IvWAyNKNTFpXMhOoDFEkwVD2Vxahd75bL3tUvnNk/uV0axO/aHLLrgpGmhwaVt6NMeAotnFlhcOlkQX5PF7GP+Kvs0iqohLKYjso9dUrHgpPgPdQ7ZvsBiqJ/A4tLJgvgAFhe1267Hw2JLX+XM1mfIeth8aMMydkm7LM4Dsk+lzPV+/QKLZxZYXDrTEbj+1AF1cVVDeiw2Zuaovq/ABn3aPHZppLV1sTGs5AwUWDyzwOLSyYK4jcUJ6zqLlQJQ9zwyiOQNHcTGvope2jIsPHkaNKuqOMpIlfPFUuSFsJh2ofQYJbB4ZsVeR2E8NPplEsNiCuLmuljBtH+0wynnWr9exiXauJlDfuG3HqVTEljs1wGl5WNkoo2bOeQXWHzOfdqWZXmFIAj60u+XlwN6uTSLjQq/T9v74x3Ww15fX0/PYbbMYX77/fLSu4sBWHzKOYrT3/tntXGJNm7mML+BxWDxsxlLtO08tTNy73cNLJ7ZwOKOLE5fVVlYXOVJkZW2Ynpfy0+1ZSh5pOBtkdkgEtHAYtiVLYrFysE1KYtzBFMc+7lW8Je+qoSyEJPt0Zmz7vRnmBacMebVDCye2UJYrB+wYLGVxWwJKXmcLNY9NGBDhpKHXSVVs6HvDhbDRjSwuBeLFRBTFkswVTyUxalNy+fcb+xrl85tnmKVSKPYHbuyHCnRpGmhwaVt6NMeBhbPbLHni9m1OjWLWRBHsZh9ZN+JHlQNYTEdkX3s78KpsYJoUnx9Dlnug8WwfhbIYmmhzstiCcQsiwus7Ho8LLb0Rd/d2gxZD5sPbVjGLqW6y+I8IPtUypz2FWtg8cwW+N2d9NKkLF5kELMsrm1UsdjZV2CDPo2N/F5fF9snECyG9bPw88XU5mVxLoXFeiGpeN4ziOQNHcTGvope2jIsPNKKYTvdjSONVDlfLEVeCIvpZBZPexhYPLNFsZiu9mSTsniX1Ke/98dbzrV+vYxLtHEzh/kNv/UAi4+zA0rL95GJNm7mML+BxbhPGwRB5wv3abMq/D5tgdGgXMYFd0GNmznkF+5fXDoPO0fhHy3EalyijZs55BdYXDrB4tGVL7h0hvrEfOwCi2cWWFw6A1mcQGBhcZUnR4zU1qZJ7au4LKYtQ8kjBW/OkO5LFxxYDF1f+D/QpTOKxcvevYHK1Cs9BX/pq0ooCzHZHp05605/hpuiWHw8wcHimRX7f6DZlyZlscRlhcVsCSl5nCzWPTRgQ4aSh0amMmbIhgKLoRGFurh0xrJ4Iwjr/5G3AFPFQ1mc2rR8zv3Gvnbp3ObJ/coojJHZ4BYWW1gvTaaygVNg8cwCi0vnKXVxG4vZx/xV9mkUVUNYTEdUFZkNvstiqUepi6q9PAKLZxZYXDrPYnFRZO16PCy29FXObH2GrIfNhzbsGdJU7XWxHq1tL4/A4pkFFpfOKBYvNd/dKYe31KhisbOvwAZ9Ghv5gboYGlNgcekMrItTGcW+9J10ZWmZP02PeUMHsbGvope2DAtPngbNqjayNFL2+mKagJ4hO5m094XMj0dg8cyKvY5i99Doncm1WKxj2j/a4bREF5KsxiXauJlDfuG3HqUTLO4n5RM7UOMSbdzMIb/AYtynDYKg84X7tFkVfp+20++X+qz2OuxdgMfNHOY33L+4dB52juL09/5ZbVyijZs5zG9gMVj8bJYTLZ2hPj2r2sxhsxlY3J3Fi3BNWzFNVZ4cMVJbMb2v4pqttgwljxQ8JMO04HajXdPA4pktisXKwTU1i7dJCWdxmvGiIe2rP1U2CGex4vRnmBZcbXfGHHobWDyzRf0faLa9GVhsZTFbQkoeJ4t1Dw3YkKHkYVdJ7WywGaYFV9vd7rI+xsDimQ0s7sjijcIWFkswVTyUxalNy+fcb+xrl31tnmKVSKNoyDBfcMoCLfqVPMWk0W3YDZwGFs9sseeLpUNjRhYnBPdjMfvIvhM9qBrCYjoiZ+R3A4ulHo1zqO/lMbB4ZgtksbQg52Vxrl0WF0XWrsfDYktf9N2tzZD1sPnQRkOG+YLbXZpSNGUOjXt5DCye2QK/u5NempTFtEDerYurGlUsdvYV2KBPYyO/oy6GjWnh54upTc3iVEkpLK4tLfOn6TFv6CA29lX00pZh4ZFWDNtpVYbFgpMis342Q3Yypd7ZNBoMLJ7ZolisHxrzslhh9Onv/fG2RBeSrI1LtHEzh/kNv/UAi48z6eM6fMGdPtLZMof5DSzGfdogCDpfuE+bVeH3aQuMBuUyLrgLatzMIb9w/+LSedg5Cv9oIVbjEm3czCG/wOLSCRaPrlfu/92dmI9dYPHMAotLZxSLi4tLdllc5ckRI7W1aVL7KjJvy1DySMFDMtxEFxxYDF1f+N+jpTOQxVV1ca2n4C99VQllISbbozNn3enPcFMUi48nOFg8s2IIqB6wYLGVxWwJKXmcLNY9NGBDhpKHRqZqy3ATWAyNqPBzFGDxN22TdlkswVTxUBanNi2fc7+xr132tXlyvzKKhgyTLCy2sF6aTGUDp8DimRXIYvuh0S+Ta7FYqZFDWMw+5q+yT6OoGsJiOiJn5IeBxVKPUmJVe3kEFs8s1MWlM4rFEpclFhdF1q7Hw2JLX+XM1mfIeth8aKMhwyR7Xcx27dzLI7B4ZoHFpfPEuriqUcViZ1+BDfo0JGAu1MXQiMJ3d6UzsC5OZRT70nfSlaVl/jQ95g0dxMa+il7aMiw8eRo0q9rI0jDZ64tpAnqG7GTSPBcyPx6BxTML17SVzkAW65j2j3Y4LdGFJKtxiTZu5pBf+K1H6QSL+0mvZ6M0LtHGzRzyCyzGfdogCDpfuE+bVeH3aTv9fqnPaq/D3gV43MxhfsP9i0vnYecoTn/vn9XGJdq4mcP8BhaDxc9mr9z/uyu2Mc4/u+8xmcNmM7C4L4u3g9nC4ipPjhiprZjeV3HNVluGkkcK7o+cL7jmfaXZdh4DRgOLZ7ZAFiuHxqQsThSmOPZzreAvfVUJZSEm26Mz59214o/8bmOxZ0H3M7B4ZotisVLlTMpithzWWcyWkJLHyWLdQwM2ZKisDAuL2yK/g8WwMQ11cV8Wb7zYZbEEU8VDWZzatHzO/ca+dunc5inWijSK5sjvBhazUyTNJLultIHitxhYPLOBxR1ZvJjPUbSxmH1k34weVA1hMR2RM/J70/limoNlG0ujysDimQ0s7shiti2xuCimdj0eFlv6ou9ubYash82HNpojv3djcd6pvhfd0Whg8cwGFl+FxbWNKhY7+wps0Kexkd+vURe3GVg8s4HFvVj8j737tOUT11YSpknPn+p1mb2vope2DAuPtFbYTtsivwvXF7NBpBzYjmrnp+EQAotnttjrKNgVOC+LdUyf/t4fbzmw+vVyItGUz5uLZw473fBbD7D4OPPUjFULbtAxgsUzG1iM+7RBEHS+cJ82q8Lv0xYYDcplXHAX1LiZQ37h/sWl87BzFP7RQqzGJdq4mUN+gcWlEyweXZYF55//7aSwM0ghsHhmgcWlM5DF+ZVPuyyu8qSwSlubJrWv4pqttgwljxS8OUO67zEsjgqSCyyeWYEsllbmvCzOyRvL4gf538/0VSWUhZhsj86cdac/w02HEQ0shgIV+3+g2ZdmZzEFscRitoSUPE4W6x4asCFDyUMjUxkzZEOBxdCIQl1cOk9hsQRTxUNZnNq0fM79xr526dzmyf3KKBoyTMoXnDJLNKVd+hedsjmwG7DbUD9YPLPA4tIZy+JFuIsxPTgflVyjYKUbs0+jqBrCYjqiqshs8GLBsTlIT5WGPfPahpQ5NJXA4tJ5FoulmkvyeFhs6auc2foMWQ+bD23YM7QQjR2ONEAlvjQKdnp3t3kQgcUzCywunSfWxVWNKhY7+wps0KexkR8962L/u8YmLGUOTSWwuHQez+LdAlDyPDLQ5A0dxMa+il7aMiw8eRo0q9rI0kiN54uVHKSc6Zzo87O7jZI5NJtir6PYPTR6Z3I5FiuM9o92OOUw6tfLxYm2yJ9JF88c6ir81qN0gsX9pHxiB+r6RENdDFGBxbhPGwRB5wv3aYMgCDpfv19ermP+4YDFEARB5wsshiAIOl9gMQRB0PkCiyEIgs4XWAxBEHS+wGIIgqDzBRZDEASdL7AYgiDofP0L33EHcCVHzC0AAAAASUVORK5CYII=" alt="" />

分析函数会吧对应名称相同的字段出现的次数记录下来,把对应次数大于1 的数据删除即可

分析函数:ROW_NUMBER() OVER (partition by name order by name)  简单的说row_number()从1开始,为每一条分组记录返回一个数字,

这里的ROW_NUMBER() OVER (partition by name order by name) 是先把name列升序,再为降序以后的没条name记录返回一个序号,

delete from test
    where rowid in (select rowid
                      from (select rowid as rid,
                                   row_number() over(partition by name order by id) as seq
                              from test)
                     where seq > 1);

相关推荐
python开发_常用的python模块及安装方法
adodb:我们领导推荐的数据库连接组件bsddb3:BerkeleyDB的连接组件Cheetah-1.0:我比较喜欢这个版本的cheeta…
日期:2022-11-24 点赞:878 阅读:8,953
Educational Codeforces Round 11 C. Hard Process 二分
C. Hard Process题目连接:http://www.codeforces.com/contest/660/problem/CDes…
日期:2022-11-24 点赞:807 阅读:5,478
下载Ubuntn 17.04 内核源代码
zengkefu@server1:/usr/src$ uname -aLinux server1 4.10.0-19-generic #21…
日期:2022-11-24 点赞:569 阅读:6,290
可用Active Desktop Calendar V7.86 注册码序列号
可用Active Desktop Calendar V7.86 注册码序列号Name: www.greendown.cn Code: &nb…
日期:2022-11-24 点赞:733 阅读:6,107
Android调用系统相机、自定义相机、处理大图片
Android调用系统相机和自定义相机实例本博文主要是介绍了android上使用相机进行拍照并显示的两种方式,并且由于涉及到要把拍到的照片显…
日期:2022-11-24 点赞:512 阅读:7,739
Struts的使用
一、Struts2的获取  Struts的官方网站为:http://struts.apache.org/  下载完Struts2的jar包,…
日期:2022-11-24 点赞:671 阅读:4,773