SELECT o.id, o.NAME, o.enterprisename, o.createtime, p.NAME AS personname, i.NAME AS categoryname, c2.classification AS categoryjoint, u.username FROM organization o, person p, category c1, industry i, category c2, users u WHERE o.id=p.groupid AND o.id=c1.groupid AND c1.classification=i.joint AND o.id=c2.groupid AND o.creatorid=u.joint AND o.id IN (SELECT id FROM organization o1 WHERE o1.industry2_joint='21') AND c2.classification IN (SELECT i1.joint FROM industry i1 WHERE i1.level1='d' OR i1.level2='d' OR i1.level3='d' OR i1.level4='d')
试一试,下面的sql:select t.id, t.name, t.ENTERPRISENAME, t.CREATETIME, t1.name as personName, t2.name as categoryName, t2.Classification as categoryJoint, t3.username from organization t, person t1, (select a1.name,a2.GroupID,a2.Classification from industry a1,category a2 where a2.Classification = a1.joint ) t2, users t3
where t.industry2_joint = '21' and t.categoryJoint in (select joint from industry where industry.level1 = 'D' or industry.level2 = 'D' or industry.level3 = 'D' or industry.level4 = 'D') and t.ID=t1.GroupID(+) and t.ID=t2.GroupID(+) and t.CREATORID = t3.JOINT(+)
先谢谢了。 这样写是比较清晰,不过这样查询还是在 and t.categoryJoint in (select joint from industry where industry.level1 = 'D' or industry.level2 = 'D' or industry.level3 = 'D' or industry.level4 = 'D') 这个条件上出错。t2.Classification as categoryJoint是其他表的字段,为什么就是不行呢?
p.NAME AS personname, i.NAME AS categoryname,
c2.classification AS categoryjoint, u.username
FROM organization o, person p, category c1,
industry i, category c2, users u
WHERE o.id=p.groupid
AND o.id=c1.groupid AND c1.classification=i.joint
AND o.id=c2.groupid
AND o.creatorid=u.joint
AND o.id IN
(SELECT id FROM organization o1 WHERE o1.industry2_joint='21')
AND c2.classification IN
(SELECT i1.joint FROM industry i1
WHERE i1.level1='d' OR i1.level2='d' OR i1.level3='d' OR i1.level4='d')
ORA-01427 但行子查询返回多于一个行
简单看了你的SQL,你主要是表关联的SQL写的少,硬按照逻辑要求憋出来的sql。你再确定一下!
t.name,
t.ENTERPRISENAME,
t.CREATETIME,
t1.name as personName,
t2.name as categoryName,
t2.Classification as categoryJoint,
t3.username
from organization t,
person t1,
(select a1.name,a2.GroupID,a2.Classification from industry a1,category a2
where a2.Classification = a1.joint ) t2,
users t3
where t.industry2_joint = '21'
and t.categoryJoint in (select joint
from industry
where industry.level1 = 'D'
or industry.level2 = 'D'
or industry.level3 = 'D'
or industry.level4 = 'D')
and t.ID=t1.GroupID(+)
and t.ID=t2.GroupID(+)
and t.CREATORID = t3.JOINT(+)
这样写是比较清晰,不过这样查询还是在
and t.categoryJoint in (select joint
from industry
where industry.level1 = 'D'
or industry.level2 = 'D'
or industry.level3 = 'D'
or industry.level4 = 'D')
这个条件上出错。t2.Classification as categoryJoint是其他表的字段,为什么就是不行呢?