三表联合查询

h150085001

求助：求知识，谢谢各路英雄

1、如何在查询里可以实现表4的效果。(以表3的CODE为条件，表1、表2的对应记录合并在一起)
2、请看我做的联合查询，为什么会多了两条记录

最耀B

感谢楼主的 分享啊！！哈哈！

aslxt

首先纠正一下，intime、outtime的字段类型应改为日期时间类型，innum改为数字类型，然后：
SELECT a.code, Max(a.intime) AS intime之最大值, Max(a.outtime) AS outtime之最大值, Sum(a.innum) AS innum之总计
FROM [SELECT 表1.code,表1.intime,表1.innum,0 as outtime
FROM 表1
UNION ALL SELECT 表2.code,0 as intime,0 as innum,表2.outtime
FROM 表2]. AS a
GROUP BY a.code
HAVING (((a.code) In (select code from 表3)))
基本可以达到你要的效果。

aslxt

（以表3的CODE为条件，表1、表2的对应记录合并在一起)
可以有几种理解：
1、表1、表2的对应记录的code必须在表3中。楼上就是这种情况的处理方法之一
2、表3的所有记录，以及表1、表2的对应记录的code必须在表3中。这种情况还得另外处理，如下：
SELECT a.code, Max(a.intime) AS intime之最大值, Max(a.outtime) AS outtime之最大值, Sum(a.innum) AS innum之总计
FROM [SELECT 表1.code,表1.intime,表1.innum,0 as outtime
FROM 表1
UNION ALL SELECT 表2.code,0 as intime,0 as innum,表2.outtime
FROM 表2 union SELECT 表3.code,0 as intime,0 as innum,0 as outtime
FROM 表3]. AS a
GROUP BY a.code
HAVING (((a.code) In (select code from 表3)))

h150085001

aslxt,thank you你啊!!!问题得已解决
{:soso_e181:}