select * from emp a where exists (select 1 from ( select brithday,count(ID) as c from emp group by brithday } b where a.brithday=b.brithday and b.c>1)
select a.ID , a.NAME , a.BRITHDAY from EMP a,EMP b where a.BRITHDAY = b.BRITHDAY
select a.ID , a.NAME , a.BRITHDAY from EMP a,EMP b where a.BRITHDAY = b.BRITHDAY AND A.ID<>B.ID
select * from emp t where exists (select id from emp where to_char(BRITHDAY,'mmdd')=to_char(t.BRITHDAY,'mmdd') and id!=t.id)
SQL> create table emp (id number,name varchar2(20),birthday varchar(20));表已创建。SQL> insert into emp values(1,'张明','1982-01-01');已创建 1 行。SQL> insert into emp values(2,'张彭','1982-05-01');已创建 1 行。SQL> insert into emp values(3,'张平','1982-05-01');已创建 1 行。SQL> insert into emp values(4,'张平','1986-03-01');已创建 1 行。SQL> insert into emp values(5,'张平','1986-01-01');已创建 1 行。SQL> insert into emp values(6,'李梦','1988-01-01');已创建 1 行。SQL> commit 2 /提交完成。SQL> select * from emp; ID NAME BIRTHDAY ---------- -------------------- -------------------- 1 张明 1982-01-01 2 张彭 1982-05-01 3 张平 1982-05-01 4 张平 1986-03-01 5 张平 1986-01-01 6 李梦 1988-01-01已选择6行。SQL> col name format a15 SQL> select birthday,ltrim(max(sys_connect_by_path(name,',')),',') name from (se lect substr(birthday,6,5) birthday,name,min(name) over(partition by substr(birth day,6,5)) min_name,(row_number() over(order by substr(birthday,6,5),name)+dense_ rank() over(order by substr(birthday,6,5))) numid from emp) start with name=min_ name connect by prior numid=numid-1 group by birthday;BIRTHDAY NAME ---------- --------------- 05-01 张彭,张平 01-01 李梦,张明,张平 03-01 张平SQL>
SELECT * FROM EMP A, EMP B WHERE TO_CHAR(A.BIRTHDATE) = TO_CHAR(B.BIRTHDATE) AND A.EMPNO<>B.EMPNO
SELECT A.* FROM EMP A, EMP B WHERE TO_CHAR(A.BIRTHDATE) = TO_CHAR(B.BIRTHDATE) AND A.EMPNO <> B.EMPNO
SELECT * FROM (SELECT T.*, COUNT(T.HIREDATE) OVER(PARTITION BY T.HIREDATE ORDER BY T.HIREDATE DESC) RN FROM EMP T) a WHERE a.RN >= 2 ORDER BY a.hiredate 这个应该可以
select distinct a.ID , a.NAME , a.BRITHDAY from EMP a,EMP b where a.BRITHDAY = b.BRITHDAY 这样是最简单的,并且能正确查询出结果
我想把所有的项目包的table下面的所有东西都删除了.请假这段程序.
谢谢啊!
select brithday,count(ID) as c from emp group by brithday
} b where a.brithday=b.brithday and b.c>1)
select a.ID , a.NAME , a.BRITHDAY from EMP a,EMP b where a.BRITHDAY = b.BRITHDAY
from emp t
where exists (select id from emp where to_char(BRITHDAY,'mmdd')=to_char(t.BRITHDAY,'mmdd') and id!=t.id)
2 /提交完成。SQL> select * from emp; ID NAME BIRTHDAY
---------- -------------------- --------------------
1 张明 1982-01-01
2 张彭 1982-05-01
3 张平 1982-05-01
4 张平 1986-03-01
5 张平 1986-01-01
6 李梦 1988-01-01已选择6行。SQL> col name format a15
SQL> select birthday,ltrim(max(sys_connect_by_path(name,',')),',') name from (se
lect substr(birthday,6,5) birthday,name,min(name) over(partition by substr(birth
day,6,5)) min_name,(row_number() over(order by substr(birthday,6,5),name)+dense_
rank() over(order by substr(birthday,6,5))) numid from emp) start with name=min_
name connect by prior numid=numid-1 group by birthday;BIRTHDAY NAME
---------- ---------------
05-01 张彭,张平
01-01 李梦,张明,张平
03-01 张平SQL>
*
FROM
EMP A,
EMP B
WHERE
TO_CHAR(A.BIRTHDATE) = TO_CHAR(B.BIRTHDATE) AND
A.EMPNO<>B.EMPNO
A.*
FROM
EMP A,
EMP B
WHERE
TO_CHAR(A.BIRTHDATE) = TO_CHAR(B.BIRTHDATE) AND
A.EMPNO <> B.EMPNO
FROM (SELECT T.*,
COUNT(T.HIREDATE) OVER(PARTITION BY T.HIREDATE ORDER BY T.HIREDATE DESC) RN
FROM EMP T) a
WHERE a.RN >= 2
ORDER BY a.hiredate 这个应该可以
这样是最简单的,并且能正确查询出结果