56,679
社区成员
发帖
与我相关
我的任务
分享
SELECT roomid,num,MIN(time) AS time FROM (
SELECT #T.roomid ,
#T.num ,
#T.time
FROM #T
JOIN ( SELECT CONVERT(NVARCHAR(13), [time]) AS [time] ,
roomid ,
MAX(num) AS num
FROM #T
GROUP BY CONVERT(NVARCHAR(13), [time]) ,
roomid
) t ON #T.num = t.num
AND CONVERT(NVARCHAR(13), #T.[time]) = t.time
AND #T.roomid = t.roomid)t
GROUP BY roomid,num
ORDER BY roomid,time
SELECT t.room_id,t.online,MIN(t.record_time) FROM(
SELECT tbl_user_online.room_id,tbl_user_online.online,tbl_user_online.record_time from tbl_user_online join
(SELECT DATE_FORMAT( `record_time` , '%Y-%m-%d %H') as record_time,room_id,MAX(online) as online
FROM tbl_user_online GROUP BY DATE_FORMAT( `record_time` , '%Y-%m-%d %H'),room_id
)t
on tbl_user_online.`online` = t.`online` and DATE_FORMAT( tbl_user_online.record_time , '%Y-%m-%d %H')=t.record_time and tbl_user_online.room_id = t.room_id)t
GROUP BY t.room_id,t.online
SELECT test.num,test.time from test join
(SELECT DATE_FORMAT( `time` , '%Y-%m-%d %H') as time,MAX(num) as num
FROM test GROUP BY DATE_FORMAT( `time` , '%Y-%m-%d %H')
)t
on test.num = t.num and DATE_FORMAT( test.time , '%Y-%m-%d %H')=t.time
SELECT test.roomid,test.num,test.time from test join
(SELECT DATE_FORMAT( `time` , '%Y-%m-%d %H') as time,roomid,MAX(num) as num
FROM test GROUP BY DATE_FORMAT( `time` , '%Y-%m-%d %H'),roomid
)t
on test.num = t.num and DATE_FORMAT( test.time , '%Y-%m-%d %H')=t.time and test.roomid = t.roomid