如何从树列中选择 [英] How to Select from tree column

问题描述

CREATE TABLE RefType
(
id INT PRIMARY KEY NOT NULL,
name NVARCHAR(50) NOT NULL
)

CREATE TABLE Ref
(
id INT PRIMARY KEY NOT NULL,
name NVARCHAR(50) NOT NULL,
rt INT NOT NULL FOREIGN KEY REFERENCES RefType(id)
)

INSERT INTO RefType VALUES(1, 'Gender')
INSERT INTO RefType VALUES(2, 'Hair color')

INSERT INTO Ref VALUES(1, 'Male', 1)
INSERT INTO Ref VALUES(2, 'Female', 1)
INSERT INTO Ref VALUES(3, 'Black', 2)
INSERT INTO Ref VALUES(4, 'Brown', 2)

CREATE TABLE Person
(
name NVARCHAR(50),
gender INT FOREIGN KEY REFERENCES RefType(id),
hair INT FOREIGN KEY REFERENCES RefType(id)
)

INSERT INTO Person VALUES('John', 1, 2)

问题：我如何在此基础上进行查询。我想得的结果是：

名字：约翰

性别：男

发：布朗

Question: How i do query on this base. My want to result is:
name : John
gender: Male
hair: Brown

推荐答案

您的架构错误 - Person 表不应该引用表 RefType 但是表价。事实上，你没有链接到表参考表人。换句话说，您无法查询您要求的数据。

进行更改，您还需要将插入更改为 Person 表

Your schema is wrong - the Person Table should not be referencing table RefType but table Ref. As things stand you have no link to table Ref from table Person. In other words you cannot query for the data you are asking for.
Make that change and you will also need to change your insert into the Person table

INSERT INTO Person VALUES(''John'', 1, 4)

你的规范化也有点奇怪 - 表 Ref 中的两个不同的实体类型与另一个定义属性类型的表可能是一个标准化程度太远了。



但是，你可以通过这个sql查询获得你想要的结果（在进行上述更改之后）

Your normalisation is also a little strange - two different entity types in table Ref with another table defining the attribute type is probably a level of normalisation too far.

However, you can get the results you want (after making the changes above) with this sql query

select #Person.name, Gender.gender, Hair.hair
from #Person
left outer join
    (select #Ref.id, #Ref.name as gender from #Ref inner join #RefType on #Ref.rt=#RefType.id
        where #RefType.name = 'Gender') as Gender on #Person.gender=Gender.id
left outer join
    (select #Ref.id, #Ref.name as hair from #Ref inner join #RefType on #Ref.rt=#RefType.id
        where #RefType.name = 'Hair color') as Hair on #Person.hair=Hair.id

我希望您同意它看起来非常糟糕，但它会返回以下结果

I hope you agree that it looks pretty awful, but it returns the following results

name	gender	hair
John	Male	Brown

这篇关于如何从树列中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！