none
请教一个复杂的SELECT RRS feed

  • 问题

  • MS SQL2005数据库中有如下表:
    表User:
    ID:用户的唯一ID
    Name:用户的名字
    MemberOf:用户所属于的组的名字。用户可以属于多个组,组之间以逗号分隔,如GroupA,GroupB,GroupC
    举例如下:
    ID Name MemberOf
    1   A       GroupA,GroupB
    2   B       GroupA,GroupC
    3   C       GroupB
    4   D       GroupC
    表Article:
    ID:文件唯一的ID
    Title:文件的标题
    Groups:可以查看该文件的组的名字,文件可以有多个组可以查看,组之间以逗号分隔,如GroupA,GroupC
    FileData:文件的内容
    举例如下:
    ID Title   Groups                            FileData
    1   Title1 GroupA,GroupB,GroupC   Title1.doc
    2   Title2 GroupB,GroupC               Title2.doc
    3   Title3 GroupC                           Title3.doc
    请问:如何获得某个用户可以查看文件的列表,如用户A可以查看文件的列表应该是
    ID Title   Groups                            FileData
    1   Title1 GroupA,GroupB,GroupC    Title1.doc
    2   Title2 GroupB,GroupC                Title2.doc

    2010年8月12日 2:33

答案

  • CREATE TABLE [USER]
    (
    	ID INT,
    	Name VARCHAR(10),
    	MemberOf VARCHAR(100)
    );
    CREATE TABLE Article 
    (
    	ID INT,
    	Title VARCHAR(10),
    	Groups VARCHAR(100),
    	FileData VARCHAR(100)
    );
    GO
    INSERT [user] SELECT 
    1,  'A',    'GroupA,GroupB' UNION ALL SELECT 
    2,  'B',    'GroupA,GroupC' UNION ALL SELECT
    3,  'C',    'GroupB' UNION ALL SELECT
    4,  'D',    'GroupC' ;
    INSERT Article SELECT 
    1 , 'Title1', 'GroupA,GroupB,GroupC',  'Title1.doc' UNION ALL SELECT 
    2 , 'Title2', 'GroupB,GroupC'    ,  'Title2.doc' UNION ALL SELECT 
    3 , 'Title3', 'GroupC'       ,  'Title3.doc';
    GO
    DECLARE @Name VARCHAR(10)
    SET @name='A'
    SELECT A.*
    FROM Article A 
    WHERE EXISTS(
    	SELECT * FROM (
    		SELECT u.Name,GroupM=substring(u.MemberOf,s.number,charindex(',',u.MemberOf+',',s.number)-s.number)
    		FROM [user] u JOIN master..spt_values s ON s.number BETWEEN 1 AND len(u.MemberOf)
    		WHERE s.type='p' AND u.Name=@Name AND substring(','+u.MemberOf,s.number,1)=',')k
    	WHERE charindex(k.GroupM,A.Groups)>0
    )
    /*
    ID  Title Groups        FileData 
    ---- ------ -------------------- ----------
      1 Title1 GroupA,GroupB,GroupC Title1.doc
      2 Title2 GroupB,GroupC    Title2.doc*/
    
    
    
    • 已标记为答案 123kaca 2010年8月12日 4:18
    2010年8月12日 3:37

全部回复

  • CREATE TABLE [USER]
    (
    	ID INT,
    	Name VARCHAR(10),
    	MemberOf VARCHAR(100)
    );
    CREATE TABLE Article 
    (
    	ID INT,
    	Title VARCHAR(10),
    	Groups VARCHAR(100),
    	FileData VARCHAR(100)
    );
    GO
    INSERT [user] SELECT 
    1,  'A',    'GroupA,GroupB' UNION ALL SELECT 
    2,  'B',    'GroupA,GroupC' UNION ALL SELECT
    3,  'C',    'GroupB' UNION ALL SELECT
    4,  'D',    'GroupC' ;
    INSERT Article SELECT 
    1 , 'Title1', 'GroupA,GroupB,GroupC',  'Title1.doc' UNION ALL SELECT 
    2 , 'Title2', 'GroupB,GroupC'    ,  'Title2.doc' UNION ALL SELECT 
    3 , 'Title3', 'GroupC'       ,  'Title3.doc';
    GO
    DECLARE @Name VARCHAR(10)
    SET @name='A'
    SELECT A.*
    FROM Article A 
    WHERE EXISTS(
    	SELECT * FROM (
    		SELECT u.Name,GroupM=substring(u.MemberOf,s.number,charindex(',',u.MemberOf+',',s.number)-s.number)
    		FROM [user] u JOIN master..spt_values s ON s.number BETWEEN 1 AND len(u.MemberOf)
    		WHERE s.type='p' AND u.Name=@Name AND substring(','+u.MemberOf,s.number,1)=',')k
    	WHERE charindex(k.GroupM,A.Groups)>0
    )
    /*
    ID  Title Groups        FileData 
    ---- ------ -------------------- ----------
      1 Title1 GroupA,GroupB,GroupC Title1.doc
      2 Title2 GroupB,GroupC    Title2.doc*/
    
    
    
    • 已标记为答案 123kaca 2010年8月12日 4:18
    2010年8月12日 3:37
  • 经测试,完全可行,谢谢!
    2010年8月12日 4:19
  • Just kindly remind: 如果你的表可以不这样设计的话(个人觉得你的Groups和MemberOf的设计在运算时不太方便和高效),你可以参考如何更好更高效的实现一对多,多对多的关系.
    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    2010年8月12日 6:48
  • 如果你的表就这样设计了,可以自建一个string split的方法,然后查询,如下:

    CREATE FUNCTION dbo.fnSplit(
      @sInputList VARCHAR(8000) -- List of delimited items
     , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
     BEGIN
     SELECT
     @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
     @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
     
     IF LEN(@sItem) > 0
     INSERT INTO @List SELECT @sItem
     END
    
    IF LEN(@sInputList) > 0
     INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
    END
    GO
    
    DECLARE	@strUser varchar(800)
    set @strUser = 'UserA'
    DECLARE	@strString varchar(800)
    select @strString = MemberOf from [User] where Name=@strUser;
    with temp as
    (
     select * from dbo.fnSplit(@strString,',')
    )
    select distinct FileData from dbo.ArticleTable A join temp T on A.Groups like '%'+T.item+'%'
    
    
    

    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    2010年8月13日 6:59