表(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字段,但显示记录仍然有很多重复,基本没有筛选请各位大侠帮小妹指指招,看看是哪个地方出了问题?
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字段,但显示记录仍然有很多重复,基本没有筛选请各位大侠帮小妹指指招,看看是哪个地方出了问题?
select id=min(id), xianlu_id, point_name, x, y, h, biaod_id
from T
group by xianlu_id, point_name, x, y, h, biaod_id
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, nullselect 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)
我按照你的方法改的查询:
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 WHERE xianlu_id = "&xianlu_id&" ORDER BY point_name ASC 搜出来的查询结果为空,我怀疑是字段类型的问题。
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, nulldelete 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
drop table kzwcreate 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, nulldelete kzw where id in(select min(id)[id] from kzw group by xianlu_id,point_name,x,y,h,biaod_id having count(1)>1)select * from kzw
strSQL ="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 where xianlu_id = "&xianlu_id&" ORDER BY point_name ASC"说明一下:
我是access的数据库,用asp建立数据集,把kzw表里的记录读出来,前面的create table都不能用的
你可以从运用多个sql语句出发,加上程序判断来实现你的目的.
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