select top 10 id from [Product] where 1=1 and language=0 and lockid<>1 and typeid in(
with b as(
select id,name,parentid from ProductSort
where id=12
union all select x.id,x.name,x.parentid from ProductSort x,b
where x.parentid=b.id
)select id from b
) order by sortid asc , id desc---这种执行就有错
with b as(select id,name,parentid from ProductSort
where id=12
union all select x.id,x.name,x.parentid from ProductSort x,b
where x.parentid=b.id
)select id from b
---上面单独执行就没错select top 10 id from [Product] where 1=1 and language=0 and lockid<>1 and
typeid in(
12
) order by sortid asc , id desc
---上面单独执行也没错为上面合到一起就会出错
with b as(
select id,name,parentid from ProductSort
where id=12
union all select x.id,x.name,x.parentid from ProductSort x,b
where x.parentid=b.id
)select id from b
) order by sortid asc , id desc---这种执行就有错
with b as(select id,name,parentid from ProductSort
where id=12
union all select x.id,x.name,x.parentid from ProductSort x,b
where x.parentid=b.id
)select id from b
---上面单独执行就没错select top 10 id from [Product] where 1=1 and language=0 and lockid<>1 and
typeid in(
12
) order by sortid asc , id desc
---上面单独执行也没错为上面合到一起就会出错
select id,name,parentid from ProductSort
where id=12
union all select x.id,x.name,x.parentid from ProductSort x,b
where x.parentid=b.id
)
select top 10 id from [Product] where 1=1 and language=0 and lockid<>1 and typeid in(
select id from b
) order by sortid asc , id desc
from [Product]
where 1=1 and language=0 and lockid<>1 and typeid in
(select id from (
select id,name,parentid from ProductSort
where id=12
union all select x.id,x.name,x.parentid from ProductSort x,b
where x.parentid=b.id
)t -->要有这个别名t
)
order by sortid asc , id desc你的where 里面不能用with,还有两条语句连接查询后要有别名
我这边其实是一个获取,所有类别下面所有子类别N级子类别id