物料已分好类第一层014 第二层01401 01402 01403 第三层 0140101 0140102 0140103 0140201....
已此类推现在自己做了一个查询,结果老提示同样的错误
declare @classid varchar(30)select classid =
case
when len(
(select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'))>=3
then substring ((select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'),1,3)
else nullendfrom pubitemclass运行后提示:消息 512,级别 16,状态 1,第 3 行
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。(0 行受影响)
请问我该怎么改才好.
已此类推现在自己做了一个查询,结果老提示同样的错误
declare @classid varchar(30)select classid =
case
when len(
(select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'))>=3
then substring ((select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'),1,3)
else nullendfrom pubitemclass运行后提示:消息 512,级别 16,状态 1,第 3 行
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。(0 行受影响)
请问我该怎么改才好.
declare @classid varchar(30) select classid =
case
when len(
(select distinct top 1 (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'))>=3
then substring ((select distinct top 1 (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'),1,3)
else null end from pubitemclass
已此类推 现在自己做了一个查询,结果老提示同样的错误
declare @classid varchar(30) select classid =
case
when len(
(select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'))>=3
then substring ((select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'),1,3)
else null end from pubitemclass
运行后提示: 消息 512,级别 16,状态 1,第 3 行
子查询返回的值多于一个。当子查询跟随在 =、!=、 <、 <=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。 (0 行受影响)
请问我该怎么改才好. ----------------如果是查其下层,直接like即可.declare @classid varchar(30)
set @classid = '014'--包含本数据
select * from tb where classid like @classid + '%' --不包含本数据
select * from tb where classid like @classid + '%' and classid <> @classid
case
when len(
(select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'))>=3
then substring ((select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'),1,3)
else null end from pubitemclass
case
when len(
(select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'))>=3
then substring ((select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'),1,3)
else null end from pubitemclass
财务要求是在我之前的报表中加入物料分类.要在表中能看到某个物料的所有分类信息.
我是这样想的,在字段中加入7列,当她选某个物料后,自动判断他在那个分类下.
class1 class2 class3 ...... class7
产品(014) 代理业务(01402) 食品(0140201) ......
已此类推
declare @classid varchar(30) select classid =
case
when len(
(select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'))>=3
then substring ((select distinct (y.classid)
from pubclassitems x join pubitemclass y
on y.classid = x.classid
and y.classid like '014%'),1,3)
else null end from pubitemclass