这个查询怎么做？

Question

一个表a1id name
1   a
2   b
3   c
4   d
5   e
6   f另一个表a2id  list    name 
1   1,2,3    aa
2   1,4      bb
查出结果：a2.name  a1.name  a1.id
aa        a         1
aa        b         2
aa        c         3
bb        a         1
bb        d         4
......

Answer 1

先拆分list然后再和a1表联合查询?

Answer 2

--分解字符串包含的信息值去另外一表查询相应的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-22  广东深圳)/*问题描述：
需要将用户表中的用户组与信息表中的用户组对比.如果有相同的组则可查看该信息. 
一个用户可能是几个用户组的成员.一条信息可能是几个用户组都可以查看. 表一 
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 3

select a2.name  ,a1.name,  a1.id 
from a2,a1
where charindex(ltrim(a1.id),a2.list)>0

Answer 4

SELECT
a2.name,
a1.name,
a1.id
FROM a2,
a1
WHERE CHARINDEX(',' + RTRIM(a1.id) + ',', ',' + a2.list + ',') > 0
ORDER BY a2.name, a1.id

Answer 5

--> 测试数据: @a1
declare @a1 table (id int,name varchar(1))
insert into @a1
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e' union all
select 6,'f'
--> 测试数据: @a2
declare @a2 table (id int,list varchar(10),name varchar(10))
insert into @a2
select 1,'1,2,3','aa' union all
select 2,'1,4','bb'select b.name,a.name,a.id from @a1 a right join @a2 b on charindex(','+ltrim(a.id)+',',','+ltrim(b.list)+',')>0
order by b.name

Answer 6

create table #(id int, name varchar(10))
insert into # 
select 1,  'a' union all 
select 2,  'b' union all
select 3,  'c' union all
select 4,  'd' union all
select 5,  'e' union all
select 6,  'f' create table #1(id int, list varchar(10),    name varchar(10))
insert into #1 
select 1,  '1,2,3',    'aa' union all 
select 2,  '1,4'   ,   'bb' 
select b.name  ,a.name,  a.id 
from # a,#1 b 
where charindex(ltrim(a.id),b.list)>0
/*
name       name       id
---------- ---------- -----------
aa         a          1
aa         b          2
aa         c          3
bb         a          1
bb         d          4(5 行受影响)*/

Answer 7

--2000中使用临时表的方法.
create table a1(id varchar(10) , name varchar(10))
insert into a1 values('1' , 'a') 
insert into a1 values('2' , 'b') 
insert into a1 values('3' , 'c') 
insert into a1 values('4' , 'd') 
insert into a1 values('5' , 'e') 
insert into a1 values('6' , 'f') 
create table a2 (id int, list varchar(10) ,  name varchar(10))
insert into a2 values(1,  '1,2,3' ,   'aa') 
insert into a2 values(2,  '1,4'   ,   'bb') 
go
--建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b  
select n.name , m.name , n.list from a1 m,
(
  SELECT A.name,list = SUBSTRING(A.list, B.ID, CHARINDEX(',', A.list + ',', B.ID) - B.ID) FROM a2 A, tmp B WHERE SUBSTRING(',' + a.list, B.id, 1) = ',' 
) n
where m.id = n.list
order by n.namedrop table a1,a2,tmp/*
name       name       list       
---------- ---------- ---------- 
aa         a          1
aa         b          2
aa         c          3
bb         a          1
bb         d          4（所影响的行数为 5 行）
*/

Answer 8

--> --> 
 
if not object_id('a1') is null
drop table a1
Go
Create table a1([id] int,[name] nvarchar(1))
Insert a1
select 1,N'a' union all
select 2,N'b' union all
select 3,N'c' union all
select 4,N'd' union all
select 5,N'e' union all
select 6,N'f'
Go--> --> 
 
if not object_id('a2') is null
drop table a2
Go
Create table a2([id] int,[list] nvarchar(5),[name] nvarchar(2))
Insert a2
select 1,N'1,2,3',N'aa' union all
select 2,N'1,4',N'bb'
Go
;with roy as 
(select [id],[name],[list]=cast(left([list],charindex(',',[list]+',')-1) as nvarchar(100)),Split=cast(stuff([list]+',',1,charindex(',',[list]+','),'') as nvarchar(100)) from a2
union all
select [id],[name],[list]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select a.[name],b.[name],b.[ID]
from 
roy a
join
a1 b on a.[list]=b.ID
order by a.[id] option (MAXRECURSION 0)
/*
name name ID
---- ---- -----------
aa   a    1
aa   b    2
aa   c    3
bb   a    1
bb   d    4(5 個資料列受到影響)
*/

Answer 9

declare @a1 table (id int, name varchar(10))
insert into @a1
select 1,  'a' union all 
select 2,  'b' union all
select 3,  'c' union all
select 4,  'd' union all
select 5,  'e' union all
select 6,  'f' declare @a2 table(id int, list varchar(10),    name varchar(10))
insert into @a2 
select 1,  '1,2,3',    'aa' union all 
select 2,  '1,4'   ,   'bb' 
---
select a2.name,a1.name,a1.id from @a1 a1,@a2 a2 where charindex(ltrim(a1.id),a2.list)>0
aa a 1
aa b 2
aa c 3
bb a 1
bb d 4