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不知道理解错了没有!
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语句怎样写?
这个地方用exist不合适,会导致每条记录都会再group 一次,效率低下,应该用inSELECT * FROM TABLE1 T WHERE t.a, t.b in ( SELECT A,B FROM TABLE1 GROUP BY (a,b) HAVING COUNT('X')>1 )
--测试数据 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
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;
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; 學習學習
SELECT * FROM t1 T 修改成 SELECT * FROM t1 as T
改成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
这是我修改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 ------------------
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不知道理解错了没有!
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语句怎样写?
SELECT A,B FROM TABLE1 GROUP BY (a,b) HAVING COUNT('X')>1 )
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
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;
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;
學習學習
修改成
SELECT * FROM t1 as T
ERROR: schema "t" does not exist
SQL 状态: 3F000
在PGsql下测试的!
------建立表-------------
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
------------------