select
count(*) as num,
YMD,
sum(if(H="00",1,null)) AS H00,
sum(if(H="01",1,null)) AS H01,
sum(if(H="02",1,null)) AS H02,
sum(if(H="03",1,null)) AS H03,
sum(if(H="04",1,null)) AS H04,
sum(if(H="05",1,null)) AS H05,
sum(if(H="06",1,null)) AS H06,
sum(if(H="07",1,null)) AS H07,
sum(if(H="08",1,null)) AS H08,
sum(if(H="09",1,null)) AS H09,
sum(if(H="10",1,null)) AS H10,
sum(if(H="11",1,null)) AS H11,
sum(if(H="12",1,null)) AS H12,
sum(if(H="13",1,null)) AS H13,
sum(if(H="14",1,null)) AS H14,
sum(if(H="15",1,null)) AS H15,
sum(if(H="16",1,null)) AS H16,
sum(if(H="17",1,null)) AS H17,
sum(if(H="18",1,null)) AS H18,
sum(if(H="19",1,null)) AS H19,
sum(if(H="20",1,null)) AS H20,
sum(if(H="21",1,null)) AS H21,
sum(if(H="22",1,null)) AS H22,
sum(if(H="23",1,null)) AS H23
from (
select ID,DATE_FORMAT(regtime, "%H") as H, DATE_FORMAT(regtime, "%Y-%m%-%d") as YMD from `fish`.`player_basic`
) t
GROUP by YMD
ORDER BY YMD DESC
查询处的num字段就是综述
select
YMD,
sum(if(H="00",1,null)) AS H00,
sum(if(H="01",1,null)) AS H01,
sum(if(H="02",1,null)) AS H02,
sum(if(H="03",1,null)) AS H03,
sum(if(H="04",1,null)) AS H04,
sum(if(H="05",1,null)) AS H05,
sum(if(H="06",1,null)) AS H06,
sum(if(H="07",1,null)) AS H07,
sum(if(H="08",1,null)) AS H08,
sum(if(H="09",1,null)) AS H09,
sum(if(H="10",1,null)) AS H10,
sum(if(H="11",1,null)) AS H11,
sum(if(H="12",1,null)) AS H12,
sum(if(H="13",1,null)) AS H13,
sum(if(H="14",1,null)) AS H14,
sum(if(H="15",1,null)) AS H15,
sum(if(H="16",1,null)) AS H16,
sum(if(H="17",1,null)) AS H17,
sum(if(H="18",1,null)) AS H18,
sum(if(H="19",1,null)) AS H19,
sum(if(H="20",1,null)) AS H20,
sum(if(H="21",1,null)) AS H21,
sum(if(H="22",1,null)) AS H22,
sum(if(H="23",1,null)) AS H23
from (
select ID,DATE_FORMAT(regtime, "%H") as H, DATE_FORMAT(regtime, "%Y-%m%-%d") as YMD from `fish`.`player_basic`
) t
GROUP by YMD
ORDER BY YMD DESC limit 100
后边加上limit 就是限制查询条数 例如上述的100条