SQL Server基于多条件的字段计算

Question

这是我的场景：

我有一个包含以下字段的人员表。

create table Person(PersonID int primary key identity(1,1),
                     Age int,
                     height decimal(4,2),
                     weight decimal(6,2)
                    );

 insert into Person(Age,height,weight) values (60,6.2,169); -- 1
 insert into Person(Age,height,weight) values (15,5.1,100); -- 2
 insert into Person(Age,height,weight) values (10,4.5,50); -- 3

我需要做的是，

if the person Age >= 18 and height >= 6 then calculationValue = 20
if the person Age >= 18 and height < 6 then calculationValue = 15
if the person Age < 18 and weight >= 60 then calculationValue = 10
if the person Age < 18 and weight < 60 then calculationValue = 5

根据这些条件，我需要找到计算值并做一些数学运算。

我尝试制作一个灵活的模型，这样将来会更容易添加更多条件，并且可以轻松更改常量值（如 18、6、60 等）

我创建了几个表，如下所示：

create table condTable(condTableID int primary key identity(1,1),
                        condCol varchar(20),
                        startValue int,
                        endValue int
                      );

  insert into condTable(condCol,startValue,endValue) values ('Age',18,999) -- 1
  insert into condTable(condCol,startValue,endValue) values ('Height',6,99) -- 2
  insert into condTable(condCol,startValue,endValue) values ('Height',0,5.99) -- 3
  insert into condTable(condCol,startValue,endValue) values ('Age',0,17) -- 4
  insert into condTable(condCol,startValue,endValue) values ('Weight',60,999) -- 5
  insert into condTable(condCol,startValue,endValue) values ('Weight',0,59) -- 6

我加入两个条件来使其下表中的一个按要求给出。（即，如果年龄> = 18且身高> = 6，则计算值= 20。等等）

create table CondJoin(CondJoin int,condTableID int,CalculationValue int)


insert into CondJoin values (1,1,20)
insert into CondJoin values (1,2,20)
insert into CondJoin values (2,1,15)
insert into CondJoin values (2,3,15)
insert into CondJoin values (3,4,10)
insert into CondJoin values (3,5,10)
insert into CondJoin values (4,4,5)
insert into CondJoin values (4,6,5)

我认为该模型将提供将来添加更多条件的灵活性。但我在SQL Server 2005中实现它时遇到困难。任何人都可以编写一条在集合基础上处理的sql，并将Person表中的值与CondJoin表中的值进行比较，并提供相应的计算值。例如。对于 ID 1 的人，它应该查看 CondJoin 表并给出计算值 20，因为他的年龄大于 18，身高大于 6。

Answer 1

看起来您正在走向动态 sql 生成。

我认为也许你最好为每列设置一行，为范围设置截止值，如果为真则设置一个值......也许是这样的：

age_condition
-----------------
min_age
max_age
value

这是你可以填充然后查询而无需动态生成的东西。

Answer 2

下面的内容极其，但应该能够表达要点。它将数据规范化并走向半面向对象（属性/值/属性值）结构。我将让您自行加强引用完整性，但以下内容是灵活的，并将返回您想要的结果：

CREATE TABLE Person (
    PersonID INT PRIMARY KEY IDENTITY(1,1)
    ,Name NVARCHAR(255)
    );

GO

CREATE TABLE PersonAttribute (
    PersonID INT
    ,CondAttributeID INT
    ,Value NVARCHAR(255)
    );

GO

CREATE TABLE CondAttribute (
AttributeID INT PRIMARY KEY IDENTITY(1,1)
,Attribute NVARCHAR(255));

GO

CREATE TABLE CondTable (
    CondTableID INT PRIMARY KEY IDENTITY(1,1)
    ,CondAttributeID INT
    ,StartValue MONEY
    ,EndValue MONEY
    );

GO

CREATE TABLE CalculationValues (
    CalculationID INT PRIMARY KEY IDENTITY(1,1)
    ,CalculationValue INT
    );

GO

CREATE TABLE CondCalculation (
    CondTableID INT
    ,CalculationID INT
    );


INSERT Person (Name)

VALUES ('Joe')
        ,('Bob')
        ,('Tom');


INSERT PersonAttribute (
    PersonID
    ,CondAttributeID
    ,Value
)

VALUES  (1, 1, '60')
        ,(1, 2, '6.2')
        ,(1, 3, '169')
        ,(2, 1, '15')
        ,(2, 2, '5.1')
        ,(2, 3, '100')
        ,(3, 1, '10')
        ,(3, 2, '4.5')
        ,(3, 3, '50');          

INSERT CondAttribute (Attribute)

VALUES ('Age')
        ,('height')
        ,('weight');

