declare @a table(id int identity(1,1),a int)
insert @a select top 100 0 from syscolumnsselect username,groupname,purviewname 
from
(
select c.username,GroupName,
substring(PurviewID+',',id,charindex(',',PurviewID+',',id)-id) IID 
from 
@a a,usergroup b,SysUser c where substring(','+PurviewID,id,1)=',' and c.GroupID=b.GroupID
)aa
inner join
Purview bb
on aa.iid=bb.PurviewID order by username--result
/*
username                       groupname                      purviewname                    
------------------------------ ------------------------------ ------------------------------ 
admin                          系统管理员                          所有权限
test1                          功能测试员                          新建管理员
test1                          功能测试员                          执行功能一
test1                          功能测试员                          执行功能二
test2                          性能测试员                          执行功能三
test2                          性能测试员                          执行功能二
test2                          性能测试员                          执行功能一(所影响的行数为 7 行)
*/

解决方案 »

  1.   

    use test
    go
    CREATE TABLE SysUser(
        UserID        int PRIMARY KEY IDENTITY,
        UserName    varchar(50) NOT NULL,
        PassWord    varchar(64) NOT NULL,
        GroupID        int,
        Status        int DEFAULT 2,
        UserMemo    text,
        IsLogin        int DEFAULT 0
    )
    GOINSERT INTO SysUser (UserName,PassWord,GroupID,Status,UserMemo,IsLogin) 
        Values ('admin','admin',1,0,'系统管理员',0)
    GO
    INSERT INTO SysUser (UserName,PassWord,GroupID,Status,UserMemo,IsLogin) 
            Values ('test1','test1',2,0,'测试人员1',0)
    GO
    INSERT INTO SysUser (UserName,PassWord,GroupID,Status,UserMemo,IsLogin) 
            Values ('test2','test2',3,0,'测试人员2',0)
    GOCREATE TABLE UserGroup(
        GroupID        int PRIMARY KEY IDENTITY,
        GroupName    varchar(50) NOT NULL,
        PurviewID    varchar(255) NOT NULL DEFAULT '1',
        GroupStatus    int DEFAULT 0,
        GroupMemo    text
    )
    GOINSERT INTO UserGroup (GroupName,PurviewID,GroupStatus,GroupMemo) 
        Values ('系统管理员','1', 0, '管理组说明') 
    GO
    INSERT INTO UserGroup (GroupName,PurviewID,GroupStatus,GroupMemo) 
        Values ('功能测试员','2,3,4', 0, '管理组说明') 
    GO
    INSERT INTO UserGroup (GroupName,PurviewID,GroupStatus,GroupMemo) 
        Values ('性能测试员','3,4,5', 0, '管理组说明') 
    GOCREATE TABLE Purview(
        PurviewID    int PRIMARY KEY IDENTITY,
        PurviewName    varchar(50) NOT NULL,
        PurviewStatus    int DEFAULT 0, 
        PurviewMemo    text,
        PurviewMana    int
    )
    GOINSERT INTO Purview (PurviewName,PurviewStatus,PurviewMemo,PurviewMana) 
        Values ('所有权限',1,'新建管理员,所有权限',0)
    GO
    INSERT INTO Purview (PurviewName,PurviewStatus,PurviewMemo,PurviewMana) 
        Values ('新建管理员',1,'权限',0)
    GO
    INSERT INTO Purview (PurviewName,PurviewStatus,PurviewMemo,PurviewMana) 
        Values ('执行功能一',1,'权限',0)
    GO
    INSERT INTO Purview (PurviewName,PurviewStatus,PurviewMemo,PurviewMana) 
        Values ('执行功能二',1,'权限',0)
    GO
    INSERT INTO Purview (PurviewName,PurviewStatus,PurviewMemo,PurviewMana) 
        Values ('执行功能三',1,'新建管理员,所有权限',0)
    GO
    INSERT INTO Purview (PurviewName,PurviewStatus,PurviewMemo,PurviewMana) 
        Values ('执行功能四',1,'权限',0)
    GO
    INSERT INTO Purview (PurviewName,PurviewStatus,PurviewMemo,PurviewMana) 
        Values ('执行功能五',1,'新建管理员,所有权限',0)go
    select 
    a.UserName,b.GroupName,PurviewName
    from 
    SysUser a
    join
    UserGroup b on a.GroupID=b.GroupID
    join
    Purview c on charindex(','+rtrim(c.PurviewID)+',',','+b.PurviewID+',')>1UserName                                           GroupName                                          PurviewName                                        
    -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
    test1                                              功能测试员                                              执行功能一
    test1                                              功能测试员                                              执行功能二
    test2                                              性能测试员                                              执行功能二
    test2                                              性能测试员                                              执行功能三(所影响的行数为 4 行)
      

  2.   

    select 
    a.UserName,b.GroupName,PurviewName
    from 
    SysUser a
    join
    UserGroup b on a.GroupID=b.GroupID
    join
    Purview c on charindex(','+rtrim(c.PurviewID)+',',','+b.PurviewID+',')>0--改为0UserName                                           GroupName                                          PurviewName                                        
    -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
    admin                                              系统管理员                                              所有权限
    test1                                              功能测试员                                              新建管理员
    test1                                              功能测试员                                              执行功能一
    test1                                              功能测试员                                              执行功能二
    test2                                              性能测试员                                              执行功能一
    test2                                              性能测试员                                              执行功能二
    test2                                              性能测试员                                              执行功能三(所影响的行数为 7 行)
      

  3.   

    再问个问题.
    外网上有个数据库.里面有一个客户信息表(customer)
    我没有权限在上面操作如建触发器等操作.只能给读取的权限.现在要在本地上把外网上的那张表(customer)的内容同步到本地数据库上.
    应该怎么作??再开一帖求助地址为:http://topic.csdn.net/u/20071121/14/600af27e-90d3-43fe-8afc-a2be9173af48.html结此帖.
    谢谢楼上各位朋友