sql 语句判断

Question

declare @type int
这里写判断
if 条件怎么写

select distinct
----查询条件
ab.useridx,ab.pos,ab.roomid,ab.nickname,ab.serverid,ab.parentid,ab.picurl,ab.[address],ab.[xpoint],ab.[ypoint]
,ab.[province],ab.[city],ab.[sex],ab.[roomname],ab.[ptype],ab.[onlineusernum],ab.[walevel],ab.[videotype],ab.[sorid],ab.[micimg]
,ab.[videoip],ab.[cncip],ab.[tmtip],ab.[tmtport],ab.[vaddress]
,isnull((select starlevel from Room_StarSonger with(nolock) where Room_StarSonger.useridx=ab.useridx),0) as starlevel
,isnull(lu.weekcash2,0) as weekcash1
,isnull(lu.addcash,0) as weekcash2
,isnull(lrs.ntype,0) as contractStatus
,ISNULL(mr.RtmpState,0) rtmpstate,ISNULL(ls.[level],1) anchorlevel --主播等级
,hotlabel
,@type as type into #templist

from @RoomUser ab
left join Live_RoomUser_Sign lrs with(nolock) on ab.useridx=lrs.useridx --签约主播
left join person_videoplay_info lu with(nolock) on ab.useridx=lu.useridx
left join MultiRoom mr with(nolock) on mr.idx=ab.roomid --20190718 
left join Live_StarExp(nolock) ls on ls.useridx=ab.useridx	--主播等级
left join VoiceAnchorInfo va on ab.useridx=va.useridx     --语音主播
where ab.useridx not in(select useridx from roomHotShield with(nolock) where 
    addtime>DATEADD(MI,-30,getdate()))
and ab.roomid not in(select [idx] from MultiRoom with(nolock) where [state]<>-1 and 
    [TYPE]=7)--过滤6人房主播

我想在外面加一个判断，判断我获取到的数据的useridx是否在语音主播表里面，如果在的，type变成1，不在的变成0，求各位大佬支点招

Answer 1

select useridx from (
select distinct
----查询条件
ab.useridx,ab.pos,ab.roomid,ab.nickname,ab.serverid,ab.parentid,ab.picurl,ab.[address],ab.[xpoint],ab.[ypoint]
,ab.[province],ab.[city],ab.[sex],ab.[roomname],ab.[ptype],ab.[onlineusernum],ab.[walevel],ab.[videotype],ab.[sorid],ab.[micimg]
,ab.[videoip],ab.[cncip],ab.[tmtip],ab.[tmtport],ab.[vaddress]
,isnull((select starlevel from Room_StarSonger with(nolock) where Room_StarSonger.useridx=ab.useridx),0) as starlevel
,isnull(lu.weekcash2,0) as weekcash1
,isnull(lu.addcash,0) as weekcash2
,isnull(lrs.ntype,0) as contractStatus
,ISNULL(mr.RtmpState,0) rtmpstate,ISNULL(ls.[level],1) anchorlevel --主播等级
,hotlabel
,@type as type into #templist
from @RoomUser ab
left join Live_RoomUser_Sign lrs with(nolock) on ab.useridx=lrs.useridx --签约主播
left join person_videoplay_info lu with(nolock) on ab.useridx=lu.useridx
left join MultiRoom mr with(nolock) on mr.idx=ab.roomid --20190718
left join Live_StarExp(nolock) ls on ls.useridx=ab.useridx --主播等级
left join VoiceAnchorInfo va on ab.useridx=va.useridx --语音主播
where ab.useridx not in(select useridx from roomHotShield with(nolock) where
addtime>DATEADD(MI,-30,getdate()))
and ab.roomid not in(select [idx] from MultiRoom with(nolock) where [state]<>-1 and
[TYPE]=7)--过滤6人房主播)
) as dd

select * from dd where useridx in (select useridx from语音主播表)

sql里判断一般用case when 即：CASE 成绩 WHEN 'A' THEN '优' ELSE '不及格' END

希望可以帮到你。

Answer 2

IIF(va.useridx IS NOT NULL, 1, 0) AS type

不是很清楚你的資料表，大概是這樣吧

Answer 3

分两个语句？先用一个临时表存 语音主播 然后在exists ，一个思路

Answer 4

这样可以实现功能，但是效率会比较差，结果集包成 cet，
里面的查询多输出一个 语音主播表的id，如果left到了就不会是空，这样最后可以根据 是否空判断 type是不是要变成1

with cet as(
select distinct
--begin add 增加查询出一个 va表的主键
va.Id VoiceAnchorInfoId,
--end add
from @RoomUser ab
left join Live_RoomUser_Sign lrs with(nolock) on ab.useridx=lrs.useridx --签约主播
left join person_videoplay_info lu with(nolock) on ab.useridx=lu.useridx
left join MultiRoom mr with(nolock) on mr.idx=ab.roomid --20190718
left join Live_StarExp(nolock) ls on ls.useridx=ab.useridx --主播等级
left join VoiceAnchorInfo va on ab.useridx=va.useridx --语音主播
where ab.useridx not in(select useridx from roomHotShield with(nolock) where
addtime>DATEADD(MI,-30,getdate()))
and ab.roomid not in(select [idx] from MultiRoom with(nolock) where [state]<>-1 and
[TYPE]=7)--过滤6人房主播
)
select 原始你的那些字段排除 type字段
,case when VoiceAnchorInfoId is null then cet.type else 1 end type from cet

Answer 5

DECODE()