时间戳

首页 > 解决方案 > Im looking to filter my query to get all names with the relation of ID

java - Im looking to filter my query to get all names with the relation of ID

问题描述

I want to have this output from my query:

CODE  NAME  MANAGER
 1    John  Ely
      Paul
      Elsa  Anahid
 2    Raul  Ely
      Rosa  Paul
      Ahmad

How can I order in this way?

John,Paul and Elsa belogns to CODE 1  
Raul,Rosa Ahmad  belong to CODE 2

I tried but i obtain this:

CODE  NAME  MANAGER
 1    John  Ely
 1    Paul
 1    Elsa  Anahid
 2    Raul  Ely
 2    Rosa  Paul
 2    Ahmad

OR this:

CODE  NAME  MANAGER
 1    John  Ely
 2    Rosa  Paul

The query:

SELECT ad.CODE,em.NAME,em.MANAGER
FROM employess em INNER JOIN
     address ad 
     ON ad.CODE = em.CODE 
GROUP BY ad.CODE;

标签： javamysqlsql

解决方案

正如 Gordon Linoff 建议的那样，在表示层进行修改确实更好。

但是，也可以使用 SQL 来实现。

SELECT
  IF(
    em.NAME = (select ems.NAME from employees ems where ems.CODE = em.CODE order by em.NAME LIMIT 1),
    em.CODE,
    ''
    ) as CODE,
  em.NAME,
  em.MANAGER
FROM employess em
INNER JOIN address ad ON ad.CODE = em.CODE
ORDER BY em.CODE, em.NAME;

这仅在名称在表employess 中是唯一的情况下才有效，否则最好在子查询中比较uniq ID。是的，这个子查询给数据库增加了不必要的负载。因此，如果性能很重要 - 保持查询简单，并在 java 中处理 CODE 字段。

推荐阅读