连接具有逗号分隔值的列上的表 [英] Joining tables on columns with comma separated values

问题描述

表1：mas_book_author

Table1: mas_book_author

author_id   |    author_name     
------------+----------------
 1          | jhon
 2          | roobini
 3          | virat
 4          | sachin
 5          | siva
 6          | priya

表4：mas_bk_accession_entry

Table4: mas_bk_accession_entry

id(int)(pk)  | author_ids(varchar)
-------------+-------------------
1            | 1,5
2            | 5,1
3            | 2,5
4            | 3,5
5            | 4,5  

我的结果应该是这样的：

my result should be like this:

author_name |count
------------+------
 jhon,siva  | 2    

这是我的SQL查询

select b.author_name,COUNT(*) as totbook 
from mas_bk_accession_entry a
   join mas_book_author as b 
     on b.author_id in (select cast((unnest(string_to_array(f.author_ids,',')) ) as int) as author_ids 
                        from mas_bk_accession_entry f)
where a.author_ids = '1,5'
group by b.author_name

推荐答案

您可以简化连接条件，并且需要一个string_agg（ ）以逗号分隔的作者姓名列表：

You can simplify your join condition and you need a string_agg() to get the comma separated list of author names:

select string_agg(author_name,','), count(*)
from mas_book_author b
  join mas_bk_accession_entry e on b.author_id = any(string_to_array(author_ids,',')::int[])
where e.author_ids = '1,5';

在线示例： http://rextester.com/NVNBH72654

但是您应该真正修复数据模型。存储逗号分隔的值（例如 author_ids 列）是一个非常非常糟糕的选择。

But you should really fix your data model. Storing comma separated values like the author_ids column is a really, really bad choice.

这篇关于连接具有逗号分隔值的列上的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！