求一个sql语句。想了一天都没想出来，特此来宝地求高手支招

[cdsun]

研究了一天也没研究出来该怎么写。求教各位大神一条sql


user表。用户表
id 用户id
registertime 注册时间（时间戳）

math_log用户记录表，每条记录存thisid和targetid，thisid代表发起方id，targetid代表应战方id
thisid和targetid对应 user表的用户id
mathtime 操作时间(时间戳)

想查询具体的某一天，当天注册的用户在当天的操作条数

比如要查询5月3日这天注册的用户，在5月3日这天，一盘没操作的用户有多少，当天只操作了一次的有多少，操作了2次的有多少，操作3-10次的有多少，操作了10次以上的用户条数有多少

select id from user where registertime>=unix_timestamp('2017-05-03 00:00:00') and registertime<=unix_timestamp('2017-05-03 23:59:59')
这样可以取出当天的所有用户id

这个id需要和targetid和thisid发生关系
比如thisid in (select id from user where registertime>=unix_timestamp('2017-05-03 00:00:00') and registertime<=unix_timestamp('2017-05-03 23:59:59')) and targetid in(select id from user where registertime>=unix_timestamp('2017-05-03 00:00:00') and registertime<=unix_timestamp('2017-05-03 23:59:59'))

然后怎么查询今天开了几次的用户有多少就不知道了。请各位高手帮帮忙。很着急。在线等

[cdsun]

有没有大神能帮忙给说说。目前我只能查询出来当天注册的用户的当天操作的日志记录。但是怎么去统计还没想到。求大神指点。我是这么计算的 select * from math_log where (thisid in (select id from user where registertime>=unix_timestamp('2017-05-03 00:00:00') and registertime<=unix_timestamp('2017-05-03 23:59:59')) and targetid in(select id from user where registertime>=unix_timestamp('2017-05-03 00:00:00') and registertime<=unix_timestamp('2017-05-03 23:59:59'))) and (mathtime>=unix_timestamp('2017-05-03 00:00:00') and mathtime>=unix_timestamp('2017-05-03 00:00:00')) 这样就可以吧这天注册的用户和这天注册的用户的操作日志给查询出来。可能比较low。但我们是后台用。效率不是太关注。主要是要准确

[二月十六]

试试这样：

SELECT USER.id,COUNT(1) FROM 
USER JOIN mathtime ON USER.id = mathtime.thisid OR USER.id = mathtime.targetid 
WHERE registertime>=unix_timestamp('2017-05-03 00:00:00') 
and registertime<=unix_timestamp('2017-05-03 23:59:59') 
AND mathtime>=unix_timestamp('2017-05-03 00:00:00') 
and mathtime<=unix_timestamp('2017-05-03 23:59:59') 
GROUP BY USER.id

[ACMAIN_CHM]

select id,registertime, (select count(*) from math_log where thisid=u.id or targetid=u.id and mathtime>=unix_timestamp('2017-05-03 00:00:00') and mathtime<=unix_timestamp('2017-05-03 23:59:59')) as 操作条数 from user u where registertime>=unix_timestamp('2017-05-03 00:00:00') and registertime<=unix_timestamp('2017-05-03 23:59:59')