土豆大布丁
2021-09-07 14:29
采纳率: 50%
浏览 78

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

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

img

  • 收藏

5条回答 默认 最新

  • guwei4037 2021-09-07 20:59
    已采纳

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

    已采纳该答案
    打赏 评论
  • qfl_sdu 2021-09-07 14:36

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

    1 打赏 评论
  • 於黾 2021-09-07 14:36

    left join

    打赏 评论
  • 唯一的小彬哥 2021-09-07 17:30

    你好,刚好我精通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
    
     
    
    

    答案我在原答案上改了

    打赏 评论
  • 唯一的小彬哥 2021-09-08 09:51

    回答半天竟然已采纳!

    打赏 评论

相关推荐 更多相似问题