爱站程序员基地时间：2020年8月18日16:46:52
[code]select * from(-- 用户信息select * from(select u.id,from_unixtime(u.c_time),from_unixtime(u.last_login_time),ua.available_total_money,CASE WHEN p.is_novice = 1THEN up.moneyELSE 0 end new_investfrom sl_user uleft join sl_user_account ua on u.id = ua.user_idleft join sl_user_plan up on up.user_id = u.idleft join sl_plan p on p.id = up.plan_idwhere p.is_novice = 1 and up.status > -1) ddleft join-- 充值-投资金额(select aa.user_id,aa.top_money,bb.plan_money from (select user_id,sum(money) top_money from sl_top_upwhere status = 2 and pay_type=9group by user_id) aaleft join(select user_id,sum(money) plan_money from sl_user_planwhere status > -1group by user_id) bb on aa.user_id = bb.user_id)ee on dd.id = ee.user_id) qqright join-- 匹配(select user_id,sum(ten),sum(ten1),sum(ten2),sum(ten3),sum(ten4) from(select uc.user_id,uc.coupon_id,uc.money,uc.sn,up.money invest_money,CASE WHEN coupon_id = 16THEN up.moneyend ten,CASE WHEN coupon_id = 17THEN up.moneyend ten1,CASE WHEN coupon_id = 18THEN up.moneyend ten2,CASE WHEN coupon_id = 19THEN up.moneyend ten3,CASE WHEN coupon_id = 20THEN up.moneyend ten4from promote_user_coupon ucleft join sl_user_plan up on uc.sn = up.snwhere uc.is_avail= 2 and uc.coupon_id in (16,17,18,19,20))bb group by user_id)ww on qq.user_id = ww.user_id-- ------------------------------------------------------------------------ 1.红包select user_id,sum(ten),sum(ten1),sum(ten2),sum(ten3),sum(ten4) from(select uc.user_id,uc.coupon_id,uc.money,uc.sn,up.money invest_money,CASE WHEN coupon_id = 16THEN up.moneyend ten,CASE WHEN coupon_id = 17THEN up.moneyend ten1,CASE WHEN coupon_id = 18THEN up.moneyend ten2,CASE WHEN coupon_id = 19THEN up.moneyend ten3,CASE WHEN coupon_id = 20THEN up.moneyend ten4from promote_user_coupon ucleft join sl_user_plan up on uc.sn = up.snwhere uc.is_avail= 2 and uc.coupon_id in (16,17,18,19,20))bb group by user_id-- 2.投资select aa.user_id,aa.top_money,bb.plan_money from (select user_id,sum(money) top_money from sl_top_upwhere status = 2 and pay_type=9 and user_id in(select user_id from promote_user_coupon ucwhere uc.is_avail= 2 and uc.coupon_id in (16,17,18,19,20) group by user_id)group by user_id) aaleft join(select user_id,sum(money) plan_money from sl_user_planwhere status > -1group by user_id) bb on aa.user_id = bb.user_id-- ------------------------------------------------------ 3.信息select * from(select u.id,from_unixtime(u.c_time),from_unixtime(u.last_login_time),ua.available_total_moneyfrom sl_user uleft join sl_user_account ua on u.id = ua.user_idwhere u.id in(select user_id from promote_user_coupon ucwhere uc.is_avail= 2 and uc.coupon_id in (16,17,18,19,20) group by user_id))aaa left join(select aa.user_id,aa.top_money,bb.plan_money from (select user_id,sum(money) top_money from sl_top_upwhere status = 2 and pay_type=9 and user_id in(select user_id from promote_user_coupon ucwhere uc.is_avail= 2 and uc.coupon_id in (16,17,18,19,20) group by user_id)group by user_id) aaleft join(select user_id,sum(money) plan_money from sl_user_planwhere status > -1group by user_id) bb on aa.user_id = bb.user_id) bbb on aaa.id = bbb.user_idleft join(select user_id,sum(ten),sum(ten1),sum(ten2),sum(ten3),sum(ten4) from(select uc.user_id,uc.coupon_id,uc.money,uc.sn,up.money invest_money,CASE WHEN coupon_id = 16THEN up.moneyend ten,CASE WHEN coupon_id = 17THEN up.moneyend ten1,CASE WHEN coupon_id = 18THEN up.moneyend ten2,CASE WHEN coupon_id = 19THEN up.moneyend ten3,CASE WHEN coupon_id = 20THEN up.moneyend ten4from promote_user_coupon ucleft join sl_user_plan up on uc.sn = up.snwhere uc.is_avail= 2 and uc.coupon_id in (16,17,18,19,20))bb group by user_id) ccc on aaa.id = ccc.user_id
二、数据库重要字段加解密函数
[code]function mysql56_enc($cont){$key = \'eOQHzVkcHF67i3Q1\';return \" to_base64(AES_ENCRYPT(\'{$cont}\',\'{$key}\')) \";}function mysql56_dec($field, $as_field=\'\', $table=\'\'){if (empty($as_field)) {$as_field = $field;}if (!empty($table)) {$table = \"{$table}.\";}$key = \'eOQHzVkcHF67i3Q1\';//return \" CONVERT(AES_DECRYPT(from_base64({$field}),\'{$key}\') USING utf8) \";return \" CASE {$table}is_sens_data_enc WHEN 1 THEN CONVERT(AES_DECRYPT(from_base64({$table}{$field}),\'{$key}\') USING utf8) ELSE {$table}{$field} END as {$as_field} \";}// --function aes_pad($string, $blocksize = 16) {$len = $blocksize - (strlen($string) % $blocksize);$padding = str_repeat(chr($len), $len);return $string.$padding;}//生成二进制加密字符串function AES_ENCRYPT($str) {$key = \'eOQHzVkcHF67i3Q1\';return openssl_encrypt($str, \'AES-128-ECB\', $key);}//生成二进制加密字符串并base64function AES_ENCRYPT_to_base64($str){return AES_ENCRYPT($str);}//从二进制加密字符串转为正常字符function AES_DECRYPT($str){$key = \'eOQHzVkcHF67i3Q1\';return openssl_decrypt($str, \'AES-128-ECB\', $key);}//从base64加密字符串转为正常字符function AES_DECRYPT_from_base64($str){return AES_DECRYPT($str);}//根据加密显示数据function aes_show_sens_data($data, $is_sens_data_enc){if ($is_sens_data_enc==1) {return AES_DECRYPT_from_base64($data);}return $data;}
三：按照时间戳，分段数据库统计
[code]SELECT time_section, COUNT(*) AS COUNT FROM(SELECT @timeint := CAST(REPLACE(RIGHT(FROM_UNIXTIME(c_time), 8), \':\', \'\') AS UNSIGNED) AS timeint,CASEWHEN @timeint>=0 AND @timeint<10000 THEN 1WHEN @timeint>=10000 AND @timeint<20000 THEN 2WHEN @timeint>=20000 AND @timeint<30000 THEN 3WHEN @timeint>=30000 AND @timeint<40000 THEN 4WHEN @timeint>=40000 AND @timeint<50000 THEN 5WHEN @timeint>=50000 AND @timeint<60000 THEN 6WHEN @timeint>=60000 AND @timeint<70000 THEN 7WHEN @timeint>=70000 AND @timeint<80000 THEN 8WHEN @timeint>=80000 AND @timeint<90000 THEN 9WHEN @timeint>=90000 AND @timeint<100000 THEN 10WHEN @timeint>=100000 AND @timeint<110000 THEN 11WHEN @timeint>=110000 AND @timeint<120000 THEN 12WHEN @timeint>=120000 AND @timeint<130000 THEN 13WHEN @timeint>=130000 AND @timeint<140000 THEN 14WHEN @timeint>=140000 AND @timeint<150000 THEN 15WHEN @timeint>=150000 AND @timeint<160000 THEN 16WHEN @timeint>=160000 AND @timeint<170000 THEN 17WHEN @timeint>=170000 AND @timeint<180000 THEN 18WHEN @timeint>=180000 AND @timeint<190000 THEN 19WHEN @timeint>=190000 AND @timeint<200000 THEN 20WHEN @timeint>=200000 AND @timeint<210000 THEN 21WHEN @timeint>=210000 AND @timeint<220000 THEN 22WHEN @timeint>=220000 AND @timeint<230000 THEN 23WHEN @timeint>=230000 AND @timeint<240000 THEN 24END AS time_sectionFROM sl_user WHERE $where ) tmp GROUP BY time_section;-- 提现排名SELECT u.real_name, user_id, COUNT(*), SUM(money) AS draw_moneyFROM sl_withdraw_deposit_apply upLEFT JOIN sl_user u ON u.id = up.user_idWHERE withdraw_deposit_type = 9 AND up.`status` IN (3,4,6) AND user_id!=0 -- $whereGROUP BY user_idORDER BY draw_money DESCLIMIT 0, 20
— 谢星用户需求
— 1、用户id，来源channel，来源活动，是否佰仟员工，是否佰仟销售，被推荐人，注册地区，注册ip
字段：用户id，注册时间，身份证，注册平台(0:pc,1:Android，2:ios，3:wechat)，注册ip，等级0:内部员工；1:正常；2:创始会员；3：待定，
登陆次数，最后登陆时间，注册地区，邀请人id，渠道，活动，是否佰仟员工，时候销售
[code]select u.id,FROM_UNIXTIME(u.c_time),u.identification,u.platform,u.reg_ip,u.grade,u.login_total,FROM_UNIXTIME(u.last_login_time),ua.available_total_money,CONCAT(r.province,r.city) area,s.invite_user,c.channel_name,a.name,ui.is_baiqian,ui.is_safrom sl_user uleft join sl_user_region r on u.id = r.user_idleft join sl_user_summary s on u.id = s.user_idleft join sl_channel_invite ci on u.id = ci.be_invited_userleft join sl_channel c on ci.channel_id = c.idleft join promote_activity a on a.id = ci.activity_idleft join user_info ui on u.id = ui.user_idleft join sl_user_account ua on u.id = ua.user_idinto outfile \'/sql/userinfo20161201.csv\' fields terminated by \',\'optionally enclosed by \'\'lines terminated by \'\\r\\n\';
四：身份证，男女判断
[code]1、身份证男女SELECT name,mobile,identification,CASE substring(identification,17,1)%2 WHEN 1 THEN \'男\' ELSE \'女\' END AS 性别FROM `sl_sales_summary` where identification like \'362%\';
