关于权限的高级查询

[仙人长]

问题：当moduleId相同时去掉同行（）而且aclState的结果 是相同数据的或运算结果（二进制的或）
例如： select * from acl
结果会是：
id, principalType,principalId,moduleId,aclState,extendsState
1 User 2 4 1 0
2 User 2 4 2 0
3 User 2 18 15 0

sql:?????
结果：(这里aclState的结果不是加是二进制的或)
id, principalType,principalId,moduleId,aclState,extendsState
1 User 2 4 3 0
3 User 2 18 15 0
附：看不清问题可以先看表结构 谢绝无意义的回贴，（菜鸟请自重）


表结构如下（一张表）：
create database if not exists crm default character set UTF8;
use crm;

create table ACL(
aclId int not null auto_increment,/**主键*/
roleId int , /**角色id不管它是什么（是否为外键）**/
userId int ,
moduleId int , /**问题点**/
aclState int ,
extendsState int default 0,
constraint PK_ACL_aclId primary key (aclId)
)engine=InnoDB default charset=UTF8 comment='访问控制列表表 ';

/**初始化**/
insert into ACL(principalType,principalId,moduleId,aclState,extendsState)
values('User',2,4,1,0);
insert into ACL(principalType,principalId,moduleId,aclState,extendsState)
values('User',2,4,2,0);
insert into ACL(principalType,principalId,moduleId,aclState,extendsState)
values('User',2,18,15,0);

[zuoxingyu]

select a.aclid,a.userid,a.moduleid,max(a.aclstate | b.aclstate ),a.extendsState
from acl a ,acl b where a.moduleid=b.moduleid 
group by moduleid;

你提供的表结构和插入语句不匹配。

[仙人长]

不好意思表是被我改过了的现在改成正确的：create table ACL(
aclId int not null auto_increment,/**主键*/
principalType varchar(10),
principalId int,
moduleId int , /**问题点**/
aclState int ,
extendsState int default 0,
constraint PK_ACL_aclId primary key (aclId)
)engine=InnoDB default charset=UTF8 comment='访问控制列表表 ';
您的回答好像没有经过测试：
结果是：
id, principalType,principalId,moduleId,aclState,extendsState
1 User 2 4 15 0
3 User 2 18 15 0
结果并不是
id, principalType,principalId,moduleId,aclState,extendsState
1 User 2 4 3 0
3 User 2 18 15 0

[zuoxingyu]

mysql> select a.aclid,a.userid,a.moduleid,max(a.aclstate | b.aclstate ),a.extend
sState
    -> from acl a ,acl b where a.moduleid=b.moduleid
    -> group by moduleid;
+-------+--------+----------+-------------------------------+--------------+
| aclid | userid | moduleid | max(a.aclstate | b.aclstate ) | extendsState |
+-------+--------+----------+-------------------------------+--------------+
|     1 |      2 |        4 |                             3 |            0 |
|     3 |      2 |       18 |                            15 |            0 |
+-------+--------+----------+-------------------------------+--------------+
2 rows in set (0.00 sec)

mysql>

[ACMAIN_CHM]

[Quote]附：看不清问题可以先看表结构 谢绝无意义的回贴，（菜鸟请自重）[/Quote]楼主对别人答题的要求还很高，结果自己出题时倒不先测试一下，提供错误的用例给别人。

对别要要求的同时，建议审视一下自己。

[ACMAIN_CHM]

mysql> select * from ACL;
+-------+---------------+-------------+----------+----------+--------------+
| aclId | principalType | principalId | moduleId | aclState | extendsState |
+-------+---------------+-------------+----------+----------+--------------+
|     1 | User          |           2 |        4 |        1 |            0 |
|     2 | User          |           2 |        4 |        2 |            0 |
|     3 | User          |           2 |       18 |       15 |            0 |
+-------+---------------+-------------+----------+----------+--------------+
3 rows in set (0.02 sec)

mysql> select aclId,principalType,
    ->  BIT_OR(principalId) as principalId,
    ->  moduleId,
    ->  BIT_OR(aclState) as aclState,
    ->  BIT_OR(extendsState) as extendsState
    -> from ACL
    -> group by moduleId;
+-------+---------------+-------------+----------+----------+--------------+
| aclId | principalType | principalId | moduleId | aclState | extendsState |
+-------+---------------+-------------+----------+----------+--------------+
|     1 | User          |           2 |        4 |        3 |            0 |
|     3 | User          |           2 |       18 |       15 |            0 |
+-------+---------------+-------------+----------+----------+--------------+
2 rows in set (0.00 sec)

mysql>

[仙人长]

[Quote=引用 3 楼 zuoxingyu 的回复:]

SQL code
mysql> select a.aclid,a.userid,a.moduleid,max(a.aclstate | b.aclstate ),a.extend
sState
-> from acl a ,acl b where a.moduleid=b.moduleid
-> group by moduleid;
+-------+--------+----……
[/Quote]真的惭愧，我知道我也菜但...那表是我改过了的主要是用来学习用的，不过真的有好几次一些个灌水的人真尽说些不搭边的话回些，最后我硬着头皮结了贴（是为了结贴率），而且我上网的机会不多，（从不去网吧）基本上就乘同学空闲时间上网狂狂