sql怎样过滤重复记录？
数据库结构如下： sql语句过滤后返回集为：
a 1 a 1
a 2 a 1
a 1 b 2
a 3 b 2
b 1
b 2
b 4
b 2
这样的sql语句应该怎样写呢？
[解决办法]
create table table1(a char(10),b integer);
insert into table1 values( 'a ',1);
insert into table1 values( 'a ',2);
insert into table1 values( 'a ',1);
insert into table1 values( 'a ',3);
insert into table1 values( 'b ',1);
insert into table1 values( 'b ',2);
insert into table1 values( 'b ',4);
insert into table1 values( 'b ',2);
commit;
SELECT * FROM TABLE1 T WHERE EXISTS(
SELECT A,B FROM TABLE1 GROUP BY (a,b) HAVING COUNT( 'X ')> 1
AND T.A=A AND T.B=B);
A B
---------- ----------
a 1
a 1
b 2
b 2
不知道理解错了没有!
[解决办法]
--测试数据
create table t1(id int, name varchar2(10), address int)
insert into t1
select 1, 'a ',1 from dual union all
select 2, 'a ',2 from dual union all
select 3, 'a ',1 from dual union all
select 4, 'a ',3 from dual union all
select 5, 'b ',2 from dual union all
select 6, 'b ',2 from dual union all
select 7, 'b ',3 from dual union all
select 8, 'b ',1 from dual union all
select 9, 'c ',1 from dual union all
select 10, 'd ',2 from dual;
--执行查询
SELECT * FROM t1 T WHERE EXISTS(
SELECT name,name FROM T1 GROUP BY (name,address) HAVING COUNT( 'id ')> 1
AND T.name=name AND T.address=address);
--查询结果
1a1
3a1
5b2
6b2
[解决办法]
select t.id,t.name,t.address
from tablename t,
(
select ta.name,ta.address
from tablename ta
group by ta.name,ta.address
having count(*) > 1
)tt
where t.name = tt.name
and t.address = tt.address;
[解决办法]
这是我修改hongqi162(失踪的月亮) 的
------建立表-------------
create table t1(id int, name varchar(10), address int)
insert into t1
select 1, 'a ',1 union all
select 2, 'a ',2 union all
select 3, 'a ',1 union all
select 4, 'a ',3 union all
select 5, 'b ',2 union all
select 6, 'b ',2 union all
select 7, 'b ',3 union all
select 8, 'b ',1 union all
select 9, 'c ',1 union all
select 10, 'd ',2
--------------------
-------查询------------
SELECT * FROM t1 T WHERE EXISTS(
SELECT name,name
FROM T1
GROUP BY name,address
HAVING COUNT( 'id ')> 1
AND T.name=name AND T.address=address)
-----------------------
--------查询结果--------
1a1
3a1
5b2
6b2
------------------
