Home > Backend Development > PHP Tutorial > Mysql has three fields. If the value of any field is the same, it is a duplicate. How should the statement be written?

Mysql has three fields. If the value of any field is the same, it is a duplicate. How should the statement be written?

WBOY
Release: 2016-07-06 13:54:01
Original
846 people have browsed it

There are three fields in mysql. If any of these three fields has duplicate values, it is a duplicate. How should I write it?
For example, if the
a b c
1 2 3
4 5 3
c field is repeated, then these two records are considered duplicates.

I tried this statement:

<code>SELECT DISTINCT a,b,c FROM `table`</code>
Copy after login
Copy after login

The result is wrong. What I found is that all three fields are the same and then they are excluded.
And what I want is that the value of any one of the three fields is the same and then they are excluded

It is equivalent to saying that the effect achieved is the same as setting these three fields as unique key values, and querying the values ​​of these three fields are all unique results

Reply content:

There are three fields in mysql. If any of these three fields has duplicate values, it is a duplicate. How should I write it?
For example, if the
a b c
1 2 3
4 5 3
c field is repeated, then these two records are considered duplicates.

I tried this statement:

<code>SELECT DISTINCT a,b,c FROM `table`</code>
Copy after login
Copy after login

The result is wrong. What I found is that all three fields are the same and then they are excluded.
And what I want is that the value of any one of the three fields is the same and then they are excluded

It is equivalent to saying that the effect achieved is the same as setting these three fields as unique key values. Querying the values ​​of these three fields is a unique result

But what do you want to find out...? For example, which one should I keep, 1 2 3 or 1 4 3? What is the significance of this query?

The sql statement is a bit complicated:

<code>SELECT
    t1.a,
    t1.b,
    t1.c
FROM
    demo AS t1
WHERE
    t1.a NOT IN (
        SELECT
            a
        FROM
            demo
        WHERE
            t1.id != id
    )
AND
t1.a NOT IN (
        SELECT
            b
        FROM
            demo
        WHERE
            t1.id != id
    )
AND
t1.a NOT IN (
        SELECT
            c
        FROM
            demo
        WHERE
            t1.id != id
    )
AND
t1.b NOT IN (
        SELECT
            a
        FROM
            demo
        WHERE
            t1.id != id
    )
AND
t1.b NOT IN (
        SELECT
            b
        FROM
            demo
        WHERE
            t1.id != id
    )
AND
t1.b NOT IN (
        SELECT
            c
        FROM
            demo
        WHERE
            t1.id != id
    )
AND
t1.c NOT IN (
        SELECT
            a
        FROM
            demo
        WHERE
            t1.id != id
    )
AND
t1.c NOT IN (
        SELECT
            b
        FROM
            demo
        WHERE
            t1.id != id
    )
AND
t1.c NOT IN (
        SELECT
            c
        FROM
            demo
        WHERE
            t1.id != id
    )</code>
Copy after login

<code>select distinct id 
from (
    select distinct a from table
        union
    select distinct b from table
        union 
    select distinct c from table
) as t</code>
Copy after login
Related labels:
source:php.cn
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
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template