已知条件A:
表#LIST是队员的信息,有8个字段的信息,如何通过8个字段的数据,查询对应的结果?
要求: #team里的任意8个字段的数据 必须是和表#LIST8个字段的数据完全相同
脚本随后上传(题主的脚本效率太低)
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 ) )
;with t as (select ID,CLASS,GroupID1 as GroupID from #team
union all
select ID,CLASS,GroupID2 from #team
union all
select ID,CLASS,GroupID3 from #team
union all
select ID,CLASS,GroupID4 from #team
union all
select ID,CLASS,GroupID5 from #team
union all
select ID,CLASS,GroupID6 from #team
union all
select ID,CLASS,GroupID7 from #team
union all
select ID,CLASS,GroupID8 from #team
union all
select ID,CLASS,GroupID9 from #team
union all
select ID,CLASS,GroupID10 from #team),
s as (select ID,Card_ID1 as Card_ID from #LIST
union all
select ID,Card_ID2 from #LIST
union all
select ID,Card_ID3 from #LIST
union all
select ID,Card_ID4 from #LIST
union all
select ID,Card_ID5 from #LIST
union all
select ID,Card_ID6 from #LIST
union all
select ID,Card_ID7 from #LIST
union all
select ID,Card_ID8 from #LIST),
m as (select t.ID,s.ID as id_1,count() cnt
from t join s on t.GroupID=s.Card_ID
group by t.ID,s.ID
having count()>=8 )
select * from m
join #team a on m.ID=a.ID
join #LIST b on m.id_1=b.ID
执行题主的脚本,得到的结果里 CLASS 202 查询结果是重复的,执行效率太低,请问如何优化和纠错?
DROP TABLE IF EXISTS #team
create table #team([ID] [int] IDENTITY(1,1) NOT NULL primary key,CLASS INT,GroupID1 INT,GroupID2 INT,GroupID3 INT,GroupID4 INT,GroupID5 INT,GroupID6 INT,GroupID7 INT,GroupID8 INT,GroupID9 INT,GroupID10 INT)
insert into #team
SELECT '201',' 11',' 12',' 13',' 14',' 15',' 16',' 17',' 18',' 19',' 20'
UNION ALL SELECT '202',' 3',' 4',' 5',' 6',' 7',' 8',' 9',' 10',' 11',' 12'
UNION ALL SELECT '203',' 5',' 6',' 7',' 8',' 9',' 10',' 11',' 12',' 13',' 14'
UNION ALL SELECT '204',' 7',' 8',' 9',' 10',' 11',' 12',' 13',' 14',' 15',' 16'
UNION ALL SELECT '205',' 9',' 10',' 11',' 12',' 13',' 14',' 15',' 16',' 17',' 18'
DROP TABLE IF EXISTS #LIST
create table #LIST([ID] [int] IDENTITY(1,1) NOT NULL primary key,Card_ID1 INT,Card_ID2 INT,Card_ID3 INT,Card_ID4 INT,Card_ID5 INT,Card_ID6 INT,Card_ID7 INT,Card_ID8 INT)
insert into #LIST
SELECT '3','4','5','6','7','8','9','10'
UNION ALL SELECT '9','10','11','12','13','14','15','16'
UNION ALL SELECT '5','6','7','8','9','10','11','12'
select b.* FROM #team b, #LIST a
WHERE (a.Card_ID1=b.GroupID1
or a.Card_ID1=b.GroupID2
or a.Card_ID1=b.GroupID3
or a.Card_ID1=b.GroupID4
or a.Card_ID1=b.GroupID5
or a.Card_ID1=b.GroupID6
or a.Card_ID1=b.GroupID7
or a.Card_ID1=b.GroupID8
or a.Card_ID1=b.GroupID9
or a.Card_ID1=b.GroupID10
)
and
(a.Card_ID2=b.GroupID1
or a.Card_ID2=b.GroupID2
or a.Card_ID2=b.GroupID3
or a.Card_ID2=b.GroupID4
or a.Card_ID2=b.GroupID5
or a.Card_ID2=b.GroupID6
or a.Card_ID2=b.GroupID7
or a.Card_ID2=b.GroupID8
or a.Card_ID2=b.GroupID9
or a.Card_ID2=b.GroupID10
)
and
(a.Card_ID3=b.GroupID1
or a.Card_ID3=b.GroupID2
or a.Card_ID3=b.GroupID3
or a.Card_ID3=b.GroupID4
or a.Card_ID3=b.GroupID5
or a.Card_ID3=b.GroupID6
or a.Card_ID3=b.GroupID7
or a.Card_ID3=b.GroupID8
or a.Card_ID3=b.GroupID9
or a.Card_ID3=b.GroupID10
)
and
(a.Card_ID4=b.GroupID1
or a.Card_ID4=b.GroupID2
or a.Card_ID4=b.GroupID3
or a.Card_ID4=b.GroupID4
or a.Card_ID4=b.GroupID5
or a.Card_ID4=b.GroupID6
or a.Card_ID4=b.GroupID7
or a.Card_ID4=b.GroupID8
or a.Card_ID4=b.GroupID9
or a.Card_ID4=b.GroupID10
)
and
(a.Card_ID5=b.GroupID1
or a.Card_ID5=b.GroupID2
or a.Card_ID5=b.GroupID3
or a.Card_ID5=b.GroupID4
or a.Card_ID5=b.GroupID5
or a.Card_ID5=b.GroupID6
or a.Card_ID5=b.GroupID7
or a.Card_ID5=b.GroupID8
or a.Card_ID5=b.GroupID9
or a.Card_ID5=b.GroupID10
)
and
(a.Card_ID6=b.GroupID1
or a.Card_ID6=b.GroupID2
or a.Card_ID6=b.GroupID3
or a.Card_ID6=b.GroupID4
or a.Card_ID6=b.GroupID5
or a.Card_ID6=b.GroupID6
or a.Card_ID6=b.GroupID7
or a.Card_ID6=b.GroupID8
or a.Card_ID6=b.GroupID9
or a.Card_ID6=b.GroupID10
)
and
(a.Card_ID7=b.GroupID1
or a.Card_ID7=b.GroupID2
or a.Card_ID7=b.GroupID3
or a.Card_ID7=b.GroupID4
or a.Card_ID7=b.GroupID5
or a.Card_ID7=b.GroupID6
or a.Card_ID7=b.GroupID7
or a.Card_ID7=b.GroupID8
or a.Card_ID7=b.GroupID9
or a.Card_ID7=b.GroupID10
)
and
(a.Card_ID8=b.GroupID1
or a.Card_ID8=b.GroupID2
or a.Card_ID8=b.GroupID3
or a.Card_ID8=b.GroupID4
or a.Card_ID8=b.GroupID5
or a.Card_ID8=b.GroupID6
or a.Card_ID8=b.GroupID7
or a.Card_ID8=b.GroupID8
or a.Card_ID8=b.GroupID9
or a.Card_ID8=b.GroupID10
)
查询的结果,但不是正确的,查询的结果要和表 #LIST里的 8个数全部相同
ID CLASS GroupID1 GroupID2 GroupID3 GroupID4 GroupID5 GroupID6 GroupID7 GroupID8 GroupID9 GroupID10
2 202 3 4 5 6 7 8 9 10 11 12
4 204 7 8 9 10 11 12 13 14 15 16
5 205 9 10 11 12 13 14 15 16 17 18
2 202 3 4 5 6 7 8 9 10 11 12
3 203 5 6 7 8 9 10 11 12 13 14
想要得到结果是
ID CLASS GroupID1 GroupID2 GroupID3 GroupID4 GroupID5 GroupID6 GroupID7 GroupID8 GroupID9 GroupID10
2 202 3 4 5 6 7 8 9 10 11 12
3 203 5 6 7 8 9 10 11 12 13 14
4 204 7 8 9 10 11 12 13 14 15 16
5 205 9 10 11 12 13 14 15 16 17 18
这个提的有的看的我有点懵逼