SQL Server自引用查询

Question

请考虑以下事项:

CREATE DATABASE TEST
USE TEST
CREATE TABLE Student
    (
      StudentID INT IDENTITY
                    PRIMARY KEY ,
      FirstName NVARCHAR(50) ,
      LastName NVARCHAR(50)
    )
CREATE TABLE StudentComponent
    (
      StudentComponentID INT IDENTITY
                             PRIMARY KEY ,
      StudentID INT FOREIGN KEY REFERENCES dbo.Student(StudentID) ,
      ComponentName NVARCHAR(50) ,
      ComponentRef NVARCHAR(50) ,
      ComponentType NCHAR(2)
    )
CREATE TABLE Component
    (
      ComponentID INT IDENTITY
                      PRIMARY KEY ,
      StudentComponentID INT FOREIGN KEY REFERENCES dbo.StudentComponent(StudentComponentID) ,
      ComponentName NVARCHAR(50) ,
      ComponentRef NVARCHAR(50) ,
      ComponentType NCHAR(2)
    )

我有3张表

2. StudentComponent
<
3. 组件/gh>

分裂成维度

CREATE TABLE DimClass
    (ClassDwKey INT,
    ClassName NVARCHAR(50),
    ClassRef NVARCHAR(50))
    CREATE TABLE DimCollege
    (CollegeDwKey INT,
    CollegeName NVARCHAR(50),
    CollegeRef NVARCHAR(50))
    CREATE TABLE DimSubject
    (SubjectDwKey INT,
    SubjectName NVARCHAR(50),
    SubjectRef NVARCHAR(50))
    CREATE TABLE DimStudent
    (StudentDwKey INT,
    StudentName NVARCHAR(50))
    INSERT INTO 
    CREATE TABLE FactAcademicEvent
    (StudentDwKey int, ClassDwKey int, CollegeDwKey int, SubjectDwKey INT)

它们之间的关系顺序相同(Student -> StudentComponent -> Component)。我将组件拆分为它们自己的维度，DimClass, DimCollege, DimSubject(按组件类型字段拆分，= 'CL'表示类，'SU'表示主题，'CO'表示学院等)。我试图加载一个事实表，从我的维度中获取代理键，并生成一个包含以下内容的行

StudentDwKey(来自dimstudent)， ClassDwKey, CollegeDwKey, SubjectDwKey。维度和组件表ComponentKey之间有一个链接，组件表通过studentcomponent表链接到student。

StudentComponent和Component基本上是一样的，除了StudentComponent引用了student，所以有更多的行。

任何想法?

Answer 1

编辑:

根据OP的编辑和评论完成修改。

SELECT
  s.StudentID,
  MAX(dim_s.StudentDwKey )   AS StudentDwKey,
  MAX(dim_cl.ClassDwKey  )   AS ClassDwKey,
  MAX(dim_su.SubjectDwKey)   AS SubjectDwKey,
  MAX(dim_co.CollegeDwKey)   AS CollegeDwKey
FROM
  Student            AS s
LEFT JOIN
  StudentComponent   AS sc
    ON  sc.StudentID    = s.StudentID
LEFT JOIN
  dimStudent         AS dim_s
    ON  dim_s.StudentName = s.StudentName  -- or whatever is a reliable join
LEFT JOIN
  dimClass           AS dim_cl
    ON  dim_cl.ClassRef   = sc.ComponentRef
    AND sc.ComponentType  = 'CL'
LEFT JOIN
  dimSubject         AS dim_su
    ON  dim_su.SubjectRef = sc.ComponentRef
    AND su.ComponentType  = 'SU'
LEFT JOIN
  dimCollege         AS dim_co
    ON  dim_co.CollegeRef = sc.ComponentRef
    AND sc.ComponentType  = 'CO'
GROUP BY
  s.StudentID

Answer 2

将学生详细信息保存在student表中(您已经这样做了)，将组件详细信息保存在component表中(您还没有这样做)，并使用StudentComponent表将它们链接在一起。像这样:

CREATE DATABASE TEST
USE TEST
CREATE TABLE Student
    (
      StudentID INT IDENTITY
                    PRIMARY KEY ,
      FirstName NVARCHAR(50) ,
      LastName NVARCHAR(50)
    )
CREATE TABLE StudentComponent
    (
      StudentComponentID INT IDENTITY
                             PRIMARY KEY ,
      StudentID INT FOREIGN KEY REFERENCES dbo.Student(StudentID) ,
      ComponentID INT FOREIGN KEY REFERENCES dbo.Component(ComponentID),
    )
CREATE TABLE Component
    (
      ComponentID INT IDENTITY
                      PRIMARY KEY ,
      ComponentName NVARCHAR(50) ,
      ComponentRef NVARCHAR(50) ,
      ComponentType NCHAR(2)
    )