问题:当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 0sql:?????
结果:(这里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);
from acl a ,acl b where a.moduleid=b.moduleid
group by moduleid;你提供的表结构和插入语句不匹配。
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
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>
+-------+---------------+-------------+----------+----------+--------------+
| 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>