如何对给定的一组参数只获得一个acl_groups_to_resources.bitmask？

Question

我的DB中有以下表格：

CREATE TABLE `restricted_components` (
  `component_id` int(11) NOT NULL,
  `resources_id` int(11) NOT NULL,
  `component_dom_id` varchar(50) NOT NULL,
  `component_dom_data_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`component_id`),
  UNIQUE KEY `component_id_UNIQUE` (`component_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `resources` (
  `resources_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `resources_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `menu_href` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `menu_text` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`resources_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `acl_groups_to_resources` (
  `groups_id` int(10) unsigned NOT NULL,
  `resources_id` int(10) unsigned NOT NULL,
  `bitmask` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`groups_id`,`resources_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `groups` (
  `groups_id` int(11) NOT NULL AUTO_INCREMENT,
  `groups_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`groups_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `users_to_groups` (
  `users_id` int(11) NOT NULL,
  `groups_id` int(11) NOT NULL,
  PRIMARY KEY (`users_id`,`groups_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `users` (
  `users_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_users_id` int(11) DEFAULT '0',
  `firstname` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`users_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我需要获得唯一的acl_groups_to_resources.bitmask值，其中包含restricted_components.component_dom_id、resources.menu_href、users.users_id、restricted_components.component_dom_data_name。

来自这个职位并对其进行了一些调整，我最终得到了以下SQL：

SELECT
  `rc`.`component_id`,
  `acl_gr`.`resources_id`,
  `acl_gr`.`bitmask`
FROM `restricted_components` AS `rc`
  LEFT JOIN `resources` AS `rs` ON rc.resources_id = rs.resources_id
  INNER JOIN `acl_groups_to_resources` AS `acl_gr` ON rs.resources_id = acl_gr.resources_id
  INNER JOIN `groups` AS `gr` ON acl_gr.groups_id = gr.groups_id
  INNER JOIN `users_to_groups` AS `usr_gr` ON gr.groups_id = usr_gr.groups_id
  INNER JOIN `users` AS `usr` ON usr_gr.users_id = usr.users_id
WHERE (rc.component_dom_id = 'add_component_restriction')
  AND (rs.menu_href = 'adminconsole')
  AND (usr.users_id = 976)
  OR  (rc.component_dom_data_name = 'btn_add_restriction')

上面的查询工作正常，但我得到了两个结果，而不是一个：

component_id | resources_id | bitmask
--------------------------------------
1            | 129          | 1
1            | 129          | 15

正确的输出将是包含给定参数的bitmask值的唯一行。查询有问题，但我不知道它是什么，所以我需要更多的关注。

这是每个表上的当前数据(可能问题在数据上)：

restricted_components
=====================

component_id | resources_id | component_dom_id         |  component_dom_data_name
----------------------------------------------------------------------------------
1            | 129          | add_component_restriction  
2            | 129          | edit_component_restriction
3            | 129          | delete_component_restriction

resources
=====================

resources_id | resources_name | menu_href    | menu_text
-------------------------------------------------------------
129          | Add            | adminconsole | Admin Console

acl_groups_to_resources
=====================

groups_id | resources_id | bitmask | created_by_users_id
--------------------------------------------------------
1         | 129          | 1       | 976
2         | 129          | 15      | 976
3         | 129          | 0       | 976

groups
=====================

groups_id | groups_name
---------------------------
1         | Administrators
2         | Restricted Admin
3         | Others

users_to_groups
======================

users_id | groups_id
---------------------------
976      | 1
976      | 2

users
=====================

users_id | parent_users_id | firstname | email
-------------------------------------------------------------
976      | 0               | Dev       | userdev@email.comdev

有谁能帮我个忙吗？(我使用的是MySQL 5.5)

注意:如果需要，可以随意重写SQL以获得所需的结果。

Answer 1

JOINs倾向于获得越来越多的行。

您可能需要组合'dup‘行，并使用BIT_OR()将其缩回一行：

SELECT   `rc`.`component_id`,
         `acl_gr`.`resources_id`,
         BIT_OR(`acl_gr`.`bitmask`)  AS bitmask
    FROM 
         ...  -- as you have now
    GROUP BY component_id, resources_id