首页 新闻 搜索 专区 学院

sql 语句判断

1
悬赏园豆:50 [待解决问题]

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,求各位大佬支点招

sql
码农工具人的主页 码农工具人 | 初学一级 | 园豆:6
提问于:2020-09-16 12:31

有大佬帮我一下

码农工具人 2天前

而且你是根据useridx关联了VoiceAnchorInfo (语言主播表)吗,如果没有的数据应该不会查出来吧。查出来的都是有的

Yimi依米 15小时前
< >
分享
所有回答(5)
0
IIF(va.useridx IS NOT NULL, 1, 0) AS type

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

RosonJ | 园豆:3740 (老鸟四级) | 2020-09-16 13:31
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

Benjamin杰明 | 园豆:204 (菜鸟二级) | 2020-09-16 13:37
0

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

..小猫咪 | 园豆:191 (初学一级) | 2020-09-16 16:37
0

DECODE()

intqu | 园豆:202 (菜鸟二级) | 2020-09-17 18:01
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

希望可以帮到你。

Yimi依米 | 园豆:225 (菜鸟二级) | 2020-09-18 17:00
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册