求帮忙解答 2条问题..万分感谢
1. select directorID, firstname,lastname from director where directorid in (select mvid from direct where mvid in(select mvid from movieinfo where rating != 'PG')); 这个转exists
2. 找出打分(ranking)第二多的用户的用户名和被打分电影的数量
求写出查询语句
下面是表
MovieInfo (mvID, title, rating, year, length, studio)
Director(directorID, firstname, lastname)
Member(username, email, password)
Actor(actorID, firstname, lastname, gender, birthplace)
Cast(mvID*, actorID*)
Direct(mvID*, directorID*)
Genre(mvID*, genre)
Ranking(username*, mvID*, score, voteDate)
谢谢
[解决办法]
1)
select * from director d where exists (select null from movieinfo a join Direct b on a.mvID = b.mvID where rating != 'PG' and b.directorID = d.directorID)
2)
select username, num from (select a.*, row_number() over (order by num desc) as rnum from (select username, count(username) as num from ranking group by username) a) b where rnum = 2
