SELECT *, A.ELEID AS ALLOWID FROM BAS_FORME A left join BAS_FORMEATTR B
on A.ELEID=ISNULL(B.ELEID, '')
AND A.OBJID='00000329' and ISNULL(B.ELEID, '')!=''
AND B.DLFLG!='1'
ORDER BY A.ELEID结果找到的结果中,有A.OBJID='00000000'等等很多不符合的数据,这此数据里的B部分的都是空
请问是哪里出错了?
on A.ELEID=ISNULL(B.ELEID, '')
AND A.OBJID='00000329' and ISNULL(B.ELEID, '')!=''
AND B.DLFLG!='1'
ORDER BY A.ELEID结果找到的结果中,有A.OBJID='00000000'等等很多不符合的数据,这此数据里的B部分的都是空
请问是哪里出错了?
SELECT *, A.ELEID AS ALLOWID FROM BAS_FORME A left join BAS_FORMEATTR B
on A.ELEID=B.ELEID AND A.OBJID='00000329' and ISNULL(B.ELEID, '')<>''
AND B.DLFLG<>'1'
ORDER BY A.ELEID
on A.ELEID=ISNULL(B.ELEID, '')
where A.OBJID='00000329' and ISNULL(B.ELEID, '')!=''
AND B.DLFLG!='1'
ORDER BY A.ELEID
试试
ISNULL(B.ELEID, '')!=''
我怎么看着这么不爽呢写成B.ELEID is not null and B.ELEID != ''不行吗
还可以用到索引。
SELECT *, A.ELEID AS ALLOWID FROM BAS_FORME A
left join BAS_FORMEATTR B on A.ELEID=ISNULL(B.ELEID, '')
where A.OBJID='00000329' and ISNULL(B.ELEID, '')<>''
AND B.DLFLG <> '1'
ORDER BY A.ELEID
left join BAS_FORMEATTR B
on A.ELEID=ISNULL(B.ELEID, '')
where A.OBJID='00000329' and B.ELEID is not null AND B.DLFLG!='1'
ORDER BY A.ELEID
SELECT B.*, A.ELEID AS ALLOWID FROM BAS_FORME A
left join BAS_FORMEATTR B
on A.ELEID=ISNULL(B.ELEID, '')
AND A.[OBJID]='00000329'
and ISNULL(B.ELEID, '')<>''
AND B.DLFLG <>'1'
ORDER BY A.ELEID
SELECT B.*, A.ELEID AS ALLOWID FROM BAS_FORME A
left join BAS_FORMEATTR B
on A.ELEID=ISNULL(B.ELEID, '')
where 1=1
AND A.[OBJID]='00000329'
and ISNULL(B.ELEID, '') <> ''
AND B.DLFLG <> '1'
ORDER BY A.ELEID
SELECT *, A.ELEID AS ALLOWID FROM BAS_FORME A left join BAS_FORMEATTR B
on A.ELEID=ISNULL(B.ELEID, '')
AND A.OBJID='00000329' and ISNULL(B.ELEID, '')!=''
AND B.DLFLG!='1'
ORDER BY A.ELEID
SELECT *, A.ELEID AS ALLOWID FROM BAS_FORME A left join BAS_FORMEATTR B
on A.ELEID=ISNULL(B.ELEID, '')
where A.OBJID='00000329' and ISNULL(B.ELEID, '')<>''
AND B.DLFLG<>'1'
ORDER BY A.ELEID
SELECT * ,
A.ELEID AS ALLOWID
FROM BAS_FORME A
inner JOIN BAS_FORMEATTR B ON A.ELEID = ISNULL(B.ELEID, '')
AND A.OBJID = '00000329'
AND ISNULL(B.ELEID, '') != ''
AND B.DLFLG != '1'
ORDER BY A.ELEID
如果想过滤掉A.OBJID<>'00000329' 的记录,建议改为
SELECT *, A.ELEID AS ALLOWID FROM BAS_FORME A left join BAS_FORMEATTR B
on A.ELEID=ISNULL(B.ELEID, '')
AND ISNULL(B.ELEID, '')!=''
AND B.DLFLG!='1'
where A.OBJID='00000329'
ORDER BY A.ELEID
我原来的语句,结果就是cxmcxm说的这样的
要在where 里做限制而不是在on ...里做限制