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

Question

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

Answer 1

如下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