都有效。
性能
PerformanceId SingerId MovieId NumberofSongs
1 1 1 2
2 3 1 4
3 2 2 6
4 4 5 3
5 5 5 3
6 2 6 2
7 4 6 5
8 6 4 6
9 6 3 3
10 4 3 4
SingerId SingerName City DOB Gender
1 A Hyderabad 14-Apr-65 M
2 B Chennai 25-May-84 M
3 C Bangalore 14-Sep-78 F
4 D Hyderabad 17-Jan-70 M
5 E Hyderabad 18-Mar-87 F
6 F Bangalore 23-Aug-75 F
7 g hyderabad 17-Jan-70 M
MovieId MovieName ReleaseDate
1 AAA 12-Jan-15
2 BBB 19-Sep-12
3 CCC 23-Jul-10
4 DDD 06-Oct-01
5 EEE 08-Nov-05
6 FFF 18-Apr-99
7 GGG 07-Aug-12
我试过这个查询它不工作，我的查询是
select count(a.singer_id) as count_movie,
a.singer_name,
a.city,
a.dob,
a.gender
from singer a
inner join performance b on
a.singer_id=b.singer_id group by
a.singer_name,a.city,a.dob,a.gender
having count(a.singer_id)=0;
帮帮我
如果我正确理解了您的要求，我认为您可以通过左外连接实现这一点，如下所示:
with performance as (select 1 performanceid, 1 singerid, 1 movieid, 2 numberofsongs from dual union all
select 2 performanceid, 3 singerid, 1 movieid, 2 numberofsongs from dual union all
select 3 performanceid, 2 singerid, 2 movieid, 2 numberofsongs from dual union all
select 4 performanceid, 4 singerid, 5 movieid, 2 numberofsongs from dual union all
select 5 performanceid, 5 singerid, 5 movieid, 2 numberofsongs from dual union all
select 6 performanceid, 2 singerid, 6 movieid, 2 numberofsongs from dual union all
select 7 performanceid, 4 singerid, 6 movieid, 2 numberofsongs from dual union all
select 8 performanceid, 6 singerid, 4 movieid, 2 numberofsongs from dual union all
select 9 performanceid, 6 singerid, 3 movieid, 2 numberofsongs from dual union all
select 10 performanceid, 4 singerid, 3 movieid, 2 numberofsongs from dual union all
select 11 performanceid, 8 singerid, null movieid, 2 numberofsongs from dual),
singer as (select 1 singerid, 'A' singername, 'Hyderabad' city, to_date('14/04/1965', 'dd/mm/yyyy') dbo, 'M' gender from dual union all
select 2 singerid, 'B' singername, 'Chennai' city, to_date('25/05/1984', 'dd/mm/yyyy') dbo, 'M' gender from dual union all
select 3 singerid, 'C' singername, 'Bangalore' city, to_date('14/09/1978', 'dd/mm/yyyy') dbo, 'F' gender from dual union all
select 4 singerid, 'D' singername, 'Hyderabad' city, to_date('17/01/1970', 'dd/mm/yyyy') dbo, 'M' gender from dual union all
select 5 singerid, 'E' singername, 'Hyderabad' city, to_date('18/03/1987', 'dd/mm/yyyy') dbo, 'F' gender from dual union all
select 6 singerid, 'F' singername, 'Bangalore' city, to_date('23/08/1975', 'dd/mm/yyyy') dbo, 'F' gender from dual union all
select 7 singerid, 'g' singername, 'Hyderabad' city, to_date('17/01/1970', 'dd/mm/yyyy') dbo, 'M' gender from dual union all
select 8 singerid, 'H' singername, 'Chennai' city, to_date('23/02/1975', 'dd/mm/yyyy') dbo, 'M' gender from dual),
movie as (select 1 movieid, 'AAA' moviename, to_date('12/01/2015', 'dd/mm/yyyy') releasedate from dual union all
select 2 movieid, 'BBB' moviename, to_date('19/09/2012', 'dd/mm/yyyy') releasedate from dual union all
select 3 movieid, 'CCC' moviename, to_date('23/07/2010', 'dd/mm/yyyy') releasedate from dual union all
select 4 movieid, 'DDD' moviename, to_date('06/10/2001', 'dd/mm/yyyy') releasedate from dual union all
select 5 movieid, 'EEE' moviename, to_date('08/11/2005', 'dd/mm/yyyy') releasedate from dual union all
select 6 movieid, 'FFF' moviename, to_date('18/04/1999', 'dd/mm/yyyy') releasedate from dual union all
select 7 movieid, 'GGG' moviename, to_date('07/08/2012', 'dd/mm/yyyy') releasedate from dual)
select s.*
from singer s
left join performance p on (s.singerid = p.singerid)
where p.movieid is null;
SINGERID SINGERNAME CITY DBO GENDER
---------- ---------- --------- ---------- ------
8 H Chennai 23/02/1975 M
7 g Hyderabad 17/01/1970 M
无论性能表中的movieid是否为空(假设null值意味着歌手在某个不是电影的地方演唱)，
获取小于1的歌曲数量:
SELECT Performance.PerformanceId, Performance.singer_id, Performance.movie_id, Performance.nof_songs, Singer.singer_id AS Expr1, Singer.singer_name, Singer.City,
Singer.DOB, Singer.Gender, movies.movie_id AS Expr2, movies.movie_name, movies.release_date
FROM Performance INNER JOIN
Singer ON Performance.singer_id = Singer.singer_id INNER JOIN
movies ON Performance.movie_id = movies.movie_id
WHERE (Performance.nof_songs < 1)
获取歌手详细信息:
SELECT Singer.singer_id AS Expr1, Singer.singer_name, Singer.City, Singer.DOB, Singer.Gender
FROM Performance INNER JOIN
Singer ON Performance.singer_id = Singer.singer_id INNER JOIN
movies ON Performance.movie_id = movies.movie_id
WHERE (Performance.nof_songs < 1)
如果一个歌手不在表演表中，那么他就没有在任何电影中表演过:
SELECT * FROM Singer
WHERE SingerId NOT IN (SELECT SingerId FROM Performance)
试试这个
select *
from singer,
performance,
movie
where singer.singerId=performance.singer_id
and performance.movie_id <> movie.movie_id(+);
或
select *
from singer,
performance
where singer.singerId=performance.singer_id
and performance.movie_id NOT IN (select movie_id from movie);
它可能对你有帮助