22,210
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:#Table1
IF OBJECT_ID('TEMPDB.DBO.#Table1') IS NOT NULL DROP TABLE #Table1
GO
CREATE TABLE #Table1([MID] VARCHAR(4),[PID] VARCHAR(8),[AA] INT)
INSERT #Table1
SELECT 'A001','A001-001',1 UNION ALL
SELECT 'A001','A001-002',0 UNION ALL
SELECT 'A001','A001-003',0 UNION ALL
SELECT 'A001','A001-004',0
--> 测试数据:#Table2
IF OBJECT_ID('TEMPDB.DBO.#Table2') IS NOT NULL DROP TABLE #Table2
GO
CREATE TABLE #Table2([ID] INT,[QID] VARCHAR(4),[PID] VARCHAR(8),[DD] INT,[EE] INT)
INSERT #Table2
SELECT 1,'XX01','A001-001',1,1 UNION ALL
SELECT 2,'XX02','A001-002',0,0 UNION ALL
SELECT 3,'XX02','A001-003',0,0 UNION ALL
SELECT 4,'XX04','A001-003',0,0 UNION ALL
SELECT 5,'XX05','A001-003',1,1
--> 测试数据:#Table3
IF OBJECT_ID('TEMPDB.DBO.#Table3') IS NOT NULL DROP TABLE #Table3
GO
CREATE TABLE #Table3([ID] INT,[QID] VARCHAR(4),[Name] VARCHAR(4))
INSERT #Table3
SELECT 1,'XX02','张三' UNION ALL
SELECT 2,'XX04','李四' UNION ALL
SELECT 3,'XX05','王五' UNION ALL
SELECT 4,'XX06','赵六'
--------------开始查询--------------------------
SELECT d.[Name] , a.[PID] , d.[DD] , d.[EE] , a.[AA]
FROM #Table1 a
LEFT JOIN (
SELECT b.[PID] , c.[Name],b.[DD] , b.[EE]
FROM #Table2 b
JOIN #Table3 c
ON b.[QID]= c.[QID]
AND (b.[EE]<>1 OR b.[DD]<>1)
) d
ON a.[PID] = d.[PID]
WHERE a.aa = 0
----------------结果----------------------------
/*
Name PID DD EE AA
张三 A001-002 0 0 0
张三 A001-003 0 0 0
李四 A001-003 0 0 0
NULL A001-004 NULL NULL 0
*/