开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