MSSQL 通过分类树,任意选择分类,查询分类下的所有产品.求高效SQL语句

Question

如下图,
要通过左侧的分类列表 任意勾选 某级别分类(可能分类下全选了子分类,也可能选了部分)查询出分类下的所有商品.
分类表
vc_code(分类编号), vc_Pcode(分类父级),vc_name(分类名),int_depth(层级)
01 -1 烟 1
011 01 本地烟 2
0111 011 10元一下 3
如上 分类为三级分类 大类为两位编号,中类1位,小类1位
小类编号=(中类编号+1位流水号)
中类编号=(大类编号+1位流水号)不明白的参照分类表示例数据.
产品表
id,ccode,name
1,0111,中南海
产品表的所有分类编号对照的都是小类号,因此其实可以通过产品编号截取后对照上任意层级的所属分类.现在用户任意勾选左侧分类结构,查询对应产品信息.我采用以下方法.
遍历树上已经勾选的任意层级分类 每次都拼接 select语句 无限union all 如果某分类完全勾选则不再遍历他的子分类(代表分类下所有子分类已经选取)
select * from product where ccode like '(所选的分类编号)%'
union all....
重复遍历select.....
我总觉得这个效率好像非常低. 每次select 都需要查询整个商品表,数10W条数据.如果用户勾选分类多了,会很慢.而且,除了分类外还有很多其他查询条件要附加.

希望有大牛给个高效率的查询语句,最好能兼顾其他过滤条件的效率...

Answer 1

首先LZ这么复杂的问题，就给这点分，少说也给个几万财富，或是支付点咨询费。O(∩_∩)O

=========================================================================

首先你这个就10万单品，不是太多。

使用中间表聚集的方式就可以了，但是又要兼顾实时性所以我就用索引视图了，如果数据库版本低可以维护一个表，其实维护表可以用外键。更好一些。

也就是说先聚合管理内码，然后是商品编号。这个顺序很重要。甚至影响你的效能。

本人的表结构，管理内码我这个都是 2+2+2 的格式，你的是 2+1+1 的格式。

创建索引视图：

看好上边的顺序，是管理分类在前，商品编号在后。

好了。

构建SQL

with PNoSec(PNO) as
(
  select '100409' union all
  select '040205' union all
  select '010107'
)
select 
 P.*
from PNoSec A
inner join dbo.VPNoProRel B on(A.PNO=B.PNO)
inner join dbo.Product P on(B.PID=P.ID)
where P.Name like '欧莱雅%'

with PNoSec(PNO) as
(
  select '100409%' union all
  select '040205%' union all
  select '010107%'
)
select 
 P.*
from PNoSec A
inner join dbo.VPNoProRel B on(B.PNO like A.PNO)
inner join dbo.Product P on(B.PID=P.ID)
where P.Name like '欧莱雅%'

你的程序就是构建SQL片断（管理分类不能有重复）

如果数据库版本低，可以使用临时表的方式。这个你自己拿捏吧。

以上方案小巧灵活，应对几十万单品不在话下。

本回答被提问者采纳