SQL server中如何比较两个表中的字段.

Question

需要将用户表中的用户组与信息表中的用户组对比.如果有相同的组则可查看该信息.
一个用户可能是几个用户组的成员.一条信息可能是几个用户组都可以查看.表一
ID  usergroup  username
1    1,2,4      用户A
2    3          用户B表二
id  usergroup  title 
1    1,3,4     信息标题1
2    3         信息标题2
3    1,2,3     信息标题3用户A可以看到 
表二中的
信息标题1
信息标题3用户B可以看到 
表二中的
信息标题1
信息标题2
信息标题3

Answer 1

--sql server 2005中的写法.
create table TB(ID int,usergroup varchar(20),username varchar(10))
insert into TB values(1,'1,2,4','用户A') 
insert into TB values(2,'3'    ,'用户B')
create table TA(id int,usergroup varchar(20),title varchar(10))
insert into TA values(1,'1,3,4','信息标题1') 
insert into TA values(2,'3'    ,'信息标题2') 
insert into TA values(3,'1,2,3','信息标题3') 
goselect distinct c.username , d.title from
(
  SELECT A.username, B.usergroup FROM(SELECT username, [usergroup] = CONVERT(xml,'<root><v>' + REPLACE([usergroup], ',', '</v><v>') + '</v></root>') FROM TB)A
  OUTER APPLY(SELECT usergroup = N.v.value('.', 'varchar(100)') FROM A.[usergroup].nodes('/root/v') N(v))B
) c,
(
  SELECT A.title   , B.usergroup FROM(SELECT title, [usergroup] = CONVERT(xml,'<root><v>' + REPLACE([usergroup], ',', '</v><v>') + '</v></root>') FROM TA)A
  OUTER APPLY(SELECT usergroup = N.v.value('.', 'varchar(100)') FROM A.[usergroup].nodes('/root/v') N(v))B
) d
where c.usergroup = d.usergroup
order by username , titledrop table TB,TA/*
username   title
---------- ----------
用户A        信息标题1
用户A        信息标题3
用户B        信息标题1
用户B        信息标题2
用户B        信息标题3(5 行受影响)
*/

Answer 2

--sql server 2000中的写法.
create table TB(ID int,usergroup varchar(20),username varchar(10))
insert into TB values(1,'1,2,4','用户A') 
insert into TB values(2,'3'    ,'用户B')
create table TA(id int,usergroup varchar(20),title varchar(10))
insert into TA values(1,'1,3,4','信息标题1') 
insert into TA values(2,'3'    ,'信息标题2') 
insert into TA values(3,'1,2,3','信息标题3') 
go-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b  
 
