用exists 查询语句②:select ... from ... where ... and ((exists ( 语句① ) and A=B ) or (not exists ( 语句① ) and A=C ) )
--非要用一个SQL ,把条件可以写到一个SQL , 先判断,然后做查询!!
lz的子查询是出现在什么地方的?如果是出现在select后面并用了嵌套的话,可能会进逻辑死套
用這個可以不? where case when 結果 is null then A=B else A=C end
我希望逻辑是这样的: …… if 子查询结果非空 then 主查询增加限制条件:A=B else 主查询不增加任何限制条件,或可以增加一个永为真的条件 ……我的1种实现是: 其他主查询and... and ((exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and RULE = 'CK')) and (select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE) IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and RULE = 'CK')) 其他主查询and... 具体说明: 如果我给这个人定了一个他只能看到某些东西的规则——则“附加”主查询的限制条件:当前想查询的东西必须在他能看到的范围内;但要是我根本没给他定规则哪些能看、哪些不能看,——则“不附加”任何主查询的限制条件。 但我实际操作发现无论什么情况都认为exists是真,也就附加之后的and 条件,导致可能查询不到实际应该获得的东西。因此之后我就在exists前+了if: and (if (exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and RULE = 'CK')) and (select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE) IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and RULE = 'CK')),就报ORA:00936缺少表达式,我再把if exists 放一个括号里,又报ORA:00920无效的关系运算符。我已经乱了,大家快来帮帮我!5555~~~
在你的語句的基礎上改的 and ( exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK') and Exists (select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE And WL_GROUP IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK')))
paoluo(一天到晚游泳的鱼),报错了,缺少右括号: and ( exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK') and Exists (select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE And WL_GROUP IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK'))) 错误指向第一个exists之前
你單獨測試這樣是沒有問題的。Select 1 Where 1=1 and ( exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK') and Exists (select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE And WL_GROUP IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK'))) 你在後面加上個")",這樣試試 and ( exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK') and Exists (select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE And WL_GROUP IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK'))))或者,將你的完整語句帖出來看看
Select 1 from CKGL Where 1=1 and ( exists (select ITEM_GROUP from QTGL_B_YWY_CODETYPE where YWY = '607078' and RULE = 'CK') and exists (select ITEM_GROUP from JCSJ_B_ITEM where ITEM_CODE_TYPE = CHGL_B_OUT.SM_TYPE and ITEM_CODE = CHGL_B_OUT.SM_CODE IN (select ITEM_GROUP from QTGL_B_YWY_CODETYPE where YWY = '607078' and RULE = 'CK'))) 就已经报错了,我用的ORACLE,后面加)因为不行
where ...
and ((exists (
语句①
)
and A=B
) or
(not exists (
语句①
)
and A=C
)
)
where case
when 結果 is null then
A=B
else A=C
end
……
if
子查询结果非空
then
主查询增加限制条件:A=B
else
主查询不增加任何限制条件,或可以增加一个永为真的条件
……我的1种实现是:
其他主查询and...
and
((exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and RULE = 'CK'))
and (select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE) IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and RULE = 'CK'))
其他主查询and...
具体说明:
如果我给这个人定了一个他只能看到某些东西的规则——则“附加”主查询的限制条件:当前想查询的东西必须在他能看到的范围内;但要是我根本没给他定规则哪些能看、哪些不能看,——则“不附加”任何主查询的限制条件。
但我实际操作发现无论什么情况都认为exists是真,也就附加之后的and 条件,导致可能查询不到实际应该获得的东西。因此之后我就在exists前+了if:
and
(if (exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and RULE = 'CK'))
and (select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE) IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and RULE = 'CK')),就报ORA:00936缺少表达式,我再把if exists 放一个括号里,又报ORA:00920无效的关系运算符。我已经乱了,大家快来帮帮我!5555~~~
and
(
exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK')
and Exists
(select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE And WL_GROUP
IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK')))
and
(
exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK')
and Exists
(select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE And WL_GROUP
IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK')))
错误指向第一个exists之前
and
(
exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK')
and Exists
(select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE And WL_GROUP
IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK')))
你在後面加上個")",這樣試試
and
(
exists (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK')
and Exists
(select WL_GROUP from JBDM_WL where WL_TYPE = CKGL.WL_TYPE and WL_CODE = CKGL.WL_CODE And WL_GROUP
IN (select WL_GROUP from YWYGL_CODE where YWY = '5743' and [RULE] = 'CK'))))或者,將你的完整語句帖出來看看
and
(
exists
(select ITEM_GROUP from QTGL_B_YWY_CODETYPE where YWY = '607078' and RULE = 'CK')
and exists
(select ITEM_GROUP from JCSJ_B_ITEM where ITEM_CODE_TYPE = CHGL_B_OUT.SM_TYPE
and ITEM_CODE = CHGL_B_OUT.SM_CODE
IN (select ITEM_GROUP from QTGL_B_YWY_CODETYPE where YWY = '607078' and RULE = 'CK')))
就已经报错了,我用的ORACLE,后面加)因为不行