所以我有下面的代码:
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/