Question

我正在使用一种姿态,我有一个这样的表格:

sec_id     prod_id
1          789
1          908
1          678
13         789
13         123
13         908
15         789

我想能够确定在秘密内发生的重叠(共同提议)。这就是说,我希望能够产生类似于:

sec_id1    sec_id2    overlap
1          13         2       
1          15         1       
13         1          2       
13         15         1
15         1          1
15         13         1

我没有这方面的经验,希望得到任何援助。

Answer 1

我被用到MySQL,因此,你可能需要稍微调整yn子,以备:

SELECT a.sec_id AS sec_id1, b.sec_id AS sec_id2, COUNT(*) AS overlap
FROM tblname AS a JOIN tblname AS b 
WHERE a.prod_id = b.prod_id AND a.sec_id != b.sec_id 
GROUP BY a.sec_id, b.sec_id

也许,这将在邮政总局(以及MySQL)开展工作:

SELECT a.sec_id AS sec_id1, b.sec_id AS sec_id2, COUNT(*) AS overlap
FROM tblname AS a JOIN tblname AS b ON a.prod_id = b.prod_id 
WHERE a.sec_id != b.sec_id 
GROUP BY a.sec_id, b.sec_id

友情链接