表vote_title中有字段id,user_name和readers,
例如其中user_name表数据为
id user_name readers
1 davy,alsaby,anglie,seaky,nenil davy,seaky
2 limy,alizy,kent,weatmy limy,kent,weatmy
现在如何查询出所有在user_name字段中存在,而在readers字段中却不存在的用户名.
(注明:系统数据库版本为4.0,这个我也没办法改变,所以不要写存储过程)
SQL code:
object_id('[T]') is not null drop table [T]
go
create table [T]([col1] varchar(5),[col2] varchar(50),[col3] datetime,[col4] varchar(50))
insert [T]
select 'Silly','1,2,3,4','2009-11-10','A,BC,C,D'
--------------开始查询--------------------------
Select ID=IDENTITY(INT,1,1),
a.Col1,COl2=substring(a.Col2,C.number,charindex(',',a.Col2+',',C.number)-C.number),
a.col3 INTO #TA
from T A
JOIN master..spt_values C ON C.type='p' and substring(','+a.COl2,C.number,1)=','
Select ID=IDENTITY(INT,1,1),
a.Col1,
a.col3,
col4=substring(a.Col4,B.number,charindex(',',a.Col4+',',B.number)-B.number) INTO #TB
from
T a join master..spt_values b
ON B.type='p' AND substring(','+a.COl4,b.number,1)=','
回复2楼:
sql serve
