现在有如下字段iniyear、student、status,表名为student,数据如下:
iniyear student status
T2009 AAA Y
T2009 BBB Y
T2009 CCC N
T2009 DDD Y
T2008 AAA Y
T2008 BBB N
T2008 EEE Y
我想查找status是Y的,今年和去年的人名数据,包括左右连接,即显示如下(BBB去年状态为N,CCC今年状态位N不符合要求不显示):
iniyear student iniyear_1 student_1
T2009 AAA T2008 AAA
T2009 DDD null null
null null T2008 EEE
应如何写oracle语句
iniyear student status
T2009 AAA Y
T2009 BBB Y
T2009 CCC N
T2009 DDD Y
T2008 AAA Y
T2008 BBB N
T2008 EEE Y
我想查找status是Y的,今年和去年的人名数据,包括左右连接,即显示如下(BBB去年状态为N,CCC今年状态位N不符合要求不显示):
iniyear student iniyear_1 student_1
T2009 AAA T2008 AAA
T2009 DDD null null
null null T2008 EEE
应如何写oracle语句
where a.iniyear='T2009' and a.status='Y' and b.iniyear='T2008' and b.status='Y';
iniyear student status
T2009 AAA Y
T2009 BBB Y
T2009 CCC N
T2009 DDD Y
B2009 AAA Y
T2008 AAA Y
T2008 BBB N
T2008 EEE Y
B2008 AAA Y
我想查找status是Y的,今年和去年的人名数据,包括左右连接,即显示如下(BBB去年状态为N,CCC今年状态为N,不符合要求不显示):
iniyear student iniyear_1 student_1
T2009 AAA T2008 AAA
T2009 DDD null null
null null T2008 EEE
B2009 AAA B2008 AAA
应如何写oracle语句
from student a outer join student b on a.student=b.student and substr(a.iniyear,1,1)=substr(b.iniyear,1,1)
where substr(a.iniyear,2)='2009' and a.status='Y' and substr(b.iniyear,2)='2008' and b.status='Y';
from student as a outer join student as b
on a.student=b.student and substr(a.iniyear,1,1)=substr(b.iniyear,1,1)
and substr(a.iniyear,2)='2009' and substr(b.iniyear,2)='2008'
and a.status='Y' and b.status='Y';
好像连接查询不能使用where子句来着
select a.iniyear,a.student,b.iniyear iniyear_1,b.student student_1
from (select * from student) a outer join (select * from student) b on a.student=b.student and substr(a.iniyear,1,1)=substr(b.iniyear,1,1)
where substr(a.iniyear,2)='2009' and a.status='Y' and substr(b.iniyear,2)='2008' and b.status='Y';