sql 语句判断
1
悬赏园豆：50
[已解决问题]
解决于 2020-09-21 14:54
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，求各位大佬支点招
最佳答案
0
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
希望可以帮到你。
收获园豆：20
其他回答(4)
0
IIF(va.useridx IS NOT NULL, 1, 0) AS type
不是很清楚你的資料表，大概是這樣吧
收获园豆：5
0
这样可以实现功能，但是效率会比较差，结果集包成 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
收获园豆：5
0
分两个语句？先用一个临时表存 语音主播 然后在exists ，一个思路
收获园豆：10
0
DECODE()
收获园豆：10
有大佬帮我一下
– 码农工具人 3年前而且你是根据useridx关联了VoiceAnchorInfo （语言主播表）吗，如果没有的数据应该不会查出来吧。查出来的都是有的
– Yimi依米 3年前