MySQL:选择除列之外只有唯一值的行 [英] MySQL: Select rows that have only unique values except for a column
本文介绍了MySQL:选择除列之外只有唯一值的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的情况:我有一个重复值有效的表(ID 字段除外),我只想检索唯一值.
Here is my situation: I have a table which duplicated values are valid (except by the ID field), and I'd like to retrieve only unique values.
例如,如果我有寄存器:
For instance, if I have the registers:
+----+--------------+-------------+------+------+-------+-----+
| ID | SATELLITE_ID | ATT_TYPE_ID | TIME | ROLL | PITCH | YAW |
+----+--------------+-------------+------+------+-------+-----+
| 1 | 1 | 1 | 2012 | 1.0 | 2.0 | 1.3 |
+----+--------------+-------------+------+------+-------+-----+
| 2 | 1 | 1 | 2012 | 1.0 | 2.0 | 1.3 |
+----+--------------+-------------+------+------+-------+-----+
| 3 | 1 | 1 | 2011 | 1.0 | 2.0 | 1.3 |
+----+--------------+-------------+------+------+-------+-----+
我只想检索 2 和 3(ID 1 和 2相等",而 3 的时间不同).
I'd like to retrieve just 2 and 3 (ID 1 and 2 are "equal", and 3 has different TIME).
这是表结构
mysql> describe attitude;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| SATELLITE_ID | int(11) | NO | | NULL | |
| ATT_TYPE_ID | int(11) | NO | | NULL | |
| TIME | varchar(4) | NO | | NULL | |
| ROLL | double | NO | | NULL | |
| PITCH | double | NO | | NULL | |
| YAW | double | NO | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
谢谢.
推荐答案
您可以将 max()
聚合应用于 ID
列,然后 GROUP BY
剩下的:
You can apply the max()
aggregate to the ID
column and then GROUP BY
the rest:
select max(id) id, SATELLITE_ID, ATT_TYPE_ID, TIME, Roll, Pitch, yaw
from attitude
group by SATELLITE_ID, ATT_TYPE_ID, TIME, Roll, Pitch, yaw
order by id
结果:
| ID | SATELLITE_ID | ATT_TYPE_ID | TIME | ROLL | PITCH | YAW |
---------------------------------------------------------------
| 2 | 1 | 1 | 2012 | 1 | 2 | 1 |
| 3 | 1 | 1 | 2011 | 1 | 2 | 1 |
这篇关于MySQL:选择除列之外只有唯一值的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文