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字段，但显示记录仍然有很多重复，基本没有筛选
请各位大侠帮小妹指指招，看看是哪个地方出了问题？
3 个解决方案
#1
try:
select * from tt a where id=(
select top 1 id from tt
where xianlu_id=a.xianlu_id
and point_name=a.point_name
and x=a.x，
and h=a.h
and biaod_id=a.biaod_id
order by id desc)
select * from tt a where id=(
select top 1 id from tt
where xianlu_id=a.xianlu_id
and point_name=a.point_name
and x=a.x，
and h=a.h
and biaod_id=a.biaod_id
order by id desc)
#2
SELECT *
FROM tt AS a
WHERE id=(
select top 1 id from tt
where xianlu_id=a.xianlu_id
and point_name=a.point_name
and y=a.y
and x=a.x
and h=a.h
and biaod_id=a.biaod_id order by id asc);
FROM tt AS a
WHERE id=(
select top 1 id from tt
where xianlu_id=a.xianlu_id
and point_name=a.point_name
and y=a.y
and x=a.x
and h=a.h
and biaod_id=a.biaod_id order by id asc);
#3
标准答案：（折腾了几天终于ok了）
SELECT * FROM kzw WHERE id in (select max(id) from kzw group by point_name,x,y,h,xianlu_id,biaod_id) and xianlu_id = "&xianlu_id&" ORDER BY point_name ASC
SELECT * FROM kzw WHERE id in (select max(id) from kzw group by point_name,x,y,h,xianlu_id,biaod_id) and xianlu_id = "&xianlu_id&" ORDER BY point_name ASC
#1
try:
select * from tt a where id=(
select top 1 id from tt
where xianlu_id=a.xianlu_id
and point_name=a.point_name
and x=a.x，
and h=a.h
and biaod_id=a.biaod_id
order by id desc)
select * from tt a where id=(
select top 1 id from tt
where xianlu_id=a.xianlu_id
and point_name=a.point_name
and x=a.x，
and h=a.h
and biaod_id=a.biaod_id
order by id desc)
#2
SELECT *
FROM tt AS a
WHERE id=(
select top 1 id from tt
where xianlu_id=a.xianlu_id
and point_name=a.point_name
and y=a.y
and x=a.x
and h=a.h
and biaod_id=a.biaod_id order by id asc);
FROM tt AS a
WHERE id=(
select top 1 id from tt
where xianlu_id=a.xianlu_id
and point_name=a.point_name
and y=a.y
and x=a.x
and h=a.h
and biaod_id=a.biaod_id order by id asc);
#3
标准答案：（折腾了几天终于ok了）
SELECT * FROM kzw WHERE id in (select max(id) from kzw group by point_name,x,y,h,xianlu_id,biaod_id) and xianlu_id = "&xianlu_id&" ORDER BY point_name ASC
SELECT * FROM kzw WHERE id in (select max(id) from kzw group by point_name,x,y,h,xianlu_id,biaod_id) and xianlu_id = "&xianlu_id&" ORDER BY point_name ASC