mysql - 找出每个 SQL 中的百分比

Question

我这里有两张桌子，trip 和 users

我的问题是介于Oct 1, 2013 at 10am PDT和之间Oct 22, 2013 at 5pm PDT，每个城市每天取消被禁止的客户的请求的百分比是多少？

标签： mysql

Answer 1

此查询应该有效：

SELECT 
    A.usersid,
    A.email,
    ((SELECT 
            COUNT(B.id)
        FROM
            trips B
        WHERE
            B.client_id = A.usersid
                AND request_at BETWEEN '10/1/2013' AND '10/22/2013'
                AND status <> 0) / (SELECT 
            COUNT(C.id)
        FROM
            trips C
        WHERE
            C.client_id = A.usersid
                AND request_at BETWEEN '10-1-2013' AND '10-22-2013') * 100) AS 'PERCENT'
FROM
    users A
WHERE
    A.role = 0 AND banned = FALSE