Mysql

選擇具有相似 ID 的行並將歸檔的值合併為一個

  • May 11, 2015

我的情況如下。我有下面的查詢,查詢做得很好。但是我已經從其中排除了一個,它裡面field_data_field_luogo的行可以有列entity_id並且列中有不同的值field_luogo_tid,所以我想要做的是,如果行entity_id相似,則將列的值5合併到一列中field_luogo_tid具有多行。

SELECT a.nid,a.type,a.title,a.status,b.body_value,c.field_bici_value,
d.field_classifiche_url,d.field_classifiche_title,e.field_circuiti_tid,
f.field_corto_value,g.field_dcorto_value,h.field_medio_value,
i.field_dmedio_value,j.field_lungo_value,k.field_dlungo_value,
l.field_cronometraggio_tid,m.field_dalzeroit_value,n.field_data_value,
o.field_edizione_value,p.field_email_email,q.field_recapiti_value,
r.field_note_value,s.field_partenza_value,t.field_organizzatore_value,
u.field_star_value,v.field_superstar_value,w.field_tipomanifestazione_tid,
x.field_verifica_percorsi_value,tex.name as c_name , 
tex_t.name as cr_name,tex_n.name as m_name 
FROM node a 
LEFT JOIN field_data_body b ON b.entity_id = a.nid 
LEFT JOIN field_data_field_bici c ON c.entity_id = a.nid 
LEFT JOIN field_data_field_classifiche d ON d.entity_id = a.nid 
LEFT JOIN field_data_field_circuiti e ON e.entity_id = a.nid 
LEFT JOIN field_data_field_corto f ON f.entity_id = a.nid 
LEFT JOIN field_data_field_dcorto g ON g.entity_id = a.nid 
LEFT JOIN field_data_field_medio h ON h.entity_id = a.nid 
LEFT JOIN field_data_field_dmedio i ON i.entity_id = a.nid 
LEFT JOIN field_data_field_lungo j ON d.entity_id = a.nid 
LEFT JOIN field_data_field_dlungo k ON k.entity_id = a.nid 
LEFT JOIN field_data_field_cronometraggio l ON l.entity_id = a.nid 
LEFT JOIN field_data_field_dalzeroit m ON m.entity_id = a.nid 
LEFT JOIN field_data_field_data n ON n.entity_id = a.nid 
LEFT JOIN field_data_field_edizione o ON o.entity_id = a.nid 
LEFT JOIN field_data_field_email p ON p.entity_id = a.nid 
LEFT JOIN field_data_field_recapiti q ON q.entity_id = a.nid 
LEFT JOIN field_data_field_note r ON r.entity_id = a.nid 
LEFT JOIN field_data_field_partenza s ON s.entity_id = a.nid 
LEFT JOIN field_data_field_organizzatore t ON t.entity_id = a.nid 
LEFT JOIN field_data_field_star u ON u.entity_id = a.nid 
LEFT JOIN field_data_field_superstar v ON v.entity_id = a.nid 
LEFT JOIN field_data_field_tipomanifestazione w ON w.entity_id = a.nid 
LEFT JOIN field_data_field_verifica_percorsi x ON x.entity_id = a.nid 
LEFT JOIN taxonomy_term_data tex ON tex.tid = e.field_circuiti_tid 
LEFT JOIN taxonomy_term_data tex_t ON tex_t.tid = l.field_cronometraggio_tid LEFT JOIN taxonomy_term_data tex_n ON tex_n.tid = w.field_tipomanifestazione_tid 
WHERE a.type = 'manifestazione' 
AND a.status = 1  
GROUP BY a.nid

就像我有多個實體 ID = 5 的行,每行的 field_luogo_id 都不同,例如:

2345
9877
9863
2374

通過合併它將成為:

2345,9877,9863,2374

您可以為此使用組 concat。例子

mysql> select  * from dummy;
+----+-------+
| id | name  |
+----+-------+
|  1 | fiest |
|  1 | fiest |
|  1 | sec   |
+----+-------+
3 rows in set (0.00 sec)

mysql> select group_concat(id) from dummy group by id;
+------------------+
| group_concat(id) |
+------------------+
| 1,1,1            |
+------------------+
1 row in set (0.00 sec)

引用自:https://dba.stackexchange.com/questions/101159