如何从表中获取布尔列计数最高的记录的Id？

Question

我在MYSQL中有以下表，表名为a_ad_display with columns

╔═════════════╦═════════╦═════════╦═════════════════════╗
║ addisplayId ║ restId  ║ clicked ║     displaytime     ║
╠═════════════╬═════════╬═════════╬═════════════════════╣
║          1  ║  20707  ║      0  ║ 2013-02-28 10:50:55 ║
║          2  ║  20708  ║      1  ║ 2013-02-28 10:55:55 ║
║          3  ║  20711  ║      1  ║ 2013-03-01 12:50:55 ║
║          4  ║  20712  ║      1  ║ 2013-03-02 01:50:55 ║
║          5  ║  20707  ║      1  ║ 2013-03-03 10:50:55 ║
║          6  ║  20708  ║      1  ║ 2013-03-03 12:50:55 ║
║          7  ║  20707  ║      1  ║ 2013-03-04 09:50:55 ║
║          8  ║  20708  ║      1  ║ 2013-03-05 10:50:55 ║
║          9  ║  20708  ║      0  ║ 2013-03-06 11:50:55 ║
║         10  ║  20707  ║      0  ║ 2013-03-06 12:50:55 ║
╚═════════════╩═════════╩═════════╩═════════════════════╝

现在，我要获取的restId在列中单击了1的最高并发数

因此，从上面的记录中，20708个restId具有最高的点击列数，即3个..so，我想用这个scenario..so获取restId，谁能指导我，我该怎么做？

Answer 1

这是最简单的答案，但它不会处理重复项，

SELECT  restID, SUM(clicked) totalClick
FROM    a_ad_display
GROUP   BY restID
ORDER   BY totalClick DESC
LIMIT   1

下面是将保留副本的查询，

SELECT  restID, SUM(clicked) totalClick
FROM    a_ad_display
GROUP   BY restID
HAVING  SUM(clicked) = 
        (
            SELECT  SUM(clicked) totalClick
            FROM    a_ad_display
            GROUP   BY restID
            ORDER   BY totalClick DESC
            LIMIT   1
        )

• SQLFiddle Demo ()