mysql - 我想检查上面的值是否重复。我怎样才能做到呢？

Question

所以我有下一个代码:

SELECT distinct NVL( l.city, ' '), e.last_name || ' ' || e.first_name
FROM locations l
FULL JOIN departments d
    ON d.location_id = l.location_id
FULL JOIN employees e
    ON e.department_id = d.department_id 

输出:

NVL(L.CITY,'')      E.LAST_NAME||''||E.FIRST_NAME                 
------------------- ----------------------------------------------                                                              
1) Seattle                                                                      
2) Seattle             Kochhar Neena                                 
3) Oxford              Zlotkey Eleni                                 
4) Oxford              Abel Ellen                                    
5) Oxford              Vargas Jonathon                                                        
6) Oxford              Grovlin Gus       

如果有一行提到了这个城市，我希望它不显示只有城市而没有姓氏的行。例如，我有行

Seattle                   Kochhar Neena

因为西雅图已经显示，所以我不想看到第一行。但是，如果没有包含城市和姓氏的行，则应该有仅包含城市的行。

知道如何做吗？

Answer 1

一点点分析 SQL 就可以解决问题。

SQL> with your_data as
  2  (
  3  SELECT distinct
  4    l.city city,
  5    e.last_name || ' ' || e.first_name name
  6  FROM hr.locations l FULL JOIN
  7       hr.departments d ON d.location_id = l.location_id FULL JOIN
  8       hr.employees e ON e.department_id = d.department_id
  9  )
 10  select *
 11  from
 12  (
 13  select
 14    y.*,
 15    count(*) over ( partition by city ) as seq
 16  from your_data y
 17  )
 18  where seq = 1 or ( seq > 1 and name != ' ' )
 19  order by 1,2;

CITY                           NAME                                                  SEQ
------------------------------ ---------------------------------------------- ----------
Beijing                                                                                1
Bern                                                                                   1
Bombay                                                                                 1
Geneva                                                                                 1
Hiroshima                                                                              1
London                         Mavris Susan                                            1
Mexico City                                                                            1
Munich                         Baer Hermann                                            1
Oxford                         Abel Ellen                                             34
Oxford                         Ande Sundar                                            34
Oxford                         Banda Amit                                             34
Oxford                         Bates Elizabeth                                        34
Oxford                         Bernstein David                                        34

有关这些技术的完整教程请参见 https://www.youtube.com/watch?v=0cjxYMxa1e4&list=PLJMaoEWvHwFIUwMrF4HLnRksF0H8DHGtt