根据子值填充父字段

I am wanting to create a computed column on a parent table that will contain an ID based on the child rows of the parent.

我想在父表上创建一个计算列,该列将包含基于父表的子行的ID。

Example Parent Table:

示例父表:

ID  Name                CalculatedClassification
1   Parent Item #1      (computed, should return 1)
2   Parent Item #2      (computed, should return 2)
3   Parent Item #3      (computed, should return -1 as there is a mixed result)

Example Child Table

示例子表

    ID  Name              ParentRow        ClassificationID
    1   Child Item #1     1                1
    2   Child Item #2     1                1
    3   Child Item #3     1                1
    4   Child Item #4     2                2
    5   Child Item #5     2                2
    6   Child Item #6     3                1
    7   Child Item #7     3                0
    8   Child Item #8     3                1    

There is a many to many table joining the two but I have left that out for simplicity. Also, the ClassificationID is a foreign key to a classification id table that can grow over time.

有很多桌子连接这两个,但为了简单起见,我把它留了下来。此外,ClassificationID是分类ID表的外键,可以随时间增长。

Any ideas on how I can do this in a function or other SQL server construct?

关于如何在函数或其他SQL服务器构造中执行此操作的任何想法?

Thanks!!

1 个解决方案

#1

I wouldn't recommend creating a computed column when referencing other tables.

我不建议在引用其他表时创建计算列。

There are a couple of ways to do this though. One is to use the min and max aggregates with a case statement to determine the value:

有几种方法可以做到这一点。一种是使用带有case语句的min和max聚合来确定值:

select p.id, 
       p.name, 
       case 
            when min(c.classificationid) = max(c.classificationid) 
            then max(c.classificationid) 
            else -1
        end CalculatedClassification
from parent p 
    join child c on p.id = c.parentrow
group by p.id, p.name

• SQL Fiddle Demo

SQL小提琴演示