需要查询不重复的记录值
例如有如下表结构和值
table
fid name sex
1
a
男
2
b
男
3
c
女
4
d
女
5
a
男
6
b
男
一:distinct
select distinct name from table
结果:
name
a
b
c
d
如果要同时打开其它记录
二:group by
1.select *, count(distinct name) from table group by name
2.select min(fid),name,sex from table group by name
结果:
fid name sex
1
a
男
2
b
男
3
c
女
4
d
女
如果要打开所有记录,不指定字段用(*)
select * from table where fid in(Select min(fid) FROM table groupby name)
结果:
fid name sex
1
a
男
2
b
男
3
c
女
4
d
女
三:
查询数据中所有某字段不重复的记录
select * from table where name in(select name from table group byname having count(name)=2)
结果:
fid name sex
1
a
男
2
b
男
5
a
男
6
b
男
以此类推:
select * from table where name in(select name from table group byname having count(name)=1)
总结:
select distinct name from table打开不重复记录的单个字段
select * from table where fid in(Select min(fid) FROM table groupby name)打开不重复记录的所有字段值
select * from table where name in(select name from table group byname having count(name)=1)打开不重复任意次数的所有记录
例如有如下表结构和值
table
fid name sex
1
2
3
4
5
6
一:distinct
select distinct name from table
结果:
name
a
b
c
d
如果要同时打开其它记录
二:group by
1.select *, count(distinct name) from table group by name
2.select min(fid),name,sex from table group by name
结果:
fid name sex
1
2
3
4
如果要打开所有记录,不指定字段用(*)
select * from table where fid in(Select min(fid) FROM table groupby name)
结果:
fid name sex
1
2
3
4
三:
查询数据中所有某字段不重复的记录
select * from table where name in(select name from table group byname having count(name)=2)
结果:
fid name sex
1
2
5
6
以此类推:
select * from table where name in(select name from table group byname having count(name)=1)
总结:
select distinct name from table打开不重复记录的单个字段
select * from table where fid in(Select min(fid) FROM table groupby name)打开不重复记录的所有字段值
select * from table where name in(select name from table group byname having count(name)=1)打开不重复任意次数的所有记录