Converting from Oracle to mysql...
select
days.mdate ,
ifnull(login_cnt,0) as login_cnt ,
ifnull(logout_cnt,0) as logout_cnt
from ( select '2016-06-16' as mdate from ts_user where limit 1,1 ) days
left join ( select substr(vl_date,0,10) as mdate ,
sum(case when vl_login_yn = 'Y' then 1 else 0 end) login_cnt ,
sum(case when vl_login_yn != 'Y' then 1 else 0 end) logout_cnt
from
ts_visit_log
where substr(vl_date,0,10)
between ? and ?
group by substr(vl_date,0,10) ) log
on log.mdate = days.mdate
order by days.mdate asc
What the hell does this mean?
oracle mysql
select ...
from ( SUBQUERY1 ) mdate
left join ( SUBQUERY2 ) log
on log.mdate = days.mdate
order by days.mdate asc
select '2016-06-16' as mdate from ts_user where limit 1,1
Note: SUBQUERY1 doesn't have much meaning. There's a column called mdate, and the value is 2016-06-16 with one row.
select
substr(vl_date,0,10) as mdate ,
sum(case when vl_login_yn = 'Y' then 1 else 0 end) login_cnt ,
sum(case when vl_login_yn != 'Y' then 1 else 0 end) logout_cnt
from
ts_visit_log
where substr(vl_date,0,10) between ? and ?
group by substr(vl_date,0,10)
Note: SUBQUERY2 is SQL above.
The temporary table log, which calculates the number of user logins and user logouts within a specific period, and the temporary table mdate is signed, and [date, number of logins, number of logouts] is calculated and sorted by date.
© 2024 OneMinuteCode. All rights reserved.