Sql查询显示一列中的许多值

Column t.name(genre) is showing to many same values I tried to make it work but i didn't succed..

列t.name(流派)显示了许多相同的值,我试图让它工作,但我没有成功..

Here is my SQL

这是我的SQL

select o.id, b.title, a.firstName, a.lastName, 
        b.noOfPages, b.price, group_concat(t.name) as 'genre'
from author a,book_author ba, book_type bt,orders o,type t, book b, order_list ol
where ol.book_fk = b.id 
and bt.book_fk = b.id 
and bt.type_fk = t.id 
and ba.author_fk = a.id 
and ba.book_fk = b.id 
and ol.orders_fk = '74' 
GROUP BY ol.id

Picture

Picture is there to show how many values in column genre query returns

图片用于显示列类型查询返回的值

1 个解决方案

#1

I believe you are concerned with repeated values of "Crime" in your genre column. Try using distinct inside group_concat.

我相信你关注你的类型专栏中“犯罪”的重复价值。尝试使用不同的内部group_concat。

select o.id, b.title, a.firstName, a.lastName,b.noOfPages, b.price,
group_concat(distinct t.name) as 'genre' from author a,book_author ba,
book_type bt,orders o,type t, book b, order_list ol where ol.book_fk = b.id
and bt.book_fk = b.id  and bt.type_fk = t.id and ba.author_fk = a.id
and ba.book_fk = b.id  and ol.orders_fk = '74' GROUP BY ol.id;