34,576
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
id1 INT,
id2 VARCHAR(5)
)
CREATE TABLE B(
id1 INT,
id2 VARCHAR(5)
)
GO
INSERT INTO A VALUES (1,'A')
INSERT INTO A VALUES (1,'B')
INSERT INTO A VALUES (2,'C')
INSERT INTO A VALUES (2,'D')
INSERT INTO A VALUES (2,'E')
INSERT INTO A VALUES (3,'F')
INSERT INTO A VALUES (4,'G')
INSERT INTO A VALUES (4,'H')
INSERT INTO B VALUES (1,'B')
INSERT INTO B VALUES (2,'D')
INSERT INTO B VALUES (2,'E')
INSERT INTO B VALUES (4,'H')
GO
SELECT
id1
,id2
,ISNULL( (SELECT '存在' FROM B WHERE b.id2=a.id2),'') AS BJ
FROM A
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([ID1] int,[ID2] nvarchar(21))
Insert #A
select 1,N'A' union all
select 1,N'B' union all
select 2,N'C' union all
select 2,N'D' union all
select 2,N'E' union all
select 3,N'F' union all
select 4,N'G' union all
select 4,N'H'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([ID1] int,[ID2] nvarchar(21))
Insert #B
select 1,N'B' union all
select 2,N'D' union all
select 2,N'E' union all
select 4,N'H'
Go
--测试数据结束
SELECT #A.*,
CASE
WHEN #B.ID2 IS NOT NULL THEN
'存在'
ELSE
''
END BJ
FROM #A
LEFT JOIN #B
ON #B.ID2 = #A.ID2;