mysql 字段值不能重复,mysql 查询表中 字段没重复值的数据

SQL coderoot@localhost : test 04:59:19>select * from tes;

+------+-------+----------+

| id | title | time |

+------+-------+----------+

| 1 | a | 20120304 |

| 2 | a | 20121314 |

| 3 | b | 20121314 |

| 4 | c | 20121111 |

| 5 | d | 20121221 |

| 6 | c | 20121111 |

+------+-------+----------+

6 rows in set (0.00 sec)

root@localhost : test 04:59:22>select a.* from tes a,(select title,count(*) from tes group by title having count(*)=1) b where a.title=b.title;

+------+-------+----------+

| id | title | time |

+------+-------+----------+

| 3 | b | 20121314 |

| 5 | d | 20121221 |

+------+-------+----------+

2 rows in set (0.00 sec)

root@localhost : test 04:59:47>select id,title,time from tes group by title,time;

+------+-------+----------+

| id | title | time |

+------+-------+----------+

| 1 | a | 20120304 |

| 2 | a | 20121314 |

| 3 | b | 20121314 |

| 4 | c | 20121111 |

| 5 | d | 20121221 |

+------+-------+----------+

5 rows in set (0.00 sec)

------解决方案--------------------

这类问题似乎此版很多，你不妨搜索下SQL codemysql> select * from tb;

+----+-------+----------+

| id | title | time |

+----+-------+----------+

| 1 | a | 20120304 |

| 2 | a | 20121314 |

| 3 | b | 20121314 |

| 4 | c | 20121111 |

| 5 | d | 20121221 |

| 6 | c | 20121111 |

+----+-------+----------+

6 rows in set (0.00 sec)

mysql> select * from tb a where not exists(select id from tb b where b.title=a.title and b.id<>a.id);

+----+-------+----------+

| id | title | time |

+----+-------+----------+

| 3 | b | 20121314 |

| 5 | d | 20121221 |

+----+-------+----------+

2 rows in set (0.00 sec)

mysql> select * from tb a where not exists(select id from tb b where b.title=a.title and b.id<>a.id and b.time<>a.time) group by a.title;

+----+-------+----------+

| id | title | time |

+----+-------+----------+

| 3 | b | 20121314 |

| 4 | c | 20121111 |

| 5 | d | 20121221 |

+----+-------+----------+

3 rows in set (0.00 sec)

------解决方案--------------------

1

select * from tt a where not exists(select 1 from tt where a.title=title and a.id<>id)

2

select * from tt a where exists(select 1 from tt where a.title=title and a.time=time and a.id

union

select * from tt a where not exists(select 1 from tt where ((a.title=title and a.id<>id)

or

(a.title=title and a.time=time and a.id