select iid,count(*) from tableA where bfald='Y' order by iid,having count(*)>1;
SELECT * FROM A WHERE (IID, BFALG) IN (SELECT IID, BFALY FROM A WHERE BFALY = 'Y' GROUP BY IID HAVING(IID) > 1);
--> --> (jake)生成测试数据
declare n number; begin select count(*) into n from user_tables where table_name=upper('T_200810'); if n>0 then execute immediate 'drop table T_200810'; end if; end; Create table T_200810 as select 1 iid,1 someth,'Y' bfalg from dual union all select 1,2,'Y' from dual union all select 2,1,'N' from dual union all select 2,2,'Y' from dual ; --得到 bfalg 有 大于1个Y 的 的所有数据Select * from T_200810 where iid in (select iid from T_200810 where bfalg='Y' group by iid HAVING count(bfalg)>1)
-- 如果按IID分组: SQL> SELECT IID 2 FROM TABLE_NAME TT 3 WHERE BFALG = 'Y' 4 GROUP BY IID 5 HAVING COUNT(1) > 1; IID ---------- 1-- 如果按SOMETH分组: SQL> SELECT SOMETH 2 FROM TABLE_NAME TT 3 WHERE BFALG = 'Y' 4 GROUP BY SOMETH 5 HAVING COUNT(1) > 1; SOMETH ---------- 2
SQL> SELECT * FROM A;IID SOMETH BFALG ---------- ---------- ---------- 1 1 Y 1 2 Y 2 1 N 2 2 YSQL> SQL> SELECT * 2 FROM A 3 WHERE IID IN (SELECT IID 4 FROM (select IID, 5 ROW_NUMBER() OVER(PARTITION BY IID ORDER BY IID) RN 6 FROM A 7 WHERE BFALG = 'Y') 8 WHERE RN > 1) 9 ;IID SOMETH BFALG ---------- ---------- ---------- 1 2 Y 1 1 Y
SQL> select * from tableD; IID SOMETH BFALG ---------- ---------- ----- 1 1 Y 1 2 Y 2 1 N 2 2 YSQL> select iid,count(*) from tableD where bfalg='Y' group by iid having count(*)>1; IID COUNT(*) ---------- ---------- 1 2
SQL> select * from tableD; IID SOMETH BFALG ---------- ---------- ----- 1 1 Y 1 2 Y 2 1 N 2 2 YSQL> select tableD.* from tableD,(select iid,count(*) from tableD where bfalg='Y' group by iid having count(*)>1) t where tableD.iid=t.iid; IID SOMETH BFALG ---------- ---------- ----- 1 1 Y 1 2 YSQL>
FROM A
WHERE (IID, BFALG) IN (SELECT IID, BFALY
FROM A
WHERE BFALY = 'Y'
GROUP BY IID
HAVING(IID) > 1);
--> --> (jake)生成测试数据
declare n number;
begin
select count(*) into n from user_tables where table_name=upper('T_200810');
if n>0 then
execute immediate 'drop table T_200810';
end if;
end;
Create table T_200810
as
select 1 iid,1 someth,'Y' bfalg from dual union all
select 1,2,'Y' from dual union all
select 2,1,'N' from dual union all
select 2,2,'Y' from dual
;
--得到 bfalg 有 大于1个Y 的 的所有数据Select * from T_200810 where iid in (select iid from T_200810 where bfalg='Y'
group by iid HAVING count(bfalg)>1)
SQL> SELECT IID
2 FROM TABLE_NAME TT
3 WHERE BFALG = 'Y'
4 GROUP BY IID
5 HAVING COUNT(1) > 1; IID
----------
1-- 如果按SOMETH分组:
SQL> SELECT SOMETH
2 FROM TABLE_NAME TT
3 WHERE BFALG = 'Y'
4 GROUP BY SOMETH
5 HAVING COUNT(1) > 1; SOMETH
----------
2
---------- ---------- ----------
1 1 Y
1 2 Y
2 1 N
2 2 YSQL>
SQL> SELECT *
2 FROM A
3 WHERE IID IN (SELECT IID
4 FROM (select IID,
5 ROW_NUMBER() OVER(PARTITION BY IID ORDER BY IID) RN
6 FROM A
7 WHERE BFALG = 'Y')
8 WHERE RN > 1)
9 ;IID SOMETH BFALG
---------- ---------- ----------
1 2 Y
1 1 Y
SQL> select * from tableD; IID SOMETH BFALG
---------- ---------- -----
1 1 Y
1 2 Y
2 1 N
2 2 YSQL> select iid,count(*) from tableD where bfalg='Y' group by iid having count(*)>1; IID COUNT(*)
---------- ----------
1 2
SQL> select * from tableD; IID SOMETH BFALG
---------- ---------- -----
1 1 Y
1 2 Y
2 1 N
2 2 YSQL> select tableD.* from tableD,(select iid,count(*) from tableD where bfalg='Y' group by iid having count(*)>1) t where tableD.iid=t.iid; IID SOMETH BFALG
---------- ---------- -----
1 1 Y
1 2 YSQL>