select p.Name, p.ProductID from dbo.product p with (nolock) join dbo.split( ( select replace(cast(p.relateditems as varchar(8000)), ' ', '') from dbo.product p join dbo.Orders_ShoppingCart s on p.ProductID = s.ProductID where s.ordernumber = 100014 ), ',') rp on p.productid = cast(rp.items as int)
兄弟,我运行了下,结果是报错Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 意思是不是说要是返回的值有多于一个结果的话不可以啊,那我这种想法是不是无法实现了?
select replace(cast(p.relateditems as varchar(8000)), ' ', '') from dbo.product p join dbo.Orders_ShoppingCart s on p.ProductID = s.ProductID where s.ordernumber = 100014 这个语句会反回几行记录?
dbo.Orders_ShoppingCart 这个里面有两个100014,应该是返回两行吧?
@relatedits 是最后一个值吧
你用变量的写法,因为会滚动赋给,所以是一个值。 而直接换成select会得到两行,所以直接换不行,加个 top 1
1. select @relatedits = replace(cast(p.relateditems as varchar(8000)), ' ', '') from dbo.product p with (nolock) join dbo.Orders_ShoppingCart s with (NOLOCK) on p.ProductID = s.ProductID where s.ordernumber = 100014 select p.Name, p.ProductID 2. from dbo.product p with (nolock) join dbo.split(@relatedits, ',') rp on p.productid = cast(rp.items as int) --------------------------------------------------------------------------------------------- 把1改一下不就行了。 select @relatedits = isnull(@relatedits+',','')+replace(cast(p.relateditems as varchar(8000)), ' ', '') from dbo.product p with (nolock) join dbo.Orders_ShoppingCart s with (NOLOCK) on p.ProductID = s.ProductID where s.ordernumber = 100014
p.Name,
p.ProductID
from dbo.product p with (nolock)
join dbo.split(
(
select replace(cast(p.relateditems as varchar(8000)), ' ', '')
from dbo.product p
join dbo.Orders_ShoppingCart s on p.ProductID = s.ProductID
where s.ordernumber = 100014
), ',') rp on p.productid = cast(rp.items as int)
兄弟,我运行了下,结果是报错Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 意思是不是说要是返回的值有多于一个结果的话不可以啊,那我这种想法是不是无法实现了?
也可以用apply如果你的@relatedits 是由多行拼出来的,那么写法稍麻烦一点,如果要一条语句完成,基本是转成xml再转为字串。
from dbo.product p
join dbo.Orders_ShoppingCart s on p.ProductID = s.ProductID
where s.ordernumber = 100014
这个语句会反回几行记录?
而直接换成select会得到两行,所以直接换不行,加个 top 1
from dbo.product p with (nolock)
join dbo.Orders_ShoppingCart s with (NOLOCK)
on p.ProductID = s.ProductID where s.ordernumber = 100014 select p.Name, p.ProductID
2. from dbo.product p with (nolock)
join dbo.split(@relatedits, ',') rp on p.productid = cast(rp.items as int)
---------------------------------------------------------------------------------------------
把1改一下不就行了。
select @relatedits = isnull(@relatedits+',','')+replace(cast(p.relateditems as varchar(8000)), ' ', '')
from dbo.product p with (nolock)
join dbo.Orders_ShoppingCart s with (NOLOCK)
on p.ProductID = s.ProductID where s.ordernumber = 100014
用top 1 就只返回第1个的值,而第二个就没有了,如何能让他把这些都显示出来呢?