所以我有下面的代码:

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


因为已经显示了Seattle,所以我不想看到第一行。但是,如果没有带有city和last_name的行,则应该只与城市对齐。

知道怎么做吗?

最佳答案

一点点分析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

关于mysql - 我想检查该值是否在上面重复。我该怎么办?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/58126816/

10-12 14:36