MySql查询管理用户权限

Question

我实际上陷入了 MySql 查询:

我有两张 table :

访问

版本

版本表用于存储软件版本。我有几个可以有多个版本的软件。

来自同一“系列”(=相同软件)的版本具有共同的根值。 lvl 列描述了软件的版本:v1 是级别 0，v3 是级别 2 等等。

访问表描述了允许用户访问哪些版本。可以启用或禁用访问。

vstart_id和vend_id用于描述允许用户访问的第一个和最后一个版本(我们假设 vstart_id 和 vend_id 引用具有相同根的版本。)

最后一个约束:

vstart_id | vend_id    
--------------------
  NULL    |  NULL       The user can access ALL versions
   3      |  NULL       The user can access all versions from the version n°3
  NULL    |   7         The user cannot access version higher than the version n°7
   3      |   6         The user can access all versions from 3 (included) to 6 (included) 

我真的不知道如何执行回答以下问题的查询:

用户n°2可以访问哪些版本？

这是数据库结构和一些用于测试的数据

    CREATE TABLE IF NOT EXISTS `access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enabled` tinyint(1) NOT NULL,
  `root` int(11) NOT NULL,
  `vstart_id` int(11) DEFAULT NULL,
  `vend_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

INSERT INTO `access` (`id`, `enabled`, `root`, `vstart_id`, `vend_id`, `user_id`) VALUES
(1, 1, 1, 2, 3, 1),
(2, 1, 2, 5, 7, 1),
(3, 1, 2, 4, NULL, 2),
(4, 1, 1, NULL, 2, 2),
(5, 1, 2, NULL, 7, 3),
(7, 1, 1, NULL, NULL, 4);

CREATE TABLE IF NOT EXISTS `version` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `root` int(11) NOT NULL,
  `lvl` int(11) NOT NULL,
  `title` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `version` (`id`, `root`, `lvl`, `title`) VALUES
(1, 1, 0, 'A - V1'),
(2, 1, 1, 'A - V2'),
(3, 1, 2, 'A - V3'),
(4, 2, 0, 'B - V1'),
(5, 2, 1, 'B - V2'),
(6, 2, 2, 'B - V3'),
(7, 2, 3, 'B - V4'),
(8, 2, 4, 'B - V5'),
(9, 2, 5, 'B - V6');

提前致谢

Answer 1

假设一个用户每个 root 最多有一个访问记录，访问中的起始和结束 id 引用 version.id:

SELECT v.*
FROM `access` a
    INNER JOIN version v ON (v.root = a.root)
WHERE a.user_id = 2
    AND v.id >= IFNULL(a.vstart_id, 0)
    AND (v.id <= a.vend_id OR a.vend_id IS NULL)

根据链接的根记录中的最小和最大等级获取版本信息

SELECT v.*
FROM version v
INNER JOIN(SELECT MIN(v.lvl) AS MinLvl, MAX(v.lvl) as MaxLvl, v.root
    FROM `access` a
    INNER JOIN version v ON (v.root = a.root)
    WHERE a.user_id = 2
    GROUP BY root
) t1 ON (t1.root = v.root AND v.lvl >= t1.MinLvl AND v.lvl <= t1.MaxLvl)

http://sqlfiddle.com/#!2/09b3c/10

根据 vstart_id 和 vend_id 的 lvl 获取版本信息(注意 vstart_id 的 lvl 必须低于 vend_id 的 lvl):

SELECT v.*
FROM version v
  INNER JOIN (SELECT a.root, v.lvl as StartLvl, v2.lvl AS EndLvl
    FROM access a
      LEFT JOIN version v ON (v.id = a.vstart_id AND v.root = a.root)
      LEFT JOIN version v2 ON (v2.id = a.vend_id AND v2.root = a.root)
    WHERE a.user_id = 2
) t1 ON (t1.root = v.root 
          AND CASE WHEN StartLvl IS NULL THEN 1=1 ELSE v.lvl >= StartLvl END
          AND CASE WHEN EndLvl IS NULL THEN 1=1 ELSE v.lvl <= EndLvl END
)

http://sqlfiddle.com/#!2/09b3c/28