INSERT CondTable (
    CondAttributeID
    ,StartValue
    ,EndValue)

VALUES (1,18,999) --Age
        ,(2,6,99) --Height 
        ,(2,0,5.99) -- Height
        ,(1,0,17) -- Age
        ,(3,60,999) -- Weight
        ,(3,0,59); -- Weight



INSERT CalculationValues (CalculationValue)
VALUES (5)
        ,(10)
        ,(15)
        ,(20);  


INSERT CondCalculation (CondTableID, CalculationID)
VALUES (1,4)
       ,(2,4)
       ,(1,3)
       ,(3,3)
       ,(4,2)
       ,(5,2)
       ,(5,1)
       ,(6,1);

SELECT *
FROM Person AS p
JOIN PersonAttribute AS pa ON p.PersonID = pa.PersonID
JOIN CondAttribute AS ca ON pa.CondAttributeID = ca.AttributeID
JOIN CondTable AS ct ON ca.AttributeID = ct.CondAttributeID
    AND CONVERT(money,pa.Value) BETWEEN ct.StartValue AND ct.EndValue
JOIN CondCalculation AS cc ON cc.CondTableID = ct.CondTableID
JOIN CalculationValues AS c ON cc.CalculationID = c.CalculationID
WHERE p.PersonID = 1

Answer 3

以下解决方案使用 PIVOT（两次）将 CondJoin 和 condTable 的组合转换为图表，然后将图表连接到 Person 表以计算目标值。我相信，也可以使用一系列 CASE 表达式来代替。无论如何......

所有的表都已变成表变量，以便于测试。首先，DDL 和数据准备：

declare @Person table(PersonID int primary key identity(1,1),
                     Age int,
                     height decimal(4,2),
                     weight decimal(6,2)
                    );
insert into @Person(Age,height,weight) values (60,6.2,169); -- 1
insert into @Person(Age,height,weight) values (15,5.1,100); -- 2
insert into @Person(Age,height,weight) values (10,4.5,50); -- 3

declare @condTable table(condTableID int primary key identity(1,1),
                        condCol varchar(20),
                        startValue int,
                        endValue int
                      );
insert into @condTable(condCol,startValue,endValue) values ('Age',18,999) -- 1
insert into @condTable(condCol,startValue,endValue) values ('Height',6,99) -- 2
insert into @condTable(condCol,startValue,endValue) values ('Height',0,5.99) -- 3
insert into @condTable(condCol,startValue,endValue) values ('Age',0,17) -- 4
insert into @condTable(condCol,startValue,endValue) values ('Weight',60,999) -- 5
insert into @condTable(condCol,startValue,endValue) values ('Weight',0,59) -- 6
declare @CondJoin table(CondJoin int,condTableID int,CalculationValue int);
insert into @CondJoin values (1,1,20)
insert into @CondJoin values (1,2,20)
insert into @CondJoin values (2,1,15)
insert into @CondJoin values (2,3,15)
insert into @CondJoin values (3,4,10)
insert into @CondJoin values (3,5,10)
insert into @CondJoin values (4,4,5)
insert into @CondJoin values (4,6,5)

现在是查询：

;with startValues as (
  select
    CondJoin,
    Age,
    Height,
    Weight,
    CalculationValue
  from (
    select
      j.CondJoin,
      j.CalculationValue,
      t.condCol,
      t.startValue
    from @CondJoin j
      inner join @condTable t on j.condTableID = t.condTableID
  ) j
  pivot (
    max(startValue) for condCol in (Age, Height, Weight)
  ) p
),
endValues as (
  select
    CondJoin,
    Age,
    Height,
    Weight,
    CalculationValue
  from (
    select
      j.CondJoin,
      j.CalculationValue,
      t.condCol,
      t.endValue
    from @CondJoin j
      inner join @condTable t on j.condTableID = t.condTableID
  ) j
  pivot (
    max(endValue) for condCol in (Age, Height, Weight)
  ) p
),
combinedChart as (
select
  s.CondJoin,
  AgeFrom    = s.Age,
  AgeTo      = e.Age,
  HeightFrom = s.Height,
  HeightTo   = e.Height,
  WeightFrom = s.Weight,
  WeightTo   = e.Weight,
  s.CalculationValue
from startValues s
  inner join endValues e on s.CondJoin = e.CondJoin
)
select
  p.*,
  c.CalculationValue
from @Person p
  left join combinedChart c
    on (c.AgeFrom    is null or p.Age    between c.AgeFrom    and c.AgeTo)
   and (c.HeightFrom is null or p.Height between c.HeightFrom and c.HeightTo)
   and (c.WeightFrom is null or p.Weight between c.WeightFrom and c.WeightTo)