2个表查询的字段GroupID和Card_ID个数不一样,题主的查询得到结果不对,,请专家答疑解惑

Question

已知条件A:
表#LIST是队员的信息,有8个字段的信息,如何通过8个字段的数据,查询对应的结果?

要求: #team里的任意8个字段的数据 必须是和表#LIST8个字段的数据完全相同

脚本随后上传(题主的脚本效率太低)

Answer 1

1、如果#LIST和#team中的Card_ID和GroupID乱序存储，那直接通过你的方法的OR条件需要写10个中取3个的组合数量的OR条件才能完成所有可能的组合的判定（即满足你要求中的“……任意……”的条件，这种不仅难得写，还容易出错……针对这种存储方式，我的方法与楼上的一样，都是通过列转行来实现的

2、如果#LIST和#team中的Card_ID和GroupID按字段序号乱序存储且中间不跳号，就如你的样例数据一样，那就比较简单了，你只需要三组OR条件就能筛选出想要的结果

-- 乱序存储
     WITH tm AS (
            SELECT id, CLASS, GroupID1 gid FROM #team UNION ALL SELECT id, CLASS, GroupID2 gid FROM #team UNION ALL SELECT id, CLASS, GroupID3 gid FROM #team UNION ALL SELECT id, CLASS, GroupID4 FROM #team UNION ALL
            SELECT id, CLASS, GroupID5 gid FROM #team UNION ALL SELECT id, CLASS, GroupID6 gid FROM #team UNION ALL SELECT id, CLASS, GroupID7 gid FROM #team UNION ALL SELECT id, CLASS, GroupID8 FROM #team UNION ALL
            SELECT id, CLASS, GroupID9 gid FROM #team UNION ALL SELECT id, CLASS, GroupID10 gid FROM #team )
        , li AS (
            SELECT ID, Card_ID1 cid FROM #LIST UNION ALL SELECT ID, Card_ID2 cid FROM #LIST UNION ALL SELECT ID, Card_ID3 cid FROM #LIST UNION ALL SELECT ID, Card_ID4 cid FROM #LIST UNION ALL
            SELECT ID, Card_ID5 cid FROM #LIST UNION ALL SELECT ID, Card_ID6 cid FROM #LIST UNION ALL SELECT ID, Card_ID7 cid FROM #LIST UNION ALL SELECT ID, Card_ID8 cid FROM #LIST )
        , tj AS (
            SELECT tm.id
              FROM tm JOIN li ON tm.gid = li.cid GROUP BY tm.id, li.ID HAVING COUNT( 1 ) = 8 )
    SELECT * FROM #team a WHERE EXISTS( SELECT 1 FROM tj b WHERE a.id = b.id );

-- 顺序存储
    SELECT *
      FROM #team a
     WHERE EXISTS( SELECT 1 FROM #LIST b
                    WHERE ( a.GroupID1 = b.Card_ID1 AND a.GroupID8 = b.Card_ID8 )
                       OR ( a.GroupID2 = b.Card_ID1 AND a.GroupID9 = b.Card_ID8 )
                       OR ( a.GroupID3 = b.Card_ID1 AND a.GroupID10 = b.Card_ID8 ) )