mysql - 满足某些条件时合并唯一键上的字符串单元格

Question

ID  TELEFON     CULOARE    piesa1   piesa2  piesa3  piesa4
1   telefon1    culoare1    0N        0N      0N      0N
1   telefon1    culoare1    14Y       0N      0N      0N
2   telefon2    culoare2    0N        8Y      0N      0N
2   telefon2    culoare2    0N        0N      4Y      0N
3   telefon3    culoare3    0N        0N      0N      0Y
3   telefon3    culoare3    0N        0N      0N      0N
3   telefon3    culoare3    0N        0N      0N      0N
3   telefon3    culoare3    0N        0N      5Y      0N
4   telefon4    Neutru      8N        0N      0N      0N
4   telefon4    Neutru      0N        0N      1N      0N
4   telefon4    Neutru      0N        0N      0N      7Y

我有一个这样的结果集，但我想以某种方式合并 ID 列重复的行，只保留值以“Y”或“N”结尾但前面的数字大于 0 的单元格.我设法通过添加 SUM 聚合函数并按行的唯一标识符进行分组(在我的例子中是 TELEFON+CULOARE 列)来仅使用数字来做到这一点。

这个结果的查询:

SELECT tt.id as `#`
,t.telefon as TELEFON
, IFNULL(c.culoare,'Neutru') as CULOARE
, (IF(p.piesa = 'piesa1', CONCAT(tt.total_piese,tt.is_stoc_min),'0N')) AS piesa1
,(IF(p.piesa = 'piesa2', CONCAT(tt.total_piese,tt.is_stoc_min),'0N')) AS piesa2
,(IF(p.piesa = 'piesa3', CONCAT(tt.total_piese,tt.is_stoc_min),'0N')) AS piesa3
,(IF(p.piesa = 'piesa4', CONCAT(tt.total_piese,tt.is_stoc_min),'0N')) AS piesa4 
,tt.total_piese
,tt.is_stoc_min
FROM telefon as t LEFT JOIN
total_piese as tt
ON t.id = tt.id_telefon
LEFT JOIN culoare as c
ON c.id  = tt.id_culoare
LEFT JOIN piesa as p
ON p.id = tt.id_piesa
ORDER BY t.id;

我想要的结果是这样的:

TELEFON     CULOARE   piesa1    piesa2  piesa3  piesa4
telefon1    culoare1    14Y       0N      0N      0N
telefon2    culoare2    0N        8Y      4Y      0N
telefon3    culoare3    0N        0N      5Y      0Y
telefon4    Neutru      8N        0N      1N      7Y

我有一个返回合并行但值末尾没有“Y”或“N”的查询:

SELECT tt.id as `#`
,t.telefon as TELEFON
, IFNULL(c.culoare,'Neutru') as CULOARE
,SUM(IF(p.piesa = 'piesa1', tt.total_piese,0)) AS piesa1
,SUM(IF(p.piesa = 'piesa2', tt.total_piese,0)) AS piesa2
,SUM(IF(p.piesa = 'piesa3', tt.total_piese,0)) AS piesa3
,SUM(IF(p.piesa = 'piesa4',tt.total_piese,0)) AS piesa4 
,tt.stoc_min
 FROM telefon as t LEFT JOIN
 total_piese as tt
 ON t.id = tt.id_telefon
 LEFT JOIN culoare as c
 ON c.id  = tt.id_culoare
 LEFT JOIN piesa as p
 ON p.id = tt.id_piesa
 GROUP BY t.Telefon,c.Culoare
 ORDER BY t.id;

Answer 1

好吧，要给你一个更好(或更优化)的答案有点困难，因为我们没有“基础数据”，但已经有了结果。

无论如何，您可以将第一次尝试用作子查询，在 piesa 字段上使用 max。 (因为任何数字都会大于 0，并且 Y 大于 N，所以 MAX 应该给你想要的输出)

select id as `#`, telefon, culoare,
max(piesa1) as piesa1,
max(piesa2) as piesa2,
max(piesa3) as piesa3,
max(piesa4) as piesa4
from
  (SELECT 
     tt.id 
    ,t.telefon as TELEFON
    ,IFNULL(c.culoare,'Neutru') as CULOARE
    ,(IF(p.piesa = 'piesa1', CONCAT(tt.total_piese,tt.is_stoc_min),'0N')) AS piesa1
    ,(IF(p.piesa = 'piesa2', CONCAT(tt.total_piese,tt.is_stoc_min),'0N')) AS piesa2
    ,(IF(p.piesa = 'piesa3', CONCAT(tt.total_piese,tt.is_stoc_min),'0N')) AS piesa3
    ,(IF(p.piesa = 'piesa4', CONCAT(tt.total_piese,tt.is_stoc_min),'0N')) AS piesa4 
    ,tt.total_piese
    ,tt.is_stoc_min
  FROM telefon as t LEFT JOIN
    total_piese as tt ON t.id = tt.id_telefon
  LEFT JOIN culoare as c ON c.id  = tt.id_culoare
  LEFT JOIN piesa as p ON p.id = tt.id_piesa) s
GROUP BY id, telefon, culoare
ORDER BY Id

或者可能(未经测试)直接在查询中使用最大值

SELECT tt.id as `#`
,t.telefon as TELEFON
,IFNULL(c.culoare,'Neutru') as CULOARE
,max((IF(p.piesa = 'piesa1', CONCAT(tt.total_piese,tt.is_stoc_min),'0N'))) AS piesa1
,max((IF(p.piesa = 'piesa2', CONCAT(tt.total_piese,tt.is_stoc_min),'0N'))) AS piesa2
,max((IF(p.piesa = 'piesa3', CONCAT(tt.total_piese,tt.is_stoc_min),'0N'))) AS piesa3
,max((IF(p.piesa = 'piesa4', CONCAT(tt.total_piese,tt.is_stoc_min),'0N'))) AS piesa4 
FROM telefon as t LEFT JOIN
total_piese as tt
ON t.id = tt.id_telefon
LEFT JOIN culoare as c
ON c.id  = tt.id_culoare
LEFT JOIN piesa as p
ON p.id = tt.id_piesa
GROUP BY tt.Id, t.telefon, c.culoare
ORDER BY t.id;