56,677
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE aa (
id int NOT NULL AUTO_INCREMENT,
type varchar(10) DEFAULT '',
dist int DEFAULT '0',
location varchar(10) DEFAULT '',
PRIMARY KEY (id)
);
insert into aa(id,type,dist,location) values (1,'AB',7000,'右边');
insert into aa(id,type,dist,location) values (2,'A',9000,'下面');
insert into aa(id,type,dist,location) values (3,'AB',12000,'下面');
insert into aa(id,type,dist,location) values (6,'A',20000,'下面');
insert into aa(id,type,dist,location) values (7,'AB',28000,'下面');
insert into aa(id,type,dist,location) values (14,'AB',7000,'左边');
insert into aa(id,type,dist,location) values (4,'A',11000,'左边');
insert into aa(id,type,dist,location) values (5,'AB',22000,'左边');
select * from (
select id,location,type,min(dist) from aa where type='A' group by location
union all
select id,location,type,min(dist) from aa where type='AB' group by location) m
where m.id not in(
select t1.id
from
(select id,location,type,min(dist) dist from aa where type='A' group by location) t1
join
(select id,location,type,min(dist) dist from aa where type='AB' group by location) t2
on t1.location=t2.location where t1.dist>t2.dist )