嵌套SQL查询 [英] Nested SQL Query

问题描述

你好朋友，
我有用列

Hello friends,
I have table called with columns

tbl_actiontable (jobno,actionstatus,cycle)

调用的表
其中循环可以取值1,2,3 ..
和actionstatus 55,56,33

我的查询是我想找出
1.最大循环数
2.查找是否在该周期中是否存在actionstatus = 33
3.如果2个条件为假，则m查找是否有actionstatus = 55或56

如何在一个sql查询中执行此任务

我尝试了以下操作，能够获得1和3的条件

where cycle can take value 1,2,3..
and actionstatus 55,56,33

My query is I''m trying to find out
1. Max cycle
2. Find if for that cycle, if there is actionstatus=33
3. if 2 condition is false, m finding if there is actionstatus = 55 or 56

How can i carry out this task in one sql query

I tried following, I was able to get 1 and 3 condition

select 1 as color from tbl_action_history where   actionstatus=56  And jobno=92397   And
cycle in (select MAX(cycle) from tbl_action_history where jobno=92397)

我无法检查第二种情况，而要检查3种情况

在此先感谢

I''m not able to check second condition and go for 3 condition

Thanks in advance

推荐答案

SELECT 1 
FROM tbl_action_history
WHERE EXISTS(SELCT MAX(cycle) FROM tbl_action_history WHERE jobno=92397 AND (actionstatus = 33 OR actionstatus = 55 OR actionstatus=56))

This might help you.

我不是100％不确定您要查找的内容，但是此代码将首先检查条件1和&. 2，然后条件3.
然后根据优先级选择前1名.

I''m not 100% sure what you''re looking for, but this code will first check for conditions 1 & 2 and then condition 3.
Then it selects the top 1 based on priority.

declare @actiontable TABLE (jobno int,
                            actionstatus int,
                            cycle int)

insert into @actiontable
values(5, 33, 14)
insert into @actiontable
values(5, 45, 20)
insert into @actiontable
values(5, 55, 17)
insert into @actiontable
values(5, 56, 25)

select top 1 *
from
((select 1 as priority, jobno, actionstatus, cycle
    from @actiontable
    where jobno = 5
    and cycle = (select MAX(cycle)
            from @actiontable
            where jobno = 5)
    and actionstatus = 33)

union all

(select top 1 2, jobno, actionstatus, cycle
    from @actiontable
    where jobno = 5
    and actionstatus in (55, 56))) as x
order by priority	

这篇关于嵌套SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！