如果您正在寻找比group-concat结果更可用(和可加入)的东西,请尝试以下解决方案。我已经创建了一些与你的例子非常相似的表格,以使其有意义。
这适用于:
你想要一个纯SQL解决方案(没有代码,没有循环)
您有一组可预测的属性(例如,不是动态的)
您可以在需要添加新属性类型时更新查询
您希望结果可以是JOINed,UNIONed或嵌套为子选择
表A(文件)
FileID, Title, Author, CreatedOn
表B(属性)
AttrID, AttrName, AttrType [not sure how you use type...]
表C(Files_Attributes)
FileID, AttrID, AttrValue
传统查询会拉出许多冗余行:
SELECT * FROM
Files F
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID);
AttrID FileID标题作者CreatedOn AttrValue AttrName AttrType
50 1 TestFile Joe 2011-01-01 true ReadOnly bool
60 1 TestFile Joe 2011-01-01 xls FileFormat文本
70 1 TestFile Joe 2011-01-01 false私人布尔
80 1 TestFile Joe 2011-01-01 2011-10-03 LastModified日期
60 2 LongNovel Mary 2011-02-01 json FileFormat文本
80 2 LongNovel Mary 2011-02-01 2011-10-04 LastModified日期
70 2 LongNovel Mary 2011-02-01 true私人布尔
50 2 LongNovel Mary 2011-02-01 true ReadOnly bool
50 3 ShortStory Susan 2011-03-01 false ReadOnly bool
60 3 ShortStory Susan 2011-03-01 ascii FileFormat文本
70 3 ShortStory Susan 2011-03-01 false私人布尔
80 3 ShortStory Susan 2011-03-01 2011-10-01 LastModified日期
50 4 ProfitLoss Bill 2011-04-01 false ReadOnly bool
70 4 ProfitLoss Bill 2011-04-01 true Private bool
80 4 ProfitLoss Bill 2011-04-01 2011-10-02 LastModified date
60 4 ProfitLoss Bill 2011-04-01 text FileFormat text
50 5 MonthlyBudget George 2011-05-01 false ReadOnly bool
60 5 MonthlyBudget George 2011-05-01二进制文件格式文本
70 5 MonthlyBudget George 2011-05-01 false私人布尔
80 5 MonthlyBudget George 2011-05-01 2011-10-20 LastModified日期
这个合并查询(使用MAX的方法)可以合并行:
SELECT
F.*,
MAX( IF(A.AttrName = 'ReadOnly', FA.AttrValue, NULL) ) as 'ReadOnly',
MAX( IF(A.AttrName = 'FileFormat', FA.AttrValue, NULL) ) as 'FileFormat',
MAX( IF(A.AttrName = 'Private', FA.AttrValue, NULL) ) as 'Private',
MAX( IF(A.AttrName = 'LastModified', FA.AttrValue, NULL) ) as 'LastModified'
FROM
Files F
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID)
GROUP BY
F.FileID;
FileID标题作者CreatedOn ReadOnly FileFormat Private LastModified
1 TestFile Joe 2011-01-01 true xls false 2011-10-03
2 LongNovel Mary 2011-02-01 true json true 2011-10-04
3 ShortStory Susan 2011-03-01 false ascii false 2011-10-01
4 ProfitLoss Bill 2011-04-01 false text true 2011-10-02
5 MonthlyBudget George 2011-05-01 false binary false 2011-10-20