SQL刷题记录 求战友互相督促交流

577. Employee Bonus
select e.name, b.bonus
from employee e left join bonus b
on e.empid = b.empid
where b.bonus is null or b.bonus < 1000

1098. Unpopular Books (Unresolved 求助！！)
此题的坑应该在于过去一年销量为0（不在orders表里的）的书也需要输出，所以需要用books left join orders. 但是不知道为什么不能输出null行，结果不正确
SELECT b.book_id, b.name
FROM books b
LEFT JOIN orders o
ON b.book_id = o.book_id
WHERE b.available_from <= DATE_SUB('2019-06-23', INTERVAL 1 MONTH)  AND o.dispatch_date >= DATE_SUB('2019-06-23', INTERVAL 1 YEAR)
GROUP BY o.book_id
HAVING SUM(IFNULL(o.quantity, 0)) < 10

试过另一种日期筛法也是这个结果
TIMESTAMPDIFF(MONTH, b.available_from, '2019-06-23') >= 1 AND TIMESTAMPDIFF(YEAR, o.dispatch_date, '2019-06-23') <= 1

用subquery也不对
SELECT b.book_id, b.name
FROM books b
LEFT JOIN
(SELECT o.book_id
FROM orders o
WHERE o.dispatch_date > DATE_SUB('2019-06-23', INTERVAL 1 YEAR)
GROUP BY o.book_id
HAVING SUM(o.quantity) < 10
) temp
ON b.book_id = temp.book_id
WHERE b.available_from < DATE_SUB('2019-06-23', INTERVAL 1 MONTH)

1098. Unpopular Books (Unresolved 求助！！)
此题的坑应该在于过去一年销量为0（不在orders表里的）的书也需要输出，所以需要用books left join orders. 但是不知道为什么不能输出null行，结果不正确
SELECT b.book_id, b.name
FROM books b
LEFT JOIN orders o
ON b.book_id = o.book_id
WHERE b.available_from <= DATE_SUB('2019-06-23', INTERVAL 1 MONTH)  AND o.dispatch_date >= DATE_SUB('2019-06-23', INTERVAL 1 YEAR)
GROUP BY o.book_id
HAVING SUM(IFNULL(o.quantity, 0)) < 10

试过另一种日期筛法也是这个结果
TIMESTAMPDIFF(MONTH, b.available_from, '2019-06-23') >= 1 AND TIMESTAMPDIFF(YEAR, o.dispatch_date, '2019-06-23') <= 1

用subquery也不对
SELECT b.book_id, b.name
FROM books b
LEFT JOIN
(SELECT o.book_id
FROM orders o
WHERE o.dispatch_date > DATE_SUB('2019-06-23', INTERVAL 1 YEAR)
GROUP BY o.book_id
HAVING SUM(o.quantity) < 10
) temp
ON b.book_id = temp.book_id
WHERE b.available_from < DATE_SUB('2019-06-23', INTERVAL 1 MONTH)

578. Get Highest Answer Rate Question
/*answer rate = answer number’s ratio in show number*/
with answer as
(select question_id, ifnull(count(case when action = ‘answer’ then 1 else 0 end)/count(case when action = ‘show’ then 1 else 0), 0) as answer_rate from survey_log group by question_id)
select question_id as survey_log
from survey_log
where answer_rate = (select max(answer_rate) from answer)

碼一個～

来个简单的： Calculate the average comments for the users with >= 2 posts, and each post has comments greater or equal to 40

CREATE TABLE comments (
    name            varchar(80),
    posts           varchar(80),
    comments         int
);

INSERT INTO comments VALUES ('u1', 'page1', '90');
INSERT INTO comments VALUES ('u1', 'page2', '50');
INSERT INTO comments VALUES ('u1', 'page3', '40');
INSERT INTO comments VALUES ('u2', 'page2', '55');
INSERT INTO comments VALUES ('u2', 'page4', '45');
INSERT INTO comments VALUES ('u4', 'page4', '30');
INSERT INTO comments VALUES ('u4', 'page3', '40');
INSERT INTO comments VALUES ('u3', 'page2', '100');

SELECT name, AVG(comments)
FROM comments
WHERE comments >= 40
GROUP BY name
HAVING COUNT( DISTINCT posts) >=2

和楼主一起加油！

求朋友接受率，很疑惑的是为什么我只能单独的 求出 接受数量 和发出数量，如果简答的放在一起用WHERE就会出现接受数量变成了12 （其实应该是三）， 欢迎指正~
. Waral dи,CREATE TABLE Friends (
date DATE,
action_id INT,
target_id INT,
action_type VARCHAR(50)
);
DELETE FROM Friends;
INSERT INTO Friends VALUES
( '19-07-01', 1, 2, 'accept'),
( '19-07-01', 2, 1, 'request'),
( '19-07-02', 4, 2, 'unfriend'),
( '19-07-02', 1, 6, 'accept'),
( '19-07-03', 7, 1, 'request'),
( '19-07-03', 6, 1, 'request'),
( '19-07-04', 1, 5, 'accept'),
( '19-07-05', 5, 1, 'request');

SELECT sa/ sr FROM

(SELECT SUM(a.accept) sa FROM
(
SELECT a.action_id, COUNT(a.target_id) accept
FROM Friends a, Friends b
WHERE a.target_id = b.action_id
AND a.action_id = b. target_id
-baidu 1point3acres      AND a.action_type ='accept'
      AND b.action_type ='request'
GROUP BY a.action_id)a) aa

,
(SELECT SUM(request) sr FROM
(
SELECT action_id, COUNT(target_id) request
  FROM Friends
  WHERE action_type = 'request'
  GROUP BY action_id
)b) bb



..

前阵子实习略忙，又开学了，直逼找工。。。我又回来了，开始重刷hackerrank。先更一把sql中的pivot table做法
thanks to
https://stackoverflow.com/questions/7674786/mysql-pivot-table
https://stackoverflow.com/questi ... mns/9668036#9668036

SELECT
    CompanyName,  
    SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
    SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM
    Company
GROUP BY
    CompanyName

还可以用left join来做。注意如果考虑duplicate，可以用MAX代替SUM

. Waral dи,缺点是列数多了枚举起来很复杂，暂时想不到更好的办法