mysql - 在决策表 SQL 中实现规则补集

Question

我有一个查询，需要使用决策表根据多个条件做出决策。

假设这里我需要知道一个结果(是或否)，具体取决于一个人具有的属性，如表中所示。

DROP    TABLE IF EXISTS `tbl_decision`; 
CREATE TABLE `tbl_decision` (
  id_rule       INT NOT NULL primary key AUTO_INCREMENT,
  rule_name     VARCHAR(25) NOT NULL,
  minVal        INT NOT NULL,
  maxVal        INT NOT NULL,    
  decision      CHAR(1) NOT NULL,
  CONSTRAINT `uc_decision` UNIQUE (`id_rule`)           
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Insert into `tbl_decision` VALUES
(1, 'WEIGHT'      , 60  ,100 ,'Y'),
(2, 'HEIGHT'      , 0   ,170 ,'Y'),
(3, 'Special rate', 10  ,30  ,'Y'),
(4, 'Consumption' , 0   ,3300,'N');

这是我要比较的表格:

DROP    TABLE IF EXISTS `tbl_guy`; 
CREATE TABLE `tbl_guy` (
  id_guy    INT NOT NULL primary key AUTO_INCREMENT,
  guy_name     VARCHAR(25)  NOT NULL,
  weight        INT NOT NULL,
  height        INT NOT NULL,    
  rate          INT NOT NULL,    
  consumption   INT NOT NULL,    
  CONSTRAINT `uc_guy` UNIQUE (`id_guy`)           
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


Insert into `tbl_guy` VALUES
(1, 'John'      , 200,220,10,1000),
(2, 'Mary'      , 100,160,11,5100),
(3, 'Diana'     , 80 ,155,19,4000),
(4, 'Ruthanne'  , 82 ,165, 5,2000);

该决定由以下查询确定。

SELECT *  FROM 
(
 SELECT id_guy,decision,
CASE WHEN rule_name = 'WEIGHT'       THEN tbl_guy.weight
     WHEN rule_name = 'HEIGHT'       THEN tbl_guy.height
     WHEN rule_name = 'Special rate' THEN tbl_guy.rate
     WHEN rule_name = 'Consumption'  THEN tbl_guy.consumption
END AS testQnty, rule_name, minVal, maxVal
FROM tbl_guy CROSS JOIN tbl_decision
) t 
WHERE minVal <= testQnty AND testQnty < maxVal 
ORDER BY id_guy;

我使用最小值和最大值来知道决策是否为"is"，但是我不知道如何定义相反的规则。例如我定义:

(3, '特价', 10 ,30 ,'Y')

如果家伙的场率值在 10 到 30 之间，则为"is"

但是对于现场消耗，如果它在 0 到 3300 之间，则判定为“否”

(4, '消耗', 0 ,3300,'N');

我知道你总是可以使用补语制定相反的规则，所以如果0 <= x <= 3300将变成x < 0 AND x > 3300但如何在查询时启用它？

Here is my fiddle

other

Answer 1

你的可变决策似乎体现了两个概念。一是规则的方向(是“之间”还是“不在之间”)，二是规则是否通过。为了澄清事情，下面保留所有规则以及一个新变量来确定规则是否通过:

SELECT t.*,
       (case when decision = 'Y' and minVal <= testQnty AND testQnty < maxVal
             then 'Passed'
             when decision = 'N' and not (minVal <= testQnty AND testQnty < maxVal)
             then 'Passed'
             else 'Not Passed'
        end) as RuleResult
FROM (SELECT id_guy, decision,
             (CASE WHEN rule_name = 'WEIGHT'       THEN tbl_guy.weight
                   WHEN rule_name = 'HEIGHT'       THEN tbl_guy.height
                   WHEN rule_name = 'Special rate' THEN tbl_guy.rate
                   WHEN rule_name = 'Consumption'  THEN tbl_guy.consumption
              END) AS testQnty, rule_name, minVal, maxVal
      FROM tbl_guy CROSS JOIN tbl_decision
     ) t 
ORDER BY id_guy;

如果您只想要通过的规则:

SELECT t.*
FROM (SELECT id_guy, decision,
             (CASE WHEN rule_name = 'WEIGHT'       THEN tbl_guy.weight
                   WHEN rule_name = 'HEIGHT'       THEN tbl_guy.height
                   WHEN rule_name = 'Special rate' THEN tbl_guy.rate
                   WHEN rule_name = 'Consumption'  THEN tbl_guy.consumption
              END) AS testQnty, rule_name, minVal, maxVal
      FROM tbl_guy CROSS JOIN tbl_decision
     ) t
where (decision = 'Y' and minVal <= testQnty AND testQnty < maxVal) or
      (decision = 'N' and not (minVal <= testQnty AND testQnty < maxVal))
ORDER BY id_guy;