27,579
社区成员
发帖
与我相关
我的任务
分享
if exists(select 1 from sysobjects where name = 'test' )
drop table test
go
create table test
(
id int identity(1,1),
name nvarchar(10)
)
insert test(name) select N'张三' union all select N'李四' union all select N'马六'
go
declare @m nvarchar(1000), @n nvarchar(200)
set @m = N'张三,李四,王五,马六'
declare @i int, @i2 int
create table #tmp(name nvarchar(10))
set @i = 0
while 1 = 1
begin
select @i2 = charindex(',', @m, @i)
if @i2 = 0
insert into #tmp select substring(@m, @i, len(@m) - @i + 1)
else
insert into #tmp select substring(@m, @i, @i2 - @i)
select @i = @i2 + 1
if @i2<= 0
break
end
declare @nothaving nvarchar(50)
select @nothaving = ''
select @nothaving = @nothaving + a.name + ',' from #tmp a where not exists( select 1 from test where a.name = name)
print @nothaving
drop table #tmp
select xm from table where xm in('张三','李四','王五','马六')
怎么会只显示记录数?
select distinct xm from table where xm not in('张三','李四','王五','马六')
显示不在这4个姓名中的其他姓名
select T.xm from
(
select '张三' as xm
union all select '李四'
union all select '王五'
union all select '赵六'
) T
left join TB on TB.xm=T.xm
where TB.xm in('张三','李四','王五','马六')
AND TB.xm is null
DECLARE @s NVARCHAR(2000)
SET @s=',张三,李四,王五,马六,'
select @s=REPLICATE(@s,','+xm+',',',')
from table1
SELECT @s
select xm from table where xm not in('张三','李四','王五','马六');