请问mysql 这样的查询需要能否实现?

Question

数据库结构
帖子积分增加的记录表
id,tid,opint,create_time
1,1,1,1510390059
2,1,1,1510390059
3,1,3,1510390059
4,2,3,1510390059
5,2,5,1510390059
6,3,3,1510390059
7,3,2,1510390059
8,4,3,1510390059

类似这样,

需求:
获取create_time 为1小时内 piont增加最多的前3 的tid?

请问能否实现?

Answer 1

SELECT 
    tid
FROM
    record
WHERE
    create_time >= CURRENT_TIMESTAMP - 3600000
GROUP BY tid
ORDER BY SUM(point) DESC
LIMIT 3

如果用APIJSON，可以这样请求：

{
    "[]": {
        "Record": {
            "@column": "tid",
            "create_time{}": ">=CURRENT_TIMESTAMP-3600000",
            "@group": "tid",
            "@order": "SUM(point)-"
        },
        "count": 3
    }
}

然后服务器会返回：

{
    "[]": [
        {
            "Record": {
                "tid": 82001
            }
        },
        {
            "Record": {
                "tid": 82002
            }
        },
        {
            "Record": {
                "tid": 82003
            }
        }
    ],
    "code": 200,
    "msg": "success"
}

可以提取出tid：

{
    "Record-tid[]": {
        "Record": {
            "@column": "tid",
            "create_time{}": ">=CURRENT_TIMESTAMP-3600000",
            "@group": "tid",
            "@order": "SUM(point)-"
        },
        "count": 3
    }
}

然后服务器会返回：

{
    "Record-tid[]": [
        82001,
        82002,
        82003
    ],
    "code": 200,
    "msg": "success"
}

一个可直接在线测试的demo：
http://39.108.143.172/

{
    "Comment-userId[]": {
        "Comment": {
            "@column": "userId",
            "date{}": ">=CURRENT_TIMESTAMP-3600000",
            "@group": "userId",
            "@order": "SUM(momentId)-"
        },
        "count": 3
    }
}

用APIJSON，后端不用写接口和文档，前端/客户端 定制返回JSON的内容和结构^_^
https://github.com/TommyLemon...