网友提出的问题.一个表有3列(id name age)我相求age一样情况下,id不一样的。
原始数据如下:
1 x 10
2 y 11
3 x 10
2 x 11
5 z 11
要达到以下效果:
1 x 10
3 x 10
5 z 11
查询标准sql适用于所有数据库并且少量数据的情况.海量数据则需结合数据库特性实现.创建表和插入数据采用PostgreSQL.
SET client_encoding = 'UTF8';
--在命令行执行以下命令
create table test010(
id integer not null,
name text not null,
age integer not null
);
COPY test010(id, name, age) FROM stdin;
1 x 10
2 y 11
3 x 10
2 x 11
5 z 11
\.
--查询标准sql实际如下,适用于所有数据库
select id,name,age
from(
select t1.id,t1.name,t1.age,t2.icount
from test010 as t1
inner join (select id,count(*) as icount from test010 group by id) as t2
on t1.id=t2.id
) as tmp
where icount=1 order by id
--第二个版本
select t1.id,t1.name,t1.age
from test010 as t1
inner join (select id from (select id,count(*) as icount from test010 group by id) as t3 where icount=1) as t2
on t1.id=t2.id
order by t1.id