select distinct InnerCode, b.SecuCode as GPDM,b.CompanyCode as vc_ccode, case left( case isnull(SecuMarket,0)
when 0 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'OH' end
when 83 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0 )+'OF' else SecuCode+'SH' end
when 90 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isn(c.StopDate,0)=0)+'OF' else SecuCode+'SZ' end
when 73 then SecuCode+'YH'
when 74 then SecuCode+'JY'
when 72 then SecuCode+'HK'
when 71 then SecuCode+'GT'
when 89 then SecuCode+'YH'
when 81 then SecuCode+'QB' else SecuCode+'OH' end ,1)
when 'Z' then substring(case isnull(SecuMarket,0) when 0 then
case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF'else SecuCode+'OH' end
when 83 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'SH' end
when 90 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'SZ' end
when 73 then SecuCode+'YH'
when 74 then SecuCode+'JY'
when 72 then SecuCode+'HK'
when 71 then SecuCode+'GT'
when 89 then SecuCode+'YH'
when 81 then SecuCode+'QB' else SecuCode+'OH' end,2,len(SecuCode+'SH'))
else case isnull(SecuMarket,0) when 0 then
case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'OH' end
when 83 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'SH' end
when 90 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'SZ' end
when 73 then SecuCode+'YH'
when 74 then SecuCode+'JY'
when 72 then SecuCode+'HK'
when 71 then SecuCode+'GT'
when 89 then SecuCode+'YH'
when 81 then SecuCode+'QB' else SecuCode+'OH' end end as vc_scode,
case left(SecuCode,1) when 'Z' then substring(SecuCode,2,len(SecuCode)) else case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0) else SecuCode end end as vc_code , case when exists(select 1 from QT_IndexQuote f where f.InnerCode=b.InnerCode) then 1 else 0 end as isIndex from SecuMain b where b.SecuMarket in (71,73,81,83,89,90,93)
查询时报子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的
when 0 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'OH' end
when 83 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0 )+'OF' else SecuCode+'SH' end
when 90 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isn(c.StopDate,0)=0)+'OF' else SecuCode+'SZ' end
when 73 then SecuCode+'YH'
when 74 then SecuCode+'JY'
when 72 then SecuCode+'HK'
when 71 then SecuCode+'GT'
when 89 then SecuCode+'YH'
when 81 then SecuCode+'QB' else SecuCode+'OH' end ,1)
when 'Z' then substring(case isnull(SecuMarket,0) when 0 then
case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF'else SecuCode+'OH' end
when 83 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'SH' end
when 90 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'SZ' end
when 73 then SecuCode+'YH'
when 74 then SecuCode+'JY'
when 72 then SecuCode+'HK'
when 71 then SecuCode+'GT'
when 89 then SecuCode+'YH'
when 81 then SecuCode+'QB' else SecuCode+'OH' end,2,len(SecuCode+'SH'))
else case isnull(SecuMarket,0) when 0 then
case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'OH' end
when 83 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'SH' end
when 90 then case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0)+'OF' else SecuCode+'SZ' end
when 73 then SecuCode+'YH'
when 74 then SecuCode+'JY'
when 72 then SecuCode+'HK'
when 71 then SecuCode+'GT'
when 89 then SecuCode+'YH'
when 81 then SecuCode+'QB' else SecuCode+'OH' end end as vc_scode,
case left(SecuCode,1) when 'Z' then substring(SecuCode,2,len(SecuCode)) else case SecuCategory when 8 then ( select c.SecuCode from LC_CodeChange c where c.InnerCode=b.InnerCode and c.CodeDefine=8 and isnull(c.StopDate,0)=0) else SecuCode end end as vc_code , case when exists(select 1 from QT_IndexQuote f where f.InnerCode=b.InnerCode) then 1 else 0 end as isIndex from SecuMain b where b.SecuMarket in (71,73,81,83,89,90,93)
查询时报子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的
一般来说case when 语句是判断某个条件或者某个值而返回一个值,而你在这里用子查询可能返回的不只一条记录,那么就会报错了,具体的修改要视需求而定。
建议不要用子查询作为字段值,这样的效率很低而且容易造成你报的错误,可以用多表连接来实现。
当你用到 then (子查询) 的时候,子查询得到的记录可能不止一条,就会抱这个错误。详细看看你的子查询,
你可以在子查询的条件中加上Rownum < 2
子查询里面
isnull、left
是mssql里的语句吧
子查询返回多行,你看看如何取你要的结果
如果随便取一条都行,就加个top 1
子查询里面