如何从 SQL 中的属性表解析类别

发布于 2024-10-07 22:37:47 字数 1241 浏览 12 评论 0原文

举个例子，假设我有以下（简化的）表（称为 NumericValue）：

Age   Gender    Occupation      Location        Value
40    M         Accountant      Johannesburg    100
40    F         Accountant      Johannesburg    120
40    M         Engineer        NULL            110
40    F         Engineer        NULL            110

现在假设我有这个名为员工的表：

Employee Number  Age   Gender    Occupation      Location        
1000123          40    F         Engineer        Cape Town      
1000124          40    M         Accountant      Johannesburg

现在，我需要的是为这两名员工选择“值”字段。假设工程师永远不会在 NumericValue 表中拥有“位置”，因此我不能只进行简单的联接。相反，我将“NumericTable”重新格式化如下：

Table: "CategoryValue"
Category   Value
1          100
2          120
3          110
4          110

使用这样的“属性”表：（

Table: "CategoryProperty"
Category   FieldName   FieldValue
1          Age         40
1          Gender      M
1          Occupation  Accountant
1          Location    Johannesburg
.
.
4          Age         40
4          Gender      F
4          Occupation  Engineer

注意，类别 4 下没有“位置”条目，它指的是 40 岁的女工程师）

这对我来说是有意义的，因为我只有特定分类字段很重要的条目。但我该如何解决这个问题并提取特定员工的值字段呢？

谢谢
卡尔

原文

By way of an example, lets say I have the following (simplified) table (called NumericValue):

Age   Gender    Occupation      Location        Value
40    M         Accountant      Johannesburg    100
40    F         Accountant      Johannesburg    120
40    M         Engineer        NULL            110
40    F         Engineer        NULL            110

Now suppose I have this table called Employees:

Employee Number  Age   Gender    Occupation      Location        
1000123          40    F         Engineer        Cape Town      
1000124          40    M         Accountant      Johannesburg

Now, what I need is to select the "value" field for these two employees. And let's say that engineers will never ever have a "location" in the NumericValue table, so I can't just do a simple join. In stead, I reformat my "NumericTable" as follows:

Table: "CategoryValue"
Category   Value
1          100
2          120
3          110
4          110

With a "property" table like this:

Table: "CategoryProperty"
Category   FieldName   FieldValue
1          Age         40
1          Gender      M
1          Occupation  Accountant
1          Location    Johannesburg
.
.
4          Age         40
4          Gender      F
4          Occupation  Engineer

(note, no entry for "location" under category 4, which refers to the 40 year old female engineer)

Which makes sense to me, since I only have entries where a specific categorisation field is of importance. But how do I resolve this and extract the Value field for the specific employee?

Thanks
Karl

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

◇流星雨 2024-10-14 22:37:47

你为什么不做这样的事情呢？

Select  e.EmployeeNumber,
        e.Age,
        e.Gender,
        e.Occupation,
        e.Location,
        nv.Value
From Employees e
Join NumericValue nv on e.Age = nv.Age
                        And e.Gender = nv.Gender
                        And e.Occupation = nv.Occupation
                        And e.Location = IsNull(nv.Location, e.Location)

当 NumericValue Location 为 NULL 时，只需将其替换为 Employee Location 的值

Why don't you do something like this?

Select  e.EmployeeNumber,
        e.Age,
        e.Gender,
        e.Occupation,
        e.Location,
        nv.Value
From Employees e
Join NumericValue nv on e.Age = nv.Age
                        And e.Gender = nv.Gender
                        And e.Occupation = nv.Occupation
                        And e.Location = IsNull(nv.Location, e.Location)

Just replace the NumericValue Location with the value of the Employee Location when it is NULL

回复收藏 0 原文

~没有更多了~