首页 新闻 赞助 找找看

SQL这种查询怎么实现

0
悬赏园豆:10 [待解决问题]

 

如何把这三张表合在一起啊。

显示如下

batchcode    qc   l   c

wunaigong的主页 wunaigong | 初学一级 | 园豆:4
提问于:2011-10-27 17:35
< >
分享
所有回答(4)
0

select  batchcode,qc,l,c  from
(select * from 表1 a  join 表2 b on a.batchcode=b.batchcode) ab 
 join 表3 c on ab.batchcode=c.batchcode

Micpower | 园豆:187 (初学一级) | 2011-10-27 17:46
0

full join 将三个表连起来不可以吗?

小小刀 | 园豆:1991 (小虾三级) | 2011-10-27 20:57
0

使用左或者右连接就行,left join 或者 right join

webaspx | 园豆:1973 (小虾三级) | 2011-10-28 09:00
0
-------------------------------
--
你好,
--
我的答案如下(性能自己注意了)
--
-----------------------------
SELECT 'KE20GBD-015510802' AS batchcode, 1 AS qc INTO #tem_1

SELECT 'KF14GBD-015760709' AS batchcode, 100 AS l INTO #tem_2 UNION ALL
SELECT 'KF24GBC', 23 UNION ALL
SELECT 'KH20GBD-015510802', 10

SELECT 'KE20GBD-015510802' AS batchcode, 1 AS c INTO #tem_3 UNION ALL
SELECT 'KF14GBD-015760709', 100 UNION ALL
SELECT 'KF24GBC', 23

SELECT * FROM #tem_1
SELECT * FROM #tem_2
SELECT * FROM #tem_3

/*
DROP TABLE #tem_1
DROP TABLE #tem_2
DROP TABLE #tem_3
*/

SELECT
CASE
WHEN t1.batchcode IS NULL THEN CASE
when t2.batchcode IS NULL THEN t3.batchcode
ELSE t2.batchcode
END
ELSE t1.batchcode
END batchcode,
--t1.batchcode,
--t2.batchcode,
--t3.batchcode,
t1.qc,
t2.l,
t3.c
FROM
#tem_1 t1 FULL JOIN #tem_2 t2
ON t1.batchcode = t2.batchcode
FULL JOIN #tem_3 t3
ON t1.batchcode = t3.batchcode
or t2.batchcode = t3.batchcode
dotNetDR_ | 园豆:2078 (老鸟四级) | 2011-10-28 09:41
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册