SELECT directorID, firstname, lastname FROM director a WHERE EXISTS (SELECT 1 FROM direct b WHERE a.directorid = b.mvid AND EXISTS (SELECT 1 FROM movieinfo c WHERE b.mvid = c.mvid AND c.rating != 'PG' ) );
2. 找出打分(ranking)第二多的用户的用户名和被打分电影的数量 求写出查询语句 这个问题不好回答,打分第二多,是指打分次数第二多,还是指打分总分第二多,并列第二怎么算? 被打分电影的数量是不是要限定哪一天,或者哪个时间范围内。附:排名函数用法 ①ROW_NUMBER: Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。 ②DENSE_RANK: Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。 ③RANK: Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。SELECT DEPARTMENTID, EMPLOYEENAME, SALARY, RANK() OVER ( Partition BY DEPARTMENTID Order By SALARY DESC) "RANK", DENSE_RANK() OVER ( Partition BY DEPARTMENTID Order By SALARY DESC ) "DENSE_RANK", ROW_NUMBER() OVER( Partition BY DEPARTMENTID Order By SALARY DESC) "ROW_NUMBER" FROM EMPLOYEEINFO
我也是初学者,写的比较烂,希望大家多给些建议。 第二个: select username from (select username,count(score) count_s from ranking group by username) tt1 where tt1.count_s<(select max(count_s) from (select username,count(score) count_s from ranking group by username) tt1) and rownum=1;
-- Ranking(username*, mvID*, score, voteDate) WITH Ranking AS (SELECT 'zhangs001' AS username, 'mv001' AS mvid, TRUNC(dbms_random.value(1,10),0) AS score, sysdate - TRUNC(dbms_random.value(1,10),0) AS votedate FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual UNION ALL SELECT 'zhangs' || lpad(TRUNC(dbms_random.value(1,4),0),3,'0'), 'mv' || lpad(TRUNC(dbms_random.value(1,10),0),3,'0'), TRUNC(dbms_random.value(1,10),0) , sysdate - TRUNC(dbms_random.value(1,10),0) FROM dual ) --上面的语句是伪造的随机数据,可以使用有数据的表 SELECT d.*,--最后一层与自身联接查询,显示最终结果 e.mvlist FROM (--第三层,只显示排名为2的数据 SELECT * FROM (--第二层,获取排名 SELECT yearmonth, week, username, times , DENSE_RANK() over(Partition BY username Order By times DESC ) AS denserank FROM (--第一层,把原始数据转换为年月,周分组的数据 SELECT username, TO_CHAR(votedate,'yyyymm') AS yearmonth, TO_CHAR(votedate,'"第"w"周"') AS week, COUNT(1) AS times FROM ranking GROUP BY username, TO_CHAR(votedate,'yyyymm') , TO_CHAR(votedate,'"第"w"周"') ) b ) c WHERE denserank = 2 ) d, ( SELECT DISTINCT username, wm_concat(DISTINCT mvid)over(partition BY username) AS mvList FROM ranking ) e --不考虑时间,取USERNAME点评过的所有电影 WHERE d.username = e.username EARMONTH WEEK USERNAME TIMES DENSERANK MVLIST --------- ------- ------------------ ---------------------- ---------------------- ----------------------------- 201205 第2周 zhangs001 1 2 mv001,mv002,mv004,mv007 201205 第2周 zhangs002 3 2 mv001,mv002,mv003,mv008,mv009
Ranking(username*, mvID*, score, voteDate) *表示联合主键吗? 如果是的话 with aaaa as ( select count(*) no_,username,rownum rn from Ranking(username*, mvID*, score, voteDate) a gruop by username, order by no_ desc ) select * from aaaa where rn=2
firstname,
lastname
FROM director a
WHERE EXISTS
(SELECT 1
FROM direct b
WHERE a.directorid = b.mvid
AND EXISTS
(SELECT 1 FROM movieinfo c WHERE b.mvid = c.mvid AND c.rating != 'PG'
)
);
求写出查询语句
这个问题不好回答,打分第二多,是指打分次数第二多,还是指打分总分第二多,并列第二怎么算?
被打分电影的数量是不是要限定哪一天,或者哪个时间范围内。附:排名函数用法
①ROW_NUMBER:
Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
②DENSE_RANK:
Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
③RANK:
Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。SELECT DEPARTMENTID,
EMPLOYEENAME,
SALARY,
RANK() OVER ( Partition BY DEPARTMENTID Order By SALARY DESC) "RANK",
DENSE_RANK() OVER ( Partition BY DEPARTMENTID Order By SALARY DESC ) "DENSE_RANK",
ROW_NUMBER() OVER( Partition BY DEPARTMENTID Order By SALARY DESC) "ROW_NUMBER"
FROM EMPLOYEEINFO
第二个:
select username from (select username,count(score) count_s from ranking group by username) tt1 where tt1.count_s<(select max(count_s) from (select username,count(score) count_s from ranking group by username) tt1) and rownum=1;
WITH Ranking AS
(SELECT 'zhangs001' AS username,
'mv001' AS mvid,
TRUNC(dbms_random.value(1,10),0) AS score,
sysdate - TRUNC(dbms_random.value(1,10),0) AS votedate
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
UNION ALL
SELECT 'zhangs'
|| lpad(TRUNC(dbms_random.value(1,4),0),3,'0'),
'mv'
|| lpad(TRUNC(dbms_random.value(1,10),0),3,'0'),
TRUNC(dbms_random.value(1,10),0) ,
sysdate - TRUNC(dbms_random.value(1,10),0)
FROM dual
)
--上面的语句是伪造的随机数据,可以使用有数据的表
SELECT d.*,--最后一层与自身联接查询,显示最终结果
e.mvlist
FROM
(--第三层,只显示排名为2的数据
SELECT *
FROM
(--第二层,获取排名
SELECT yearmonth,
week,
username,
times ,
DENSE_RANK() over(Partition BY username Order By times DESC ) AS denserank
FROM
(--第一层,把原始数据转换为年月,周分组的数据
SELECT username,
TO_CHAR(votedate,'yyyymm') AS yearmonth,
TO_CHAR(votedate,'"第"w"周"') AS week,
COUNT(1) AS times
FROM ranking
GROUP BY username,
TO_CHAR(votedate,'yyyymm') ,
TO_CHAR(votedate,'"第"w"周"')
) b
) c
WHERE denserank = 2
) d,
( SELECT DISTINCT username,
wm_concat(DISTINCT mvid)over(partition BY username) AS mvList
FROM ranking
) e --不考虑时间,取USERNAME点评过的所有电影
WHERE d.username = e.username
EARMONTH WEEK USERNAME TIMES DENSERANK MVLIST
--------- ------- ------------------ ---------------------- ---------------------- -----------------------------
201205 第2周 zhangs001 1 2 mv001,mv002,mv004,mv007
201205 第2周 zhangs002 3 2 mv001,mv002,mv003,mv008,mv009
*表示联合主键吗?
如果是的话
with aaaa as
(
select count(*) no_,username,rownum rn
from Ranking(username*, mvID*, score, voteDate) a
gruop by username,
order by no_ desc
)
select *
from aaaa
where rn=2