Sql server关系使用之间的值

Question

我有一个包含2个表(sql server 2008)的数据库:

LenderCommission

int ID

<

委员会em>十进制

CommissionTier

int ID

MinCommission小数点

MaxCommission小数点

我想在两个表之间创建一个关系。最明显的方法是将CommissionTierId添加到LenderCommission表中，不过这是固定的。如果一个级别的佣金结构改变了，那么所有的贷方佣金都需要重估。

我不是专家，但我猜这是不可能创建一个关系，其中Commission是MinCommission和MaxCommission之间。然而，有没有什么方法可以让这种关系变得更流畅呢?

Answer 1

我不太确定您想要做什么-我已经将您可能希望的一种工作方式的演示放在一起。我们实际上创建了一个包含更多列的表，然后用几个触发器将其隐藏在视图后面，以确保一切正常运行。

表:

create table dbo.CommissionTier (
    ID int not null,
    MinCommission decimal not null,
    MaxCommission decimal not null,
    constraint PK_CommissionTier PRIMARY KEY (ID),
    constraint UQ_CommissionTier_RangeValidation UNIQUE (ID,MinCommission,MaxCommission) --This is to support our FK below
)
go
create table dbo._LenderCommission (
    ID int not null,
    Commission decimal not null,
    CommissionTier int not null,
    _MinCommission decimal not null,
    _MaxCommission decimal not null,
    constraint PK__LenderCommission PRIMARY KEY (ID),
    constraint CK__LenderCommission_InRange CHECK (
        _MinCommission < Commission and --Inclusive or exclusive bound? <= or <
        Commission < _MaxCommission --Ditto?
    ),
    constraint FK__LenderCommission_Tier
       FOREIGN KEY (CommissionTier)
       references dbo.CommissionTier (ID), --This FK isn't actually needed
    constraint FK__LenderCommission_Tier_Range
       FOREIGN KEY (CommissionTier,_MinCommission,_MaxCommission)
       references dbo.CommissionTier(ID,MinCommission,MaxCommission) on update cascade
)

视图(从现在开始，您将将其视为原始表):

create view dbo.LenderCommission
with schemabinding
as
    select
        ID,
        Commission,
        CommissionTier
    from
        dbo._LenderCommission
go

您可能不需要这个，但我倾向于在视图伪装成表时总是添加索引，并赋予它与原始主键相同的名称:

create unique clustered index PK_LenderCommission on dbo.LenderCommission(ID)

现在有几个触发器做一些必要的房屋清洁:

create trigger dbo.T_LenderCommission_I
on dbo.LenderCommission
instead of insert
as
    set nocount on
    insert into dbo._LenderCommission (ID,Commission,CommissionTier,_MinCommission,_MaxCommission)
    select i.ID,i.Commission,i.CommissionTier,ct.MinCommission,ct.MaxCommission
    from
        inserted i
            inner join
        dbo.CommissionTier ct
            on
                i.CommissionTier = ct.ID
go
create trigger dbo.T_LenderCommission_U
on dbo.LenderCommission
instead of update
as
    set nocount on
    update lc
    set
        Commission = i.Commission,
        CommissionTier = i.CommissionTier,
        _MinCommission = ct.MinCommission,
        _MaxCommission = ct.MaxCommission
    from
        dbo._LenderCommission lc
            inner join
        inserted i
            on
                lc.ID = i.ID
            inner join
        dbo.CommissionTier ct
            on
                i.CommissionTier = ct.ID

现在我们填充它们:

insert into CommissionTier(ID,MinCommission,MaxCommission) values
(1,0,1000),
(2,1000,2000),
(3,2000,3000)
go
insert into LenderCommission(ID,Commission,CommissionTier) values
(1,500,1),
(2,750,1),
(3,1500,2)

现在对于一些错误:

--This produces an error:
insert into LenderCommission(ID,Commission,CommissionTier) values
(4,500,2)
go
--This produces an error:
update LenderCommission set Commission = 2500 where ID=2
go
--This *doesn't* produce an error because the new value matches the new tier
update LenderCommission set Commission = 2500,CommissionTier = 3 where ID=2
go
--This is okay, we're adjusting one of the tiers
update CommissionTier set MaxCommission = 1550 where ID = 2
go
--This produces an error, because the cascading foreign key updated the values:
update LenderCommission set Commission = 1600 where ID = 3

---

作为上面的替代方法，您可以创建一个计算列，它执行相同的检查并采用合适的值，而不是使用CK__LenderCommission_InRange:

CommissionOutOfRange AS
  CASE WHEN Commission < MinCommission THEN -1
  CASE WHEN Commission > MaxCommission THEN 1
  ELSE 0 END

现在您可以快速搜索表中超出范围的值(但没有什么可以阻止它们被创建

Answer 2

我不太明白你的目的。这两个表之间似乎已经存在一种逻辑关系;据推测，佣金可以落在最小值和最大值之间。您可以这样执行连接:

select <whatever>
from LenderCommission lc join
     CommissionTier ct
     on lc.commission between ct.MinCommission and ct.MaxCommission;

你想干什么?

一种可能性是，您希望通过将此连接转换为对等连接而不是"之间"连接来提高此连接的效率。拥有本我肯定会做到这一点。如果CommissionTier表只有几十行，那么这种效率水平可能不重要。

也许你试图在表之间强制一个约束，所以新的佣金只能添加一个佣金层。如果是，你可以通过触发器来检查。

真正将这种关系转换为单个外键将是复杂的。您需要考虑以下事项:

• 防止佣金层级重叠。
• 当委托层发生变化时修改外键关系(即修改eaderCommission中的数据)。
• 维护佣金级别的历史数据，如果您需要查看不同时间点的"它是什么"。

这些都是可能的，但可能比您想象的要复杂得多。