I have a table that looks like:
a | b
---+---
1 | a
2 | a
3 | a
1 | b
3 | b
2 | c
3 | c
It represents many-to-many relatons a<->b. I'd like to get all existing relations a<->count(b)<->a, like:
a1 | a2 | count
----+----+-------
1 | 2 | 1 #1<->a<->2
1 | 3 | 2 #1<->(a,b)<->3
2 | 1 | 1 #duplicate for 1<->a<->2
2 | 3 | 2 #2<->(a,c)<->3
3 | 1 | 2 #duplicate for 1<->(a,b)<->3
3 | 1 | 2 #duplicate for 2<->(a,c)<->3
I've managed it yet for single a, but can't figure out how to cycle through all:
SELECT
'1' AS a1,
t1.a AS a2,COUNT(t1.b)
FROM
a_b t1
INNER JOIN(
SELECT
b
FROM a_b
WHERE
a = '1'
) t2
ON
t1.b = t2.b
WHERE t1.a != '1'
GROUP BY t1.a
ORDER BY t1.a;
a1 | a2 | count
----+----+-------
1 | 2 | 1
1 | 3 | 2
Is it achievable without cross joining a_b on itself or looping through external script?
Here is SQLFiddle http://www.sqlfiddle.com/#!1/8b53a/1/0
TIA
A little addition to Gordon Linoff's solution: In order to not get the relations double (i.e. 1-3 and 3-1) I added the where clause:
select ab1.a as a1, ab2.a as a2, count(*)
from a_b ab1 join
a_b ab2
on ab1.b = ab2.b and ab1.a <> ab2.a
where ab1.a < ab2.a
group by ab1.a, ab2.a
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다