How to retrieve datasets from normalised shema in MySQL 5.7?

问题: I am trying to retrieve datasets from a normalised MySQL 5.7 table shema where I am struggling to get the values. There are 4 tables: https://www.db-fiddle.com/f/q2PJZVeg...

问题:

I am trying to retrieve datasets from a normalised MySQL 5.7 table shema where I am struggling to get the values.

There are 4 tables: https://www.db-fiddle.com/f/q2PJZVegdWXnpkotN2utu2/0

Table1: articles

article_id | title
1            First Car
2            Second Car

Table2: articles_attr

article_id | attr_id 
1            1
1            2
1            3
1            5
2            3
2            4

Table3: attr_groups

attr_id | attr_group_id | attribute
1         1               red
2         2               diesel
3         3               automatic
4         3               airbag
5         3               radio

Table4: attr_groups_names

attr_group_id | name
1               color
2               engine
3               features

Now I would like to retrieve all datasets (car1, car2, ..) with all attributes where the ones with multiple attributes per group get agregated.

e.g.

article_id | title | color | engine | features
1            Car 1   red     diesel   automatic,radio
2 ... 

The amount of groups is huge (20+), so I would like to avoid to many joins.

My best shot:

SELECT 
    a.article_id, 
    a.title,
    GROUP_CONCAT(CASE attr.attr_group_id WHEN 26 THEN cat.attr_de END) AS functions,
    GROUP_CONCAT(CASE attr.attr_group_id WHEN 27 THEN cat.attr_de END) AS miscellaneous

FROM       articles_attr AS attr 
INNER JOIN articles a ON a.article_id = attr.article_id
INNER JOIN articles_attr AS cat ON cat.attr_id = attr.attr_id

GROUP BY   a.article_id
LIMIT 3

How can this be done?


回答1:

Your query has a correct basic structure, but your CASE expressions look somewhat off. Try this version:

SELECT
    a.article_id,
    a.title,
    GROUP_CONCAT(CASE WHEN agn.name = 'color'    THEN ag.attribute END) color,
    GROUP_CONCAT(CASE WHEN agn.name = 'engine'   THEN ag.attribute END) engine,
    GROUP_CONCAT(CASE WHEN agn.name = 'features' THEN ag.attribute END) features
FROM articles a
INNER JOIN articles_attr aa
    ON a.article_id = aa.article_id
INNER JOIN attr_groups ag
    ON ag.attr_id = aa.attr_id
INNER JOIN attr_groups_names agn
    ON agn.attr_group_id = ag.attr_group_id
GROUP BY
    a.article_id,
    a.title;

enter image description here

Demo

GROUP_CONCAT works here by ignoring NULL values in the aggregation, which then do not get added to the concatenated string. Note also that depending on your MySQL version, you might have to GROUP BY both the id and title in the articles table.

  • 发表于 2019-01-06 06:43
  • 阅读 ( 237 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除