使用DISTINCT如何去掉重复记录,并查询所有字段?
表(kzw):
id xianlu_id point_name x y h biaod_id
1 19 草桥 314594.654 494252.073
2 19 草桥 314594.654 494252.073
3 19 北宫门 315032.232 492997.054
4 19 北宫门 315032.232 492997.054
5 19 北宫门 315032.232 492997.054 1
6 18 北三环路口 311013.473 496936.988 1
7 18 北三环路口 311013.473 496936.988
8 18 北三环路口 311013.473 496936.988 40.77
筛选结果:
id xianlu_id point_name x y h biaod_id
1 19 草桥 314594.654 494252.073
3 19 北宫门 315032.232 492997.054
5 19 北宫门 315032.232 492997.054 1
6 18 北三环路口 311013.473 496936.988 1
7 18 北三环路口 311013.473 496936.988
8 18 北三环路口 311013.473 496936.988 40.77
数据表规律:
id(自动编号),xianlu_id(数字),point_name(文本),x(文本),y(文本),h(文本),biaod_id(文本)。其中xianlu_id,point_name,x,y,h,biaod_id六个字段唯一确定一个数,为联合主键
实现的效果:
通过xianlu_id,point_name,x,y,h,biaod_id六个字段共同作用,去除重复项,但是要保证能查询出id字段
我的方法:
方法一:利用distinct去除重复项
SELECT distinct point_name,x,y,h,xianlu_id,biaod_id from kzw WHERE xianlu_id = "&xianlu_id& " ORDER BY point_name ASC
结果:
显示记录正确,但是没有办法查询出id字段
方法二:建立子查询
select * from kzw a where not exists(select 1 from kzw where id > a.id and point_name = a.point_name and x= a.x and y = a.y and h = a.h and xianlu_id = a.xianlu_id and biaod_id = a.biaod_id) and xianlu_id = "&xianlu_id& " ORDER BY point_name ASC
结果:
能查询出id字段,但显示记录仍然有很多重复,基本没有筛选
请各位大侠帮小妹指指招,看看是哪个地方出了问题?
[解决办法]
create table kzw(id int, xianlu_id int, point_name varchar(10), x decimal(10, 3), y decimal(10, 3), h decimal(10, 2), biaod_id int)
insert kzw select 1, 19, '草桥 ', 314594.654, 494252.073,null, null
union all select 2, 19, '草桥 ', 314594.654, 494252.073,null, null
union all select 3, 19, '北宫门 ', 315032.232, 492997.054,null, null
union all select 4, 19, '北宫门 ', 315032.232, 492997.054,null, null
union all select 5, 19, '北宫门 ', 315032.232, 492997.054,null, 1
union all select 6, 18, '北三环路口 ', 311013.473, 496936.988,null, 1
union all select 7, 18, '北三环路口 ', 311013.473, 496936.988,null, null
union all select 8, 18, '北三环路口 ', 311013.473, 496936.988, 40.77, null
select id=min(id), xianlu_id, point_name, x, y, h, biaod_id
from kzw
group by xianlu_id, point_name, x, y, h, biaod_id
order by id
--result
id xianlu_id point_name x y h biaod_id
----------- ----------- ---------- ------------ ------------ ------------ -----------
1 19 草桥 314594.654 494252.073 NULL NULL
3 19 北宫门 315032.232 492997.054 NULL NULL
5 19 北宫门 315032.232 492997.054 NULL 1
6 18 北三环路口 311013.473 496936.988 NULL 1
7 18 北三环路口 311013.473 496936.988 NULL NULL
8 18 北三环路口 311013.473 496936.988 40.77 NULL
(6 row(s) affected)
[解决办法]
create table kzw(id int, xianlu_id int, point_name varchar(10), x decimal(10, 3), y decimal(10, 3), h decimal(10, 2), biaod_id int)
insert kzw select 1, 19, '草桥 ', 314594.654, 494252.073,null, null
union all select 2, 19, '草桥 ', 314594.654, 494252.073,null, null
union all select 3, 19, '北宫门 ', 315032.232, 492997.054,null, null
union all select 4, 19, '北宫门 ', 315032.232, 492997.054,null, null
union all select 5, 19, '北宫门 ', 315032.232, 492997.054,null, 1
union all select 6, 18, '北三环路口 ', 311013.473, 496936.988,null, 1
union all select 7, 18, '北三环路口 ', 311013.473, 496936.988,null, null
union all select 8, 18, '北三环路口 ', 311013.473, 496936.988, 40.77, null
delete kzw where id not in(select min(id)[id] from kzw group by xianlu_id,point_name,x,y,h,biaod_id)
select * from kzw