sql-server - 在 SQL 中更新事实表无法正常工作

Question

我使用 SQL Server 已经 4 个月了，我的大部分工作是为我的公司构建数据仓库。我们通常采用星型模式方法，但由于维度太多，我通常会运行 UPDATE 查询以稍后填充维度列。在重新测试我的查询时，我遇到了一个我绝对无法解释的奇怪错误，这让我非常害怕。我的数据有一个类型为 DateTime 的 Registered_on 列。我创建了一个名为 HOUR 的维度，其中包含 24 行，一天中的每个小时一行，一个 int 用于匹配。所以，第一行是午夜，有一列是 0。

当我运行

的查询时

SELECT HourDimension.Hour
    ,DATEPART(Hour, AdmissionLogs.Registered_on)
FROM FactTable
INNER JOIN AdmissionLogs ON AdmissionLogs.ID = FactTable.LogID
INNER JOIN HourDimension ON HourDimension.HourNumber = DATEPART(Hour, AdmissionLogs.Registered_on)

按照这张图片，一切都完美匹配。

所以我做了更新查询:

UPDATE FactTable
SET FactTable.IdfHourDimension = HourDimension.IdfHourDimension
FROM FactTable
INNER JOIN AdmissionLogs ON AdmissionLogs.ID = FactTable.LogID
INNER JOIN HourDimension ON HourDimension.HourNumber = DATEPART(Hour, AdmissionLogs.Registered_on)

并通过以下方式查看事实表的结果:

SELECT HourDimension.Hour
    ,HourDimension.HourNumber
    ,DatePart(Hour, AdmissionLogs.Registered_on)
FROM FactTable
INNER JOIN HourDimension ON HourDimension.IdfHourDimension = FactTable.IdfHourDimension
INNER JOIN AdmissionLogs ON AdmissionLogs.ID = FactTable.LogID

我明白了:

时间绝对不匹配!我不知道发生了什么，但我严重怀疑我所知道的一切。

Answer 1

The results are different because you are not asking the same SELECT.

In the original query in which "Everything Matches Perfectly" you have ...
INNER JOIN HourDimension
 ON HourDimension.HourNumber = DATEPART(Hour, AdmissionLogs.Registered_on)

In the later query, after the UPDATE you have ...
INNER JOIN HourDimension
 ON HourDimension.IdfHourDimension = FactTable.IdfHourDimension


It should read...
SELECT HourDimension.Hour
    ,HourDimension.HourNumber
    ,DatePart(Hour, AdmissionLogs.Registered_on)
FROM FactTable
INNER JOIN AdmissionLogs
 ON AdmissionLogs.ID = FactTable.LogID
INNER JOIN HourDimension
 ON HourDimension.HourNumber = FactTable.IdfHourDimension