mysql如何去除两个字段数据相同的记录?
比如说有个name列和一个eamil列,如果数据库里面有条记录的这两列的值相同(我说的是这条记录的对应的那两列的值相同,并不是同一条记录里面两列值相同)的话就自动删除其他多余的列而保留最新的那一条(也就是ID最小的那个,ID是一个自增主键)
——————————————————
也就是说表里面有两条记录的name都是admin,email都是abc@163.com,我只想保留其中一条,这该怎么做
回复内容:
其实你会用英文搜索的话。可以很方便在stack overflow上 找到相关的信息 真的学CS的就不要用百度了 用google你会发现一个不一样的世界的随便贴一个
sql - How can I remove duplicate rows?
稍微讲一下其中一个思路(里面有很多很好的答案 你可以自己去看)
就是做一个group by 保留其中id 最大的(你说自增长 id最大的应该就是最新的)就可以了
具体sql query 可以这样写
<span class="k">delete</span> <span class="k">from</span> <span class="n">test</span> <span class="k">where</span> <span class="n">id</span> <span class="k">not</span> <span class="k">in</span><span class="p">(</span> <span class="k">select</span> <span class="n">name</span><span class="p">,</span><span class="n">email</span><span class="p">,</span><span class="k">max</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">from</span> <span class="n">test</span> <span class="k">group</span> <span class="k">by</span> <span class="n">name</span><span class="p">,</span><span class="n">email</span> <span class="k">having</span> <span class="n">id</span> <span class="k">is</span> <span class="k">not</span> <span class="k">null</span><span class="p">)</span>
数据:

执行sql:select count(*) as count ,name,id from ceshi group by name

如果想保留id的最大值:
简单的办法是:delete from ceshi where id not in (select count(*) as count ,name,id from (select * from ceshi order by id desc) group by name) distinct 其实非常的简单,只需要把你这张表当成两张表来处理就行了。
DELETE p1 from TABLE p1, TABLE p2 WHERE p1.name = p2.name AND p1.email = p2.email AND p1.id 这里有个问题,题主说保留最新的那一条(也就是ID最小的那个),既然是递增,最新的不应该是最大的那条吗?
上面的的语句,p1.id '即可。
当然是用group by,count可以更精准控制重复n次的情况。不过目测楼主需求应该只要把重复的删掉,保留最新的就可以了。 DELETE FROM table WHERE id not in ( SELECT
tb.id FROM ( SELECT tmp.* FROM table tmp ) tb GROUP BY tb.field1, tb.field2,… );
table是表名,field是要去重的字段。 新建一个表,设置name,email为唯一索引,然后重新插入旧表数据

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

To learn more about open source, please visit: 51CTO Hongmeng Developer Community https://ost.51cto.com Running environment DAYU200:4.0.10.16SDK: 4.0.10.15IDE: 4.0.600 1. To create an application, click File- >newFile->CreateProgect. Select template: [OpenHarmony] EmptyAbility: Fill in the project name, shici, application package name com.nut.shici, and application storage location XXX (no Chinese, special characters, or spaces). CompileSDK10, Model: Stage. Device

The journey of an email is: MUA: MailUserAgent - Mail User Agent. (i.e. email software similar to Outlook) MTA: MailTransferAgent - Mail transfer agent, which is those email service providers, such as NetEase, Sina, etc. MDA: MailDeliveryAgent - Mail delivery agent. A server of the Email service provider sender->MUA->MTA->MTA->if

The difference between comcn and com: 1. There are differences between comcn and com in terms of meaning, but there is no difference in access speed; 2. comcn is an international domain name and is a global top-level domain name for use by commercial institutions, while cn is a Chinese company domain name , domestic commercial institutions, domestic domain names, only enterprises can register; 3. The search priority is that cn will search for .cn first. After finding the .cn server, the .cn server will then search for .com; 4. cn is provided by cnnic China Internet Center Management, com's management organization is abroad.

php提交表单通过后,弹出的对话框怎样在当前页弹出php提交表单通过后,弹出的对话框怎样在当前页弹出而不是在空白页弹出?想实现这样的效果:而不是空白页弹出:------解决方案--------------------如果你的验证用PHP在后端,那么就用Ajax;仅供参考:HTML code

Applicable scenarios: 1. The project scale is not large. 2. The number of users is not very large, and the concurrency requirements are not strong. 3. There is no dedicated operation and maintenance force. 4. Exquisite team size. For some regular projects, or units where the division of corporate responsibilities is not very clear. explain. Often a system goes from requirements to design, development, testing to final launch, operation and maintenance. Often 80% of the tasks are completed by the development team. Therefore, in addition to implementing the system's functions, developers must also provide customers with consultation and answer questions and solve production problems. Just imagine, after an application is launched, there are no monitoring measures. Just like driving a car without any dashboard, no one feels safe on the road like this. How to balance simplicity and efficiency is something worth thinking about. 1. Springb

You need the permissions provided by admin to make changes to this file. Solution: 1. After selecting the administrator account on the login interface and entering the password, you can modify the file smoothly; 2. You can right-click the file and select "As Administrator" Solution: 3. Modify file permissions, right-click the file, select "Properties", click the "Security" tab, then click the "Edit" button, select your username, and then check the "Full Control" option ; 4. Use the command prompt to solve the problem; 5. Set UA permissions.

How to use Flask-Admin to implement the backend management interface Background introduction: With the development of websites and applications, the backend management interface is becoming more and more important. During the development process, we often need a convenient and fast backend management interface to manage data, users and other important information. Flask-Admin is a powerful and easy-to-use Flask extension that can help us quickly implement the background management interface. Flask-Admin is an open source project based on Flask and SQLAlchemy

Many users need to send emails for work in their daily lives, and some even need to attach various plug-in materials for communication. So how to insert attachments? Let’s take a look at the detailed tutorial below. How to insert attachments to win10 mailbox: 1. Open the mailbox 2. Click the "New Mail" icon in the upper left corner 3. Click "Insert" in the upper right corner 4. Click "Attachment" in the upper right corner 5. Select the required "Attachment" 6. Complete
