将多行合并到一个MySQL中 [英] Combine multiple rows into one MySQL
本文介绍了将多行合并到一个MySQL中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要一些帮助.我用谷歌搜索了解决方案,但没有找到解决方法.
I need some help. I googled for solution, but I didn't found one.
我有三个表:
langs
(int) id | (varchar) language
1 | English
2 | Latin
3 | Esperanto
... | ...
keys
(int) id | (varchar) keys
1 | dog
2 | cat
... | ...
value
(int) id | (int) key_id | (int) lang_id | (varchar) value
1 | 1 | 1 | Dog
2 | 1 | 2 | Canis
3 | 2 | 1 | Cat
4 | 2 | 2 | Felis
5 | 2 | 3 | Kato
... | ... | ... | ...
我想得到结果:
key_id | keys | English | Latin | Esperanto
1 | dog | Dog | Canis | NULL
2 | cat | Cat | Felis | Kato
... | ... | ... | ... | ...
合并等于key_id
的行.我知道我可以尝试使用多个JOIN来做到这一点,但这很慢,而且我必须知道不同langs
的确切数目.
merging rows with equal key_id
. I know I can try to make it with multiple JOINs, but it's slow and I must know the exact number of different langs
.
先谢谢了. :)
推荐答案
SELECT CONCAT(
' SELECT `value`.`key_id`,'
, '`keys`.`keys`,'
, GROUP_CONCAT(
'GROUP_CONCAT(IF(`value`.`lang_id`=',id,',`value`.`value`,NULL))'
, ' AS `', REPLACE(language, '`', '``'), '`'
)
, ' FROM `keys` JOIN `value` ON `value`.`key_id` = `keys`.`id`'
, ' GROUP BY `value`.`key_id`'
)
INTO @sql
FROM `langs`;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
这篇关于将多行合并到一个MySQL中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文