代码之家 › 专栏 › 技术社区 › Tom

使用SQL跟踪/查询状态更改

tsql linq sql-server

Tom · 技术社区 · 17 年前

我得到了一个数据库设计,它存储关于一个组织的信息以及组织已经发生或将要发生的任何更改。由于几乎任何事情都可以改变一个组织,所以在一个名为“组织”的表中只有一个表只包含唯一的OrganizationId。对该组织的更改都具有生效日期,并遵循类似的设计模式,如以下位置更改:

Table: organization_locations

organization_id (int, not null) - Relates back to the Organizations.ID column.
location_id (int, not null) - Relates to Locations.ID
eff_date (datetime, not null) - The date this change becomes effective

Table: Locations

ID (int, pk, identity, not null) - ID of the location
Name (varchar(255), not null) - Name of the location
... Other miscellaneous columns that aren't important for this discussion ...

例如。组织可能只包含两行,分别保存ID的1和2。位置可以有3个位置 (id, name) :

1, Location1
2, Location2
3, Location3

organization_locations (organization_id, location_id, eff_date):

1, 1, 1/1/2000  <--- Organization 1 is starting at location 1
1, 2, 1/1/2010  <--- On 1/1/2010, organization 1 moves to location 2 (from location 1)
1, 3, 1/1/2011 <--- On 1/1/2011, organization 1 moves to location 3 (in this case from location 2)

我已经有了一个大型的、可能过于复杂的查询,用于指定日期并在给定时间返回组织状态,但我觉得可能有一种更简单的方法。然而,目前的问题是:

从这个模式中,我如何回答这样一个问题:“在给定的时间范围内,哪些组织将从位置1移动到另一个位置,哪些组织将从另一个位置移动到位置1:日期1到日期2?”

同样可以回答第一个问题的一个类似问题是:我如何查询每个组织的位置更改(简单),同时显示他们正在移动的前一个位置(硬)?是吗?

注意:包括linq标签,以防在linq中有一个简单的方法可以做到这一点。

1 回复 | 直到 17 年前

Quassnoi 17 年前

地点1:

SELECT  DISTINCT organization_id
FROM    organization_locations ol
WHERE   ol.eff_date BETWEEN @date1 AND @date2
        AND ol.location = 1

从位置1开始:

SELECT  DISTINCT organization_id
FROM    (
        SELECT organization_id,
               (
               SELECT  TOP 1 location_id
               FROM    organization_locations oln
               WHERE   oln.organization_id = ol.organization_id
                       AND oln.eff_date < ol.eff_date
               ORDER BY
                       organization_id DESC, eff_date DESC
               ) AS previous_location
        FROM   organization_locations ol
        WHERE  eff_date BETWEEN @date1 AND @date2
        ) olo
WHERE   previous_location = 1

显示上一个位置:

SELECT ol.*,
       (
       SELECT  TOP 1 location_id
       FROM    organization_locations oln
       WHERE   oln.organization_id = ol.organization_id
               AND oln.eff_date < ol.eff_date
               AND location_id = 1
       ORDER BY
               organization_id DESC, eff_date DESC
       ) AS previous_location
FROM   organization_locations ol

有一个 UNIQUE INDEX 在 (organization_id, eff_date) 对你有很大帮助。