时间戳

首页 > 解决方案 > 左连接在结果中重复列的相同值

mysql - 左连接在结果中重复列的相同值

问题描述

我有这个 sql 查询：

SELECT t1.id_action, DATE_FORMAT(t1.date_creation,'%d/%m/%Y à %H:%i') AS creation_action, 
u.id_utilisateur, p.genre AS genre_utilisateur,
p.nom, av.titre_avancement, b.nom_banque, c.nom_courtier
FROM action AS t1 
LEFT JOIN type 
ON type.id_type=t1.id_type
LEFT JOIN avancement AS av
ON av.id_avancement=t1.id_avancement  
LEFT JOIN utilisateur AS u
ON u.id_utilisateur=t1.id_utilisateur 
LEFT JOIN personne AS p 
ON p.id_personne=u.id_personne
left join offre_concurrente o 
on o.id_dossier = t1.id_dossier
left join banque b 
on b.id_banque = o.id_banque
left join courtier_concurrent c 
on c.id_courtier_concurrent=o.id_courtier_concurrent
WHERE t1.id_dossier=11 AND t1.fait='1' 
GROUP BY t1.id_action
ORDER BY t1.date_creation DESC

在“b.nom_banque”和“c.nom_courtier”中，它显示为空，除了这两个列之外，所有其他列都可以，我无法弄清楚确切的问题

标签： mysql

解决方案

您应该将 select 中的所有非聚合列添加到 group by 子句，但我可以看到您没有使用任何聚合函数，因此请改用 DISTINCT。尝试这个：

SELECT DISTINCT t1.id_action, DATE_FORMAT(t1.date_creation,'%d/%m/%Y à %H:%i') AS creation_action, 
u.id_utilisateur, p.genre AS genre_utilisateur,
p.nom, av.titre_avancement, b.nom_banque, c.nom_courtier
FROM action AS t1 
LEFT JOIN type 
ON type.id_type=t1.id_type
LEFT JOIN avancement AS av
ON av.id_avancement=t1.id_avancement  
LEFT JOIN utilisateur AS u
ON u.id_utilisateur=t1.id_utilisateur 
LEFT JOIN personne AS p 
ON p.id_personne=u.id_personne
left join offre_concurrente o 
on o.id_dossier = t1.id_dossier
left join banque b 
on b.id_banque = o.id_banque
left join courtier_concurrent c 
on c.id_courtier_concurrent=o.id_courtier_concurrent
WHERE t1.id_dossier=11 AND t1.fait='1' 
ORDER BY t1.date_creation DESC

推荐阅读