sql怎样过滤重复记录？

[comicx]

数据库结构如下： 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语句应该怎样写呢？

[hongqi162]

结果是怎样的? distinct不可以吗？

[hongqi162]

最好把表结构 测试数据 测试输出结果发出来

[临摹]

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

不知道理解错了没有!

[comicx]

a数据库，b表内容为：
id name address
1 a 1
2 a 2
3 a 1
4 a 3
5 b 2
6 b 2
7 b 3
8 b 1
9 c 1
10 d 2
我想取得的表为：
id name address
1 a 1
3 a 1
5 b 2
6 b 2
想从表b中以name取重复记录并分组，再取address重复记录！
这只是举例，实际表更复杂！
不知道sql语句怎样写？

[bjt_]

这个地方用exist不合适，会导致每条记录都会再group 一次，效率低下,应该用in

SELECT * FROM TABLE1 T WHERE t.a, t.b in (
SELECT A,B FROM TABLE1 GROUP BY (a,b) HAVING COUNT('X')>1 )

[hongqi162]

--测试数据
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);
--查询结果
1 a 1
3 a 1
5 b 2
6 b 2

[mantisXF]

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;

[shoter193]

create table t11(id int, name varchar2(10), address int);
insert into t11
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 distinct a.* from t11,t11 a
where t11.id<>a.id and t11.name=a.name and t11.address =a.address;


學習學習

[hongqi162]

你不使用表别名的方法在试试

[hongqi162]

SELECT * FROM t1 T
修改成
SELECT * FROM t1 as T

[tl0352118]

学习，学习，浩浩学习
从没这样想过。。。。。
向搂住学习

[comicx]

知道了，但是在mysql里用该怎么改？

[hongqi162]

这个语句在mysql中可以直接运行的吧

[comicx]

试了一下，在Oracle里没有问题，但是这不是标准sql语句，在mysql和其他数据库中不能用，好像是不支持用t,有没有通用的方法不使用t。

[comicx]

改成as t和原先一样，错误提示：
ERROR: schema "t" does not exist
SQL 状态: 3F000
在PGsql下测试的！

[壹 佰]

SELECT DISTINCT A.ID,A.NAME,A.ADDRESS FROM b A, b B WHERE A.ID<>B.ID AND A.NAME=B.NAME AND A.ADDRESS=B.ADDRESS ORDER BY A.NAME

[wwq15947619971]

顶

[Gangzai1983]

这是我修改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)
-----------------------
--------查询结果--------
1 a 1
3 a 1
5 b 2
6 b 2
------------------