检查一列以获取MySQL中的唯一值
您可以为此使用子查询。让我们首先创建一个演示表
mysql> create table uniqueBothColumnValueSameDemo -> ( -> UserId int, -> UserValue int -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into uniqueBothColumnValueSameDemo values(10,20); mysql> insert into uniqueBothColumnValueSameDemo values(10,20); mysql> insert into uniqueBothColumnValueSameDemo values(20,30); mysql> insert into uniqueBothColumnValueSameDemo values(20,40); mysql> insert into uniqueBothColumnValueSameDemo values(50,10); mysql> insert into uniqueBothColumnValueSameDemo values(50,10); mysql> insert into uniqueBothColumnValueSameDemo values(60,30); mysql> insert into uniqueBothColumnValueSameDemo values(60,30); mysql> insert into uniqueBothColumnValueSameDemo values(60,50);
使用select语句显示表中的所有记录。查询如下-
mysql> select *from uniqueBothColumnValueSameDemo;
输出如下
+--------+-----------+ | UserId | UserValue | +--------+-----------+ | 10 | 20 | | 10 | 20 | | 20 | 30 | | 20 | 40 | | 50 | 10 | | 50 | 10 | | 60 | 30 | | 60 | 30 | | 60 | 50 | +--------+-----------+ 9 rows in set (0.00 sec)
这是检查列唯一值的查询
mysql> SELECT DISTINCT UserId, UserValue -> FROM uniqueBothColumnValueSameDemo tbl1 -> WHERE (SELECT count(DISTINCT UserValue) -> FROM uniqueBothColumnValueSameDemo tbl2 -> WHERE tbl2.UserId = tbl1.UserId) = 1;
输出如下
+--------+-----------+ | UserId | UserValue | +--------+-----------+ | 10 | 20 | | 50 | 10 | +--------+-----------+ 2 rows in set (0.00 sec)