– 查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部 select c.name,count(fc.film_id) from film f inner join film_category fc on f.film_id = fc.film_id inner join category c on fc.category_id = c.category_id where f.description like “%robot%” and c.category_id in (select category_id from film_category fc group by category_id having count(fc.film_id) >=5);
SELECT c.name, COUNT(fc.film_id) FROM (select category_id, COUNT(film_id) AS category_num FROM film_category GROUP BY category_id HAVING count(film_id)>=5) AS cc, film AS f, film_category AS fc, category AS c WHERE f.description LIKE ‘%robot%’ AND f.film_id = fc.film_id AND c.category_id = fc.category_id AND c.category_id=cc.category_id;
select c.name,count(fc.film_id) from (select category_id,count(film_id) as category_num from film_category group by category_id having count(film_id)>=5) as cc, film as f,film_category as fc,category as c where f.description like “%robot%” and f.film_id=fc.film_id and c.category_id=fc.category_id and c.category_id=cc.category_id;
– 不同客群的占比 create table customer (id int, Gp varchar(2), age int(3)); insert into customer values (1,“A”,29), (2,“B”,29), (3,“A”,29), (4,“B”,29), (5,“C”,29), (6,“D”,29); select * from customer; select count(GP)/(select count(*) from customer) “占比” from customer group by GP;
– 每天的审批通过及审批通过的平均申请金额 create table app (apply_date date , loan_no int, app_prin int(10), result char(2));
insert into app values (“2018-2-5”,111,1000,“Y”), (“2018-4-5”,112,1000,“N”), (“2018-5-5”,113,2000,“N”), (“2018-6-5”,114,3000,“Y”), (“2018-3-5”,115,1000,“N”), (“2018-9-5”,116,5000,“Y”), (“2018-9-5”,117,3000,“Y”);
insert into app values (“2018-2-5”,118,3000,“N”), (“2018-4-5”,119,1000,“Y”); select * from app;
select apply_date,count(result) c1, avg(app_prin) “平均金额” from app where result=“Y” group by apply_date;
select b.apply_date,ifnull(c1/c2,0) “每天通过率” ,ifnull(平均金额,0) “通过平均金额” from (select apply_date,count(result) c1, avg(app_prin) “平均金额” from app where result=“Y” group by apply_date) a right join (select apply_date,count(result) c2 from app group by apply_date) b on b.apply_date=a.apply_date order by b.apply_date;
