PHP-MYSQL-删除重复的行，但保留最后添加的行 [英] PHP - MYSQL - Delete duplicate rows but keep the last added

问题描述

如何删除重复的行，但保留最后插入的行.

How to delete duplicate rows but to keep the last inserted.

Name   Surname   Value      Date
A        AA        2     2014-10-01
B        BB        5     2014-12-01
C        CC        9     2015-07-01
D        DD        9     2016-10-01
E        EE        9     2014-10-25

重复值

Name   Surname   Value      Date
C        CC        9     2015-07-01
D        DD        9     2016-10-01
E        EE        9     2014-10-25

我想要保留的价值

Name   Surname   Value      Date
D        DD        9     2016-10-01

---

答案后更新代码，目标得以实现. 感谢所有受访者. 我希望这对某人有用.

---

The code updated after the answers, The goal is achieved. Thanks to all respondents. I hope this will be useful for someone.

    <?php
        include("conf.php");
        $table = "tables123";

        $query = "SELECT Name FROM " . $table;
        $resultat = mysqli_query($conn,$query);

        if(empty($resultat)) {
            echo "<p>" . $table . " table does not exist</p>";
            $query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS $table (
                Name varchar(255) NOT NULL,
                Surname varchar(255) NOT NULL,
                Value varchar(255) NOT NULL,
                Date varchar(255) NOT NULL
            )CHARACTER SET utf8 COLLATE utf8_general_ci");
        }
        else {
            echo "<p>" . $table . "table exists</p>";
        } // else

        $sql = "INSERT INTO $table (Name, Surname, Value, Date) 
        VALUES 
            ('A', 'AA', 2, '2014-10-01'), 
            ('B', 'BB', 5, '2014-12-01'), 
            ('C', 'CC', 9, '2015-07-01'), 
            ('D', 'DD', 9, '2016-10-01'),
            ('E', 'EE', 9, '2014-10-25')
            ";


        if ($conn->query($sql) === TRUE) {
            echo "New record created successfully";
        } else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }


        /// Hakan SONMEZ solution
        $sql3 = mysqli_query($conn, "
    DELETE tbl1 FROM $table tbl1, $table tbl2 WHERE tbl1.date < tbl2.date AND tb1.value = tb2.value
        ");

        ?>

推荐答案

尝试一下.

DELETE tbl1 FROM new_table tbl1, new_table tbl2 WHERE tbl1.date < tbl2.date AND tbl1.value = tbl2.value;

尝试之前，请复制数据.

Copy your data before you try.

这篇关于PHP-MYSQL-删除重复的行，但保留最后添加的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！