Buscar todos los coautores: Faceting / Agrupamiento para la tabla de asignación de muchos a muchos

Table : Books ------------- ID Name 1 Book1 2 Book2 3 Book3 4 Book4 Table : Authors ------------- ID Name 1 Author1 2 Author2 3 Author3 4 Author4 Table : BookAuthorMapping --------------------------- ID BookId AuthorId 1 1 1 2 1 2 3 1 3 4 2 2 5 2 3 6 3 3 7 4 4 

Asi que,

  • Libro1 escrito por Autor1, Autor2, Autor3
  • Book2 está escrito por Author2, Author3
  • Book3 solo es escrito por Author3
  • Book4 solo es escrito por Author4

El problema es: dado un AuthorId, necesito saber cuántos libros coautor otros autores con él:

Ejemplo:

 Given AuthorId: 1 ------------------- AuthorId Count 2 1 // 2 has co-authonetworking only book1 3 1 // 3 has co-authonetworking only book1 1 1 // Its okay, if i get author1 in the result Given AuthorId: 2 ------------------- AuthorId Count 1 1 // 1 has co-authonetworking only book1 3 2 // 3 has co-authonetworking book1 and book2 2 2 // Its okay if i get author 2 in the result Given AuthorId: 3 ------------------- AuthorId Count 1 1 // 1 has co-authonetworking only book1 2 2 // 2 has co-authonetworking book1 and 2 3 3 // Its okay if i get author 3 in the result. Given AuthorId: 4 ------------------- AuthorId Count 4 1 // I should not get author1 0 author2 0 , author3 0 for this 

– EDITAR – Tuve una solución como: select aId, count(mapping.bId) from mapping join (select bId from mapping where aId = ?) as tmp on mapping.bId = tmp.bId group by aId;

Además, @Giorgos Betsos mencionó lo mismo en las respuestas.

Tengo curiosidad si es posible sin la consulta interna.

Prueba esto:

 SELECT "AuthorId", COUNT(*) FROM BookAuthorMapping WHERE "BookId" IN (SELECT "BookId" FROM BookAuthorMapping WHERE "AuthorId" = 1) GROUP BY "AuthorId" 

Demo aquí

Alternativamente, puede usar una INNER JOIN :

 SELECT t1."AuthorId", COUNT(*) FROM BookAuthorMapping AS t1 INNER JOIN BookAuthorMapping AS t2 ON t1."BookId" = t2."BookId" AND t2."AuthorId" = 1 GROUP BY t1."AuthorId" 

Demo aquí

También puede hacer esto con las funciones de window:

 select authorid, count(*) from (select bam.*, max(case when AuthorId = 1 then 1 else 0 end) over (partition by bookid) as HasAuthor1 from BookAuthorMapping bam ) bam where hasAuthor1 = 1 group by authorid; 

En algunas circunstancias, la function de window sería más rápida que una aproximación usando join o in .

Intereting Posts