sQL Server查询语句求帮助！！！！
本帖最后由 Peng_baoqing 于 2013-03-19 15:46:57 编辑 Table1
MID PID(主键) AA
A001 A001-001 1
A001 A001-002 0
A001 A001-003 0
A001 A001-004 0
Table2
ID(主键) QID PID DD EE
1 XX01 A001-001 1 0
2 XX02 A001-002 0 0
3 XX02 A001-003 0 0
4 XX04 A001-003 0 0
5 XX05 A001-003 0 1
Table3
ID(主键) QID Name
1 XX02 张三
2 XX04 李四
3 XX05 王五
4 XX06 赵六
如何查询出这样的结果
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
最好用一条Sql语句或者一个存储过程查询出来！！！！ sql?server
[解决办法]
Select tb3.name,tb1.pid,tb2.dd,tb2.ee,tb1.aa
From table1 tb1
LEFT OUTER JOIN table2 tb2 on tb2.pid=tb1.pid
LEFT OUTER JOIN table3 tb3 on tb3.qid=tb2.qid
[解决办法]
--> 测试数据：#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
----------------结果----------------------------
/*
NamePIDDDEEAA
张三A001-002000
张三A001-003000
李四A001-003000
NULLA001-004NULLNULL0
*/
