sqlite查询多个表中字段相同的数据

一共有6个表
其中两两为一组
如一组中一个表字段为memberid,name,idcard另一个表记录memberid,money,conut.
现在要在三组内找到姓名相同的记录怎么处理

img

如下sql已在sqlite中测试通过。


--创建临时表，保存数据
create temporary table tempFilterTable(MemberID,name,idcard,money,count)

--将数据插入到临时表，注意是union all，这样重复的数据一样会保存
insert into tempFilterTable(MemberID,name,idcard,money,count) 
select MemberID,name,idcard,money,count from (
select t1.[MemberID],t1.[name],t1.[idcard],ext1.[money],ext1.[count] from t1 left join ext1 on t1.[MemBerID] = ext1.[MemBerID]
union all
select t2.[MemberID],t2.[name],t2.[idcard],ext2.[money],ext2.[count] from t2 left join ext2 on t2.[MemBerID] = ext2.[MemBerID]
union all
select t3.[MemberID],t3.[name],t3.[idcard],ext3.[money],ext3.[count] from t3 left join ext3 on t3.[MemBerID] = ext3.[MemBerID]) t

--筛选出姓名重复出现1次以上的数据
select * from tempFilterTable where name in (SELECT [name] FROM tempFilterTable GROUP BY [name] HAVING count(*) > 1 ) order by name

img

每组查出结果后，三组数据在代码里写个for循环遍历就是了

回答半天竟然已采纳！

left join

你好，刚好我精通Oracle性能优化，也会sqlite。
你的题目不是很清晰，按照我理解的意思，就是从三组中找相同的名称也就是NAME。
如果只是去找姓名相同的，那么只需要对姓名做等值关联即可
就像这样


SELECT A.memberid,A.NAME,B.idcard,B.MONEY,B.COUNT FROM (
    SELECT NAME,memberid FROM (
        SELECT A.MEMBERID,A.NAME FROM TGROUP1_MASTER A 
        UNION ALL
        SELECT A.MEMBERID,A.NAME FROM TGROUP2_MASTER A 
        UNION ALL
        SELECT A.MEMBERID,A.NAME FROM TGROUP3_MASTER A 
    ) A
    GROUP BY A.NAME,A.memberid HAVING COUNT(*) >1
) A ,
(
SELECT A.MEMBERID,A.NAME,A.idcard,B.* FROM TGROUP1_MASTER A LEFT JOIN  TGROUP1_EXT B ON A.memberid = B.memberid
 UNION ALL
 SELECT A.MEMBERID,A.NAME,A.idcard,B.* FROM TGROUP2_MASTER A LEFT JOIN  TGROUP2_EXT B ON A.memberid = B.memberid
UNION ALL
SELECT A.MEMBERID,A.NAME,A.idcard,B.* FROM TGROUP3_MASTER A LEFT JOIN  TGROUP3_EXT B ON A.memberid = B.memberid
) B

WHERE A.memberid = B.memberid AND A.NAME = B.NAME ORDER BY A.NAME ASC,B.MONEY ASC

答案我在原答案上改了