比如我有一个表 main_table
id name
1 aa
2 bb
3 cc
4 aa
5 dd
6 ee希望查询出来的表是
name
aa
bb
cc
dd
ee让重复的数据只出现一次谢谢大家,能帮忙看看吗?
id name
1 aa
2 bb
3 cc
4 aa
5 dd
6 ee希望查询出来的表是
name
aa
bb
cc
dd
ee让重复的数据只出现一次谢谢大家,能帮忙看看吗?
select name from main_table group by name order by name
或
select name from table1 group by name
id name other
1 aa 00
2 bb 00
3 cc 00
4 aa 00
5 dd 00
6 ee 00查询出来的表是
name other
aa 00
bb 00
cc 00
dd 00
ee 00只对name进行不能重复的判断呢?
create table tb(id int, name varchar(10), other varchar(10))
insert into tb
select 1, 'aa','00' union all
select 2, 'bb','00' union all
select 3, 'cc','00' union all
select 4, 'aa','00' union all
select 5, 'dd','00' union all
select 6, 'ee','00' select * from tb a where not exists (select 1 from tb where name=a.name and id<a.id)/*
id name other
-------------------------
1 aa 00
2 bb 00
3 cc 00
5 dd 00
6 ee 00
*/drop table tb
因为other的数据都是重复的,我有点糊涂了
先按 NAME分组,再按OTHER分组, 楼主可以先看一下联机丛书 GROUP BY 的用法!
id name
1 aa
2 bb
3 cc
4 aa
5 dd
6 ee 希望查询出来的表是
name
aa
bb
cc
dd
ee
select distinct name from tb order by name
如果是 main_table
id name other
1 aa 00
2 bb 00
3 cc 00
4 aa 00
5 dd 00
6 ee 00 查询出来的表是
name other
aa 00
bb 00
cc 00
dd 00
ee 00
select distinct name ,other from tb order by name如果是 main_table
id name other
1 aa 00
2 bb 00
3 cc 00
4 aa 00
5 dd 00
6 ee 00 查询出来的表是
id name other
1 aa 00
2 bb 00
3 cc 00
5 dd 00
6 ee 00
select t.* from main_table t where id = (select min(id) from main_table where name = name) order by t.name如果是 main_table
id name other
1 aa 00
2 bb 00
3 cc 00
4 aa 00
5 dd 00
6 ee 00 查询出来的表是
id name other
4 aa 00
2 bb 00
3 cc 00
5 dd 00
6 ee 00
select t.* from main_table t where id = (select max(id) from main_table where name = name) order by t.name