Question

Oracle中如何的decode函数的使用

下面是我的一个SQL语句，不用看其他的，就给我看一下decode中的语句，我的意思是当 c.Gsptype的值符合n (17, 18, 19, 21, 22, 101) And Not Between 50 And 99，为b.rgid,否则为0；这个decode语句怎么写？我的是错误的

Select Count(*),

decode (c.Gsptype,In (17, 18, 19, 21, 22, 101) And Not Between 50 And 99, b.rgid,'0') b.rgid

From Bms_St_Rg_Dtl b, Pub_Goods c
Where b.Goodsid = c.Goodsid
And c.Gsptype In (17, 18, 19, 21, 22, 101)
Or c.Gsptype Between 50 And 99
Group By b.rgid

Answer 1

decode中的第二个值应该是一个吧，好像不能使用其他的操作符。

追问

我觉得也是，那你用case when then 能不能做出来?

追答

是不是用where条件就能搞定呀，按照条件分两部分，是不是比case更方便点。
Select c.Gsptype, b.rgid
From Bms_St_Rg_Dtl b, Pub_Goods c
Where b.Goodsid = c.Goodsid
And c.Gsptype In (17, 18, 19, 21, 22, 101)
and not c.Gsptype Between 50 And 99
union c.Gsptype, b.rgid,'0'
Select c.Gsptype, b.rgid
From Bms_St_Rg_Dtl b, Pub_Goods c
Where b.Goodsid = c.Goodsid
And ( c.Gsptype not In (17, 18, 19, 21, 22, 101)
or c.Gsptype Between 50 And 99 )