34,590
社区成员
发帖
与我相关
我的任务
分享
select * from (values('a'),('b'),('c')) s(name)
where not exists(select 0 from TableA as a where a.Name=s.[name])
with B as
(
select 'a' name union all
select 'b' name union all
select 'c' name
)
select * from B left join A on B.name=A.name
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[f_splitstr](@SourceSql NVARCHAR(MAX),@StrSeprate VARCHAR(100))
RETURNS @temp TABLE(F1 VARCHAR(100))
AS
BEGIN
DECLARE @ch AS VARCHAR(100)
SET @SourceSql=@SourceSql+@StrSeprate
WHILE(@SourceSql<>'')
BEGIN
SET @ch=LEFT(@SourceSql,CHARINDEX(@StrSeprate,@SourceSql,1)-1)
INSERT @temp VALUES(@ch)
SET @SourceSql=STUFF(@SourceSql,1,CHARINDEX(@StrSeprate,@SourceSql,1),'')
END
RETURN
END
declare @s varchar(100),@sql varchar(1000)
set @s='a,b,c,d,e,f';
;WITH A(id,name)AS(
select 1,'a' union all
select 2,'c' union all
select 3,'d'
)
SELECT F1
FROM dbo.f_splitstr(@s, ',')
WHERE F1 NOT IN ( SELECT name
FROM A )
use test
go
--> --> (Andy)生成測試數據
if not object_ID('Tempdb..#') is null
drop table #
Go
Create table #(id nvarchar(2),name nvarchar(4))
Insert #
select '1','a' union all
select '2','c' union all
select '3','d'
Go
Select char(ascii(b.name)-1) as name
from # as a
inner join # as b on b.name=char(ascii(a.name)-1)
/*
name
-----------------------
b
*/