如何在SQL中实现高性能树视图 [英] How to implement high performance tree view in SQL

问题描述

亲爱的所有人，

我们有这样的表格(简化版):
1-表属性:

Dear All,

We have a tables like This(simplified version):
1- table Properties :

id       PropName      ParentId
1        Properties    null
2        color         1
3        Length        1
4        width         1
5        Area          1

表属性中的ParentId是Fk To Id

2-表格属性值:

ParentId is Fk To Id in Table Properties

2- Tabl Properties_value :

id       PropName      ParentId  ItemId
1        red             2          1
2        100             3          1

表属性中的ParentId是Fk到ID&TableItem中的ItemId是Fk到ID
3- TableItem:

ParentId is Fk To Id in Table Properties &ItemId is Fk To Id in TableItem
3- TableItem :

id       ItemName   ItemNo
1        Tv         100

我需要查询一个具有以下结果/格式且具有高性能的sql

I Need query a sql that gives the following result/format with high performance

Item_id   ItemName ItemNo   Color  Length        
 1           Tv     100     Red    100

我尝试了获取结果的枢轴方法，但查询变得非常复杂，并且存在很多性能问题，所以，我该怎么办才能获得此结果?您的想法是什么?

I Tried The pivot Method To Get This Result But The Query Became Very Complex And There Was Alot Of Performance Issues So, what can I do to Get This Result What is your ideas?

推荐答案

您的设计有缺陷.

Item表的属性应为列(颜色，长度等).

这样，您的查询将变得更加容易.

Your design is flawed.

The Item table should have the properties as columns (color, length etc).

This way your queries will be much easier.

这篇关于如何在SQL中实现高性能树视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！