CASE 表 包括以下字段
CASE_ID(Case编号),OPENER_ID(开Case员工编号),CLOSER_ID(关Case员工编号),LOCATION_ID(办公室地址编号)
320 238 82 1
321 94 238 2
322 94 94 1REF 表包括以下字段
REF_ID(编号),FULLNAME(姓名)
82 张强
94 李丽
238 陈文平LOCATION 表包括以下字段
LOCATION_ID(编号),CITY(办公室地址)
1 BEIJING
2 CHENGDU要写PL SQL语句,根据OPENER_ID查询条件得到结果集,包括以下项目:CASE_ID,OPENER_NAME,CLOSER_NAME,CITY
应该怎么写?
如,查询编号94的CASE,列出:
321 李丽 陈文平 CHENGDU
322 李丽 李丽 BEIJING下面的语句肯定不对,请前辈修改?
SELECT CASE.CASE_ID,
REF.FULLNAME,
REF.FULLNAME,
LOCATION.CITY
FROM CASE
WHERE OPENER_ID="94"
AND CASE.LOCATION_ID=LOCATION.LOCATION_ID
AND CASE.OPENER_ID=REF.REF_ID
AND CASE.CLOSER_ID=REF.REF_ID
CASE_ID(Case编号),OPENER_ID(开Case员工编号),CLOSER_ID(关Case员工编号),LOCATION_ID(办公室地址编号)
320 238 82 1
321 94 238 2
322 94 94 1REF 表包括以下字段
REF_ID(编号),FULLNAME(姓名)
82 张强
94 李丽
238 陈文平LOCATION 表包括以下字段
LOCATION_ID(编号),CITY(办公室地址)
1 BEIJING
2 CHENGDU要写PL SQL语句,根据OPENER_ID查询条件得到结果集,包括以下项目:CASE_ID,OPENER_NAME,CLOSER_NAME,CITY
应该怎么写?
如,查询编号94的CASE,列出:
321 李丽 陈文平 CHENGDU
322 李丽 李丽 BEIJING下面的语句肯定不对,请前辈修改?
SELECT CASE.CASE_ID,
REF.FULLNAME,
REF.FULLNAME,
LOCATION.CITY
FROM CASE
WHERE OPENER_ID="94"
AND CASE.LOCATION_ID=LOCATION.LOCATION_ID
AND CASE.OPENER_ID=REF.REF_ID
AND CASE.CLOSER_ID=REF.REF_ID
REF.FULLNAME,
REF.FULLNAME,
LOCATION.CITY
FROM CASE,LOCATION,REF
WHERE OPENER_ID="94"
AND CASE.LOCATION_ID=LOCATION.LOCATION_ID
AND CASE.OPENER_ID=REF.REF_ID
AND CASE.CLOSER_ID=REF.REF_ID
现在的语句查询结果都是一样的:
321 李丽 李丽 CHENGDU
322 李丽 李丽 BEIJING
REF.FULLNAME,
REF.FULLNAME,
LOCATION.CITY
FROM CASE,LOCATION,REF
WHERE close_ID="94"
AND CASE.LOCATION_ID=LOCATION.LOCATION_ID
AND CASE.OPENER_ID=REF.REF_ID
AND CASE.CLOSER_ID=REF.REF_ID
c.FULLNAME,
d.FULLNAME,
b.CITY
FROM CASE a inner join LOCATION b
on a.LOCATION_ID=b.LOCATION_ID
left outer join REF c
on a.OPENER_ID=c.REF_ID
left outer join REF d
on a.CLOSER_ID=d.REF_ID
c.FULLNAME,
d.FULLNAME,
b.CITY
FROM CASE a inner join LOCATION b
on a.LOCATION_ID=b.LOCATION_ID
and a.close_ID="94"left outer join REF c
on a.OPENER_ID=c.REF_ID
left outer join REF d
on a.CLOSER_ID=d.REF_ID
Error ORA-00904:"B" "LOCATION_ID": Invalid identifier
字段名没错啊
on a.LOCATION_ID=b.LOCATION_ID看看是否和数据库的不一样啊
我是看不到你的数据库
你改改应该就好了
c.FULLNAME,
d.FULLNAME,
b.CITY,
FROM CASE a
inner join LOCATION b
on (b.LOCATION_REF=a.location_ref)
left outer join REF c
on (a.OPENER_ID=c.REF_ID)
left outer join REF d
on (a.CLOSER_ID=d.REF_ID)
where a.CLOSER_ID='94' order by
CASE_ID