计算几个日期的最大值 [英] Count the number of max values for several dates

问题描述

对于给定的用户，我想进行一次SQL查询，以返回该用户每天具有最佳价值的时间。

我使用MySQL。

For a given user, I want to make a SQL query that returns the number of time the user had the best value for each day.
I use MySQL.

-----------------------------
| User |    Date    | Value |
-----------------------------
| John | 2013-04-03 | 57    |
| John | 2013-04-04 | 13    |
| John | 2013-04-05 | 102   |
| John | 2013-04-06 | 64    |
| Bob  | 2013-04-03 | 8     |
| Bob  | 2013-04-04 | 83    |
| Bob  | 2013-04-05 | 44    |
| Bob  | 2013-04-06 | 101   |
| Paul | 2013-04-03 | 22    |
| Paul | 2013-04-04 | 72    |
| Paul | 2013-04-05 | 50    |
| Paul | 2013-04-06 | 15    |
-----------------------------

示例：

John：2次（2013-04-03& 2013-04-05）

鲍勃：2次（2013-04-04& 2013-04-06）

保罗：0次

Example :
John : 2 times (2013-04-03 & 2013-04-05)
Bob : 2 times (2013-04-04 & 2013-04-06)
Paul : 0 time

我不知道我必须使用哪些函数（ COUNT（*）？ SUM（*）？ MAX（Value）？ GROUP BY 吗？）。

如何为每个日期循环并求和？

I don't know which functions I have to use (COUNT(*) ? SUM(*) ? MAX(Value) ? GROUP BY ?).
How can I make a loop for each date and make the sum ?

推荐答案

由于您没有提到您正在使用的RDBMS，因此以下查询将对几乎所有 RDBMS起作用：

Since you have not mentioned the RDBMS you are using, here's a query that will work on almost all RDBMS:

SELECT  aa.User, COUNT(bb.User ) TotalCount
FROM    (SELECT DISTINCT User FROM TableName) aa
        LEFT JOIN
        (
            SELECT  a.User
            FROM    TableName a
                    INNER JOIN
                    (
                        SELECT  Date, MAX(Value) max_val
                        FROM    TableName
                        GROUP   BY Date
                    ) b ON  a.Date = b.Date AND
                            a.Value = b.max_val
        ) bb ON aa.User = bb.User 
GROUP   BY aa.User

• SQLFiddle演示


• SQLFiddle Demo

输出

╔══════╦════════════╗
║ USER ║ TOTALCOUNT ║
╠══════╬════════════╣
║ Bob  ║          2 ║
║ John ║          2 ║
║ Paul ║          0 ║
╚══════╩════════════╝

这篇关于计算几个日期的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！