SQLに関して質問です。とりあえずSQLiteを用いています。

難しいと言っておきながらなんですが、望みの結果と少し違いますが、

select B1.a_id as id1, B2.a_id as id2, count(*) as count

from B as B1 join B as B2

where id1 < id2 and B1.code = B2.code

group by id1, id2;

で似た出力は得られます。なお、テーブルAは利用していません。

同一の項目がもの例でいうところの 2 - 4が0という部分は抜けてます。

何かの参考になれば。

MSDEですか以下で強引に取れました。

JOINやnull値の扱いなどで環境依存があるかもしれませんが

一応標準規格でやっています

SELECT C.ID1
      ,C.ID2
      ,COUNT(C.B_CODE) AS CNT
  FROM
(
SELECT A.ID1
      ,A.ID2
      ,A.CODE AS A_CODE
      ,B.CODE AS B_CODE
  FROM
(
SELECT L.ID1
      ,L.ID2
      ,R.CODE
  FROM
(
SELECT X.*
FROM
(
SELECT L.ID AS ID1
      ,R.ID AS ID2
  FROM TABLE_A AS L
      ,TABLE_A AS R
) AS X
 WHERE  X.ID1 < X.ID2
) AS L
LEFT OUTER JOIN TABLE_B AS R
  ON L.ID1 = R.TABLE_A_ID
) AS A LEFT OUTER JOIN
(
SELECT L.ID1
      ,L.ID2
      ,R.CODE
  FROM
(
SELECT X.*
  FROM
(
SELECT L.ID AS ID1
      ,R.ID AS ID2
  FROM TABLE_A AS L
      ,TABLE_A AS R
) AS X
 WHERE X.ID1 < X.ID2
) AS L
LEFT OUTER JOIN TABLE_B AS R
  ON L.ID2 = R.TABLE_A_ID
) AS B
ON A.ID1=B.ID1 AND A.ID2=B.ID2 AND A.CODE=B.CODE
) AS C
GROUP BY C.ID1,C.ID2
ORDER BY C.ID1,C.ID2

ちなみにテーブル類

create table TABLE_A
(ID INTEGER
)
GO
INSERT INTO TABLE_A VALUES(1)
GO
INSERT INTO TABLE_A VALUES(2)
GO
INSERT INTO TABLE_A VALUES(3)
GO
INSERT INTO TABLE_A VALUES(4)
GO

create table TABLE_B
(ID INTEGER
,TABLE_A_ID INTEGER
,CODE CHAR(2)
)
GO

INSERT INTO TABLE_B VALUES(1,1,'10')
INSERT INTO TABLE_B VALUES(2,1,'11')
INSERT INTO TABLE_B VALUES(3,2,'10')
INSERT INTO TABLE_B VALUES(4,2,'11')
INSERT INTO TABLE_B VALUES(5,2,'12')
INSERT INTO TABLE_B VALUES(6,3,'10')
INSERT INTO TABLE_B VALUES(7,3,'11')
INSERT INTO TABLE_B VALUES(8,3,'12')
INSERT INTO TABLE_B VALUES(9,3,'13')
INSERT INTO TABLE_B VALUES(10,4,'13')
GO

SQLiteの環境がないのですが。。。

1行目の「case when cnt is null then 0 else cnt end as cnt」は、id1=1 & id2=4などのケースで、nullが返ってくるので、nullの場合は0に変換しています。

SQLiteのマニュアルを見たところ、関数が他のRDBMSと異なるところがあるようです。

Query Language Understood by SQLite: expression

もし、構文エラーになるようなら、SQLiteの構文に合わせてください。

性能に関してですが、TABLE_Bに、次のインデクスを定義してみてください。

create index Bix1 on TABLE_B(table_a_id,code)

select x.id1,x.id2,case when cnt is null then 0 else cnt end as cnt
 from
 (select A1.id as id1,A2.id as id2
   from TABLE_A as A1
    inner join TABLE_A as A2
    on A1.id<A2.id) as x
 left join
 (select B1.table_a_id as id1,B2.table_a_id as id2,count(B1.code) as cnt
   from TABLE_B as B1 
    left join TABLE_B as B2
     on B1.table_a_id<B2.table_a_id and B1.code=B2.code
   where B2.table_a_id is not null
   group by B1.table_a_id,B2.table_a_id) as y
 on x.id1=y.id1 and x.id2=y.id2
 order by x.id1,x.id2
</pre>

最初の回答のTABLE_BのLEFT JOINは、INNER JOINでよかったので、訂正版です。

LEFT JOINのままでも、「where B2.table_a_id is not null」の条件を入れてあるので、そのままでも

結果に違いはありません。

select x.id1,x.id2,case when cnt is null then 0 else cnt end as cnt
 from
 (select A1.id as id1,A2.id as id2
   from TABLE_A as A1
    inner join TABLE_A as A2
    on A1.id<A2.id) as x
 left join
 (select B1.table_a_id as id1,B2.table_a_id as id2,count(B1.code) as cnt
   from TABLE_B as B1 
    inner join TABLE_B as B2
     on B1.table_a_id<B2.table_a_id and B1.code=B2.code
   group by B1.table_a_id,B2.table_a_id) as y
 on x.id1=y.id1 and x.id2=y.id2
 order by x.id1,x.id2
</pre>