MySQL同表子查询
我有一张从客户那里收取付款的表。我想从为每个客户付款的前 3 位客户那里获取信息。
下面的查询获取每个客户一天中每小时的总付款。
SELECT
hour(received) as hr,
login,
count(*) as total
FROM
payment
WHERE
received >= date("2011-02-24")
AND
received <date("2011-02-25")
AND
PAYMENT_BATCH_FILE_ID is null
GROUP BY
hr,
client_id
ORDER BY
hr,
total DESC
下面的查询获取每个客户在一小时内排名前 3 的客户
SELECT
hour(received) as hr,
login,
count(*) as total
FROM
payment
WHERE
received >=date("2011-02-24")
AND
received < date("2011-02-25")
AND
PAYMENT_BATCH_FILE_ID is null
AND
hour(received)="3"
GROUP BY
hr,
client_id
ORDER BY
hr,
total DESC
LIMIT 3
如何结合这两个查询来获取每小时付款的排名前 3 的客户？
谢谢。
I have one table that collect payment from client.I want to get from top 3 client that make payment for each client.
The query below gets total payment for each client for each hour in a day.
SELECT
hour(received) as hr,
login,
count(*) as total
FROM
payment
WHERE
received >= date("2011-02-24")
AND
received <date("2011-02-25")
AND
PAYMENT_BATCH_FILE_ID is null
GROUP BY
hr,
client_id
ORDER BY
hr,
total DESC
The query below get top 3 client for each client in a hour
SELECT
hour(received) as hr,
login,
count(*) as total
FROM
payment
WHERE
received >=date("2011-02-24")
AND
received < date("2011-02-25")
AND
PAYMENT_BATCH_FILE_ID is null
AND
hour(received)="3"
GROUP BY
hr,
client_id
ORDER BY
hr,
total DESC
LIMIT 3
How to combine the both queries to get top 3 client that make payment for each hour?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是一个复杂的问题。请参阅此链接 http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
祝你好运！
This is a complicated problem. refer to this link http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
Good luck !