Table of Contents
回复内容:
Home Backend Development PHP Tutorial mysql如何去除两个字段数据相同的记录?

mysql如何去除两个字段数据相同的记录?

Jun 17, 2016 am 08:32 AM
admin com email name

比如说有个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> 
Copy after login
distinct 如果要保留id的最小值,例如:
数据: mysql如何去除两个字段数据相同的记录?
执行sql:select count(*) as count ,name,id from ceshi group by name
mysql如何去除两个字段数据相同的记录?最后要删除的sql为:delete from ceshi where id not in (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为唯一索引,然后重新插入旧表数据
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hongmeng native application random poetry Hongmeng native application random poetry Feb 19, 2024 pm 01:36 PM

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

How to use email, smtplib, poplib, imaplib modules to send and receive emails in Python How to use email, smtplib, poplib, imaplib modules to send and receive emails in Python May 16, 2023 pm 11:44 PM

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

What is the difference between comcn and com What is the difference between comcn and com May 12, 2023 pm 04:08 PM

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提交表单通过后,弹出的对话框怎样在当前页弹出,该如何解决 Jun 13, 2016 am 10:23 AM

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

What is the role and usage of springboot admin monitoring What is the role and usage of springboot admin monitoring May 25, 2023 pm 06:52 PM

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. How to solve this problem? You need the permissions provided by admin to make changes to this file. How to solve this problem? Jul 26, 2023 am 10:56 AM

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 background management interface How to use Flask-Admin to implement the background management interface Aug 03, 2023 pm 11:30 PM

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

Tutorial on how to insert attachments into win10 mailbox Tutorial on how to insert attachments into win10 mailbox Jan 07, 2024 pm 12:14 PM

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

See all articles