how can i list items in one field MySQL?
i have a table fil开发者_JAVA技巧led with models,the models start year and end year and productid's that are assigned to it
ModelNo | StartYear | EndYear | ProductID |
0 | 1997 | 2000 | 511777 |
1 | 1998 | 1998 | 511789 |
what i'd like is an sql result as follows:
ProductID | Profile
511777 | 0-1997,0-1998,0-1999,0-2000
511789 | 1-1998
If anyone has any ideas as to how i could achieve this.
DPERROTT
You'd need to join onto an auxiliary numbers table (containing sequential integers) to bring the missing numbers in the range in. i.e. something like
... FROM Models m
JOIN Numbers n ON n.number >= m.StartYear AND n.number <= m.EndYear
Then use CONCAT to get 0-1997
and GROUP_CONCAT to get 0-1997,0-1998,0-1999,0-2000
精彩评论