sql - 存储未知数量属性的更好方法

Question

目前使用 Postgres 9.3我有一个表 Person(Id, FName, Lname, Address1, Adress2, phone1, phone1,....)

我可以做 Person(id, FName, Lname)然后是 Address(PersonID, AddressName, Address) 和 Pone(PersonID, PhoneName, Number)

但是当我需要添加新属性时，比如电子邮件，我需要更改架构并添加 Email(PersonID, EmailName, Address)

我想做的是Person(ID, AtrbLbl, AtribVal)

1, Fname, Ron
1, Lname, H
1, HomeEmal, rh@home.ca
1, HomeAddress, 123 st edmonton
2, LName, Smith
3, Fname, Bob
2, Fname, Sam
3, Lnaem, Marly
3, HomeAdress, Heven
2, HomeAddress, abc St.
1, FavorateColor, red
2, FavorateColor, red
3, FavorateColor, red
1, FavorateIcream, Chocolate 
2, FavorateIcream, Vanila
3, FavorateIcream, Mint
4, FName, tom
4, FavorateColor, blue

我，Ron H，由所有 id = 1 组成，如果说我找到了一份工作，你可以添加 1, WorkEmail, rh@Work.ca

所以如果我想要所有 FavorateColor 都是红色的人的属性

Select * from person where id in (Select ID from person where  AtrbLbl = FavorateColor and AtribVal = red)`

我的问题是搜索多个属性。在 sudo sql 中我想要的是

Select * from person where id in (Select id from person where (AtrbLbl = FavorateColor and AtribVal = red) AND (AtrbLbl = Fname and AtribVal = Ron)

显然那是行不通的。

我想做的是

insert into temptbl
Select Count(id) cnt, ID from person where (AtrbLbl = FavorateColor and AtribVal = red) OR (AtrbLbl = Fname and AtribVal = Ron) 

Select * From person where id in (select id from temtbl where cnt = 2)  order by id
where 2 is the number of searched attributes.

所以如果我想要那些喜欢红色、巧克力和 FName Ron 的人

insert into temptbl
Select Count(id) cnt, ID from person where (AtrbLbl = FavorateColor and AtribVal = red) OR (AtrbLbl = Fname and AtribVal = Ron) OR (AtrbLbl = FavorateIcream and AtribVal = Chocolate) 

Select * From person where id in (select id from temtbl where cnt = 3)  order by id

在我看来，我应该能够通过将 where 的一部分的结果与另一部分的结果相结合来在 on 语句中做到这一点。

谁能想出一条语句来做到这一点？或者更优雅的方法？

Answer 1

经典 SQL 更适合静态模式。

不过，您可以编写单个查询。

例如，您要查找所有具有以下特征的人:

FavorateColor = red
AND
Fname = Ron
AND
FavorateIcream = Chocolate

对每个属性执行三个单独的查询，并仅返回与所有三个过滤器匹配的 ID:

SELECT *
FROM PersonDetails
WHERE PersonID IN
    (
        SELECT ID
        FROM person
        WHERE AtrbLbl = 'FavorateColor' AND AtribVal = 'red'

        INTERSECT

        SELECT ID
        FROM person
        WHERE AtrbLbl = 'Fname' AND AtribVal = 'Ron'

        INTERSECT

        SELECT ID
        FROM person
        WHERE AtrbLbl = 'FavorateIcream' AND AtribVal = 'Chocolate'
    )

所以，这是可能的，但就我个人而言，不会这样做。正如您在问题开头所描述的那样，我会为人员、地址、电话、电子邮件设置单独的表格。