select distinct c.username , d.title from
(
  SELECT A.username,usergroup = SUBSTRING(A.usergroup, B.ID, CHARINDEX(',', A.usergroup + ',', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING(',' + a.usergroup, B.id, 1) = ',' 
) c,
(
  SELECT A.title   ,usergroup = SUBSTRING(A.usergroup, B.ID, CHARINDEX(',', A.usergroup + ',', B.ID) - B.ID) FROM ta A, tmp B WHERE SUBSTRING(',' + a.usergroup, B.id, 1) = ',' 
) d
where c.usergroup = d.usergroup
order by username , titledrop table TB,TA,tmp/*
username   title
---------- ----------
用户A        信息标题1
用户A        信息标题3
用户B        信息标题1
用户B        信息标题2
用户B        信息标题3(5 行受影响)
*/

Answer 3

谢谢dawugui 我用的是sql2000
提示如下错误信息:
服务器: 消息 156，级别 15，状态 1，行 5
在关键字 'OUTER' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 5
第 5 行: '(' 附近有语法错误。
服务器: 消息 156，级别 15，状态 1，行 9
在关键字 'OUTER' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 9
第 9 行: '(' 附近有语法错误。

Answer 4

我上面有两个,第一个是sql server 2005的,第二个是sql server 2000的,你对应选择.

Answer 5

非常感谢.SQL2000的可运行.先试下先.

Answer 6

--sql server 2000中不用临时表的SQL语句.
create table TB(ID int,usergroup varchar(20),username varchar(10))
insert into TB values(1,'1,2,4','用户A') 
insert into TB values(2,'3'    ,'用户B')
create table TA(id int,usergroup varchar(20),title varchar(10))
insert into TA values(1,'1,3,4','信息标题1') 
insert into TA values(2,'3'    ,'信息标题2') 
insert into TA values(3,'1,2,3','信息标题3') 
goselect distinct c.username , d.title from
(
  select a.username,usergroup=substring(a.usergroup,b.id,charindex(',',a.usergroup+',',b.id)-b.id)
  from tb a,(select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9  union select 10 union select 11 union select 12 union select 13 union select 14 union select 15) b
  where substring(','+a.usergroup,b.id,1)=','
) c, TA d
where charindex(',' + c.usergroup + ',' , ',' + d.usergroup + ',') > 0
order by username , titledrop table TB,TA/*
username   title
---------- ----------
用户A        信息标题1
用户A        信息标题3
用户B        信息标题1
用户B        信息标题2
用户B        信息标题3(5 行受影响)
*/

Answer 7

--分解字符串包含的信息值去另外一表查询相应的信息
/*问题描述：
需要将用户表中的用户组与信息表中的用户组对比.如果有相同的组则可查看该信息. 
一个用户可能是几个用户组的成员.一条信息可能是几个用户组都可以查看. 表一 
ID     usergroup     username 
1         1,2,4             用户A 
2         3                     用户B 表二 
id     usergroup     title   
1         1,3,4           信息标题1 
2         3                   信息标题2 
3         1,2,3           信息标题3 用户A可以看到   
表二中的 
信息标题1 
信息标题3 用户B可以看到   
表二中的 
信息标题1 
信息标题2 
信息标题3
*/-------------------------------------------------------------
--sql server 2000中用临时表的写法.
create table TB(ID int,usergroup varchar(20),username varchar(10))
insert into TB values(1,'1,2,4','用户A') 
insert into TB values(2,'3'    ,'用户B')
create table TA(id int,usergroup varchar(20),title varchar(10))
insert into TA values(1,'1,3,4','信息标题1') 
insert into TA values(2,'3'    ,'信息标题2') 
insert into TA values(3,'1,2,3','信息标题3') 
go--建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b  
 
select distinct c.username , d.title from
(
  SELECT A.username,usergroup = SUBSTRING(A.usergroup, B.ID, CHARINDEX(',', A.usergroup + ',', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING(',' + a.usergroup, B.id, 1) = ',' 
) c, TA d
where charindex(',' + c.usergroup + ',' , ',' + d.usergroup + ',') > 0
order by username , titledrop table TB,TA,tmp/*
username   title
---------- ----------
用户A        信息标题1
用户A        信息标题3
用户B        信息标题1
用户B        信息标题2
用户B        信息标题3(5 行受影响)
*/--------------------------------------------------------------------
--sql server 2000中不用临时表的SQL语句.
create table TB(ID int,usergroup varchar(20),username varchar(10))
insert into TB values(1,'1,2,4','用户A') 
insert into TB values(2,'3'    ,'用户B')
create table TA(id int,usergroup varchar(20),title varchar(10))
insert into TA values(1,'1,3,4','信息标题1') 
insert into TA values(2,'3'    ,'信息标题2') 
insert into TA values(3,'1,2,3','信息标题3') 
goselect distinct c.username , d.title from
(
  select a.username,usergroup=substring(a.usergroup,b.id,charindex(',',a.usergroup+',',b.id)-b.id)
  from tb a,(select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9  union select 10 union select 11 union select 12 union select 13 union select 14 union select 15) b
  where substring(','+a.usergroup,b.id,1)=','
) c, TA d
where charindex(',' + c.usergroup + ',' , ',' + d.usergroup + ',') > 0
order by username , titledrop table TB,TA/*
username   title
---------- ----------
用户A        信息标题1
用户A        信息标题3
用户B        信息标题1
用户B        信息标题2
用户B        信息标题3(5 行受影响)
*/-------------------------------------------------------------------
--sql server 2005中的写法.
create table TB(ID int,usergroup varchar(20),username varchar(10))
insert into TB values(1,'1,2,4','用户A') 
insert into TB values(2,'3'    ,'用户B')
create table TA(id int,usergroup varchar(20),title varchar(10))
insert into TA values(1,'1,3,4','信息标题1') 
insert into TA values(2,'3'    ,'信息标题2') 
insert into TA values(3,'1,2,3','信息标题3') 
goselect distinct c.username , d.title from
(
  SELECT A.username, B.usergroup FROM(SELECT username, [usergroup] = CONVERT(xml,'<root><v>' + REPLACE([usergroup], ',', '</v><v>') + '</v></root>') FROM TB)A
  OUTER APPLY(SELECT usergroup = N.v.value('.', 'varchar(100)') FROM A.[usergroup].nodes('/root/v') N(v))B
) c, TA d
where charindex(',' + c.usergroup + ',' , ',' + d.usergroup + ',') > 0
order by username , titledrop table TB,TA/*
username   title
---------- ----------
用户A        信息标题1
用户A        信息标题3
用户B        信息标题1
用户B        信息标题2
用户B        信息标题3(5 行受影响)
*/

Answer 8

--用一个函数校验值
create Table T1(ID int,usergroup varchar(20),username varchar(10))
insert into T1 values(1,'1,2,4','用户A') 
insert into T1 values(2,'3'    ,'用户B')
go
create Table T2(id int,usergroup varchar(20),title varchar(10))
insert into T2 values(1,'1,3,4','信息标题1') 
insert into T2 values(2,'3'    ,'信息标题2') 
insert into T2 values(3,'1,2,3','信息标题3') 
gogo
create function F_split(@s1 nvarchar(100),@s2 nvarchar(100))
returns bit
as
begin
declare @flag bit
select @flag=0,@s1=@s1+',',@s2=','+@s2+','
while charindex(',',@s1)>0 and @flag=0
begin
if charindex(','+left(@s1,charindex(',',@s1)),@s2)>0
select @flag=1
else
set @s1=stuff(@s1,1,charindex(',',@s1),'')
end
return @flag
endgo
select 
* 
from 
T1 a 
join 
T2 b on dbo.F_split(a.usergroup,b.usergroup)=1
where a.username='用户A'go
select 
* 
from 
T1 a 
join 
T2 b on dbo.F_split(a.usergroup,b.usergroup)=1
where a.username='用户B'ID          usergroup            username   id          usergroup            title      
----------- -------------------- ---------- ----------- -------------------- ---------- 
1           1,2,4                用户A        1           1,3,4                信息标题1
1           1,2,4                用户A        3           1,2,3                信息标题3（所影响的行数为 2 行）ID          usergroup            username   id          usergroup            title      
----------- -------------------- ---------- ----------- -------------------- ---------- 
2           3                    用户B        1           1,3,4                信息标题1
2           3                    用户B        2           3                    信息标题2
2           3                    用户B        3           1,2,3                信息标题3（所影响的行数为 3 行）