建表
CREATE TABLE friend_request ( sender_id INT, send_to_id INT, request_date DATE ); CREATE TABLE request_accepted ( requester_id INT, accepter_id INT, accept_date DATE ); INSERT INTO friend_request VALUES(1, 2, '2016_06-01'); INSERT INTO friend_request VALUES(1, 3, '2016_06-01'); INSERT INTO friend_request VALUES(1, 4, '2016_06-01'); INSERT INTO friend_request VALUES(2, 3, '2016_06-02'); INSERT INTO friend_request VALUES(3, 4, '2016-06-09'); INSERT INTO request_accepted VALUES(1, 2, '2016_06-03'); INSERT INTO request_accepted VALUES(1, 3, '2016-06-08'); INSERT INTO request_accepted VALUES(2, 3, '2016-06-08'); INSERT INTO request_accepted VALUES(3, 4, '2016-06-09'); INSERT INTO request_accepted VALUES(3, 4, '2016-06-10');解题思路
接受请求的次数 / 申请总次数
SELECT ROUND( IFNULL( (SELECT COUNT(DISTINCT requester_id ,accepter_id) FROM request_accepted) / (SELECT COUNT(DISTINCT sender_id ,send_to_id) FROM friend_request) , 0),2) AS accept_rate;