删除错误发票SQL

    1. 查询详单
    2. select t3.statement_id,t3.mid,t1.merchant_name,t3.appkey,t3.soft_name,t3.income,t3.statement_status from settlement_statement_detail t3 left join settlement_statement t1 on t3.statement_id=t1.statement_id left join invoice t2 on t1.invoice_id=t2.id where  t1.statement_status=3 and t2.id='3591';
    4. 更新详单
    5. update settlement_statement_detail t3 left join settlement_statement t1 on t3.statement_id=t1.statement_id left join invoice t2 on t1.invoice_id=t2.id set t3.statement_status=1 where  t1.statement_status=3 and t2.id='3591';
    7. 查询结算单
    8. select * from settlement_statement t1 left join invoice t2 on t1.invoice_id=t2.id where statement_status=3 and t2.id='3591';
    10. 更新结算单
    11. update settlement_statement t1 left join invoice t2 on t1.invoice_id=t2.id set invoice_id='0',statement_status=1 where statement_status=3 and t2.id='3591';
    13. 查询发票
    14. select * from invoice where id='3591';
    16. 删除发票
    17. delete from invoice where id='3591';

    某款游戏收入 按照商户排序

    1. SELECT IF(t3.name IS NULL,'渠道','商户') AS is_merchant,IF(t3.name IS NULL,t2.channel_id,t3.name) AS mcname,t1.soft_name,
    2. SUM(t1.user_pay/100) AS user_pay,SUM(t1.new_user_cnt) AS new_user_cnt
    3. FROM stats_data AS t1 FORCE INDEX (idx_date_soft_id) JOIN channel_info AS t2 ON t2.channel_id = t1.channel_id LEFT JOIN merchant as t3 ON t2.mid = t3.mid
    4. WHERE t1.datetime >= '2016-03-27' AND t1.datetime <= '2016-04-08'
    5. AND t1.soft_id = '3247379'
    6. AND t2.type = 2 AND t2.assigned_team = 'jiyichang' AND t2.status = 0
    7. AND (t1.user_pay > 0 OR t1.new_user_cnt > 0)
    8. AND t3.name != ''
    9. GROUP BY mcname
    10. ORDER BY is_merchant,user_pay DESC;

    大厅每月新增（qid去重） 频率：每月

    1. select COUNT(qid),SUBSTR(t1.fl_date,1,7) from (select qid,MIN(login_date) fl_date from first_login_detail_qid_channel_appkey where channel like 'gu\_%' group by qid having MIN(login_date)>='2015-01-01') t1 group by SUBSTR(t1.fl_date,1,7);

    统计助手激活和下载数据

    1. select t2.assigned_user,sum(new_user_cnt),SUM(t1.j_download_cnt) AS j_download_cnt,datetime from channel_summary_data as t1 left join channel_info as t2 on t1.channel_id=t2.channel_id where t2.status=0 and (t2.channel_id like 'sjzs\_%' or t2.channel_id like '45%' or t2.channel_id like '47%') and t2.assigned_user in ('zhangxiaotao', 'wuyuzhou', 'zhoushixin', 'wuyijing', 'wangshaofeng-bd') and datetime>='2014-10-01' and datetime<='2014-10-07' group by t2.assigned_user,datetime;

    ????

    1. /* 以下两条数量相加才是全量的最后结果 */
    3. /* 统计全量有效数据 分两部分统计 sub_type 为1、2时需要乘5倍  */
    4. select t2.assigned_user,SUM(t1.download_cnt) AS download_cnt,SUM(GREATEST(t1.j_download_cnt, t1.new_user_cnt*5))  AS j_download_cnt,datetime from channel_summary_data as t1 left join channel_info as t2 on t1.channel_id=t2.channel_id where t2.type=2 and t2.assigned_team='jiyichang' and  t2.status=0 and t2.sub_type in ('1','2') and datetime>='2014-10-01' and datetime<='2014-10-19' group by t2.assigned_user; 
    6. select t2.assigned_user,SUM(t1.download_cnt) AS download_cnt,SUM(t1.j_download_cnt)  AS j_download_cnt,datetime from channel_summary_data as t1 left join channel_info as t2 on t1.channel_id=t2.channel_id where t2.type=2 and t2.assigned_team='jiyichang' and  t2.status=0   and datetime>='2014-10-01' and datetime<='2014-10-19' and t2.sub_type not in ('1','2') group by t2.assigned_user;

    给多人添加相同数据库数据

    1. insert into report (name,username,type,params,create_time) select name,'specific_username',type,params,now() from report where id='19';

    多行转一行shell代码

    1. awk '{print $1" "}' datas | tr -d '\n' 
    2. awk '{print "\""$1"\","}' datas | tr -d '\n' 
    3. awk '{print "'\''"$1"'\'',"}' datas | tr -d '\n' 
    4. awk '{print $1}' datas | sort | uniq | awk '{print "'\''"$1"'\'',"}' | tr -d '\n'
    5. awk '{print $1}' datas | sort | uniq | awk '{print $1" "}' | tr -d '\n'

    运行PHP脚本时，交互命令

    1. fwrite(STDOUT, 'free text what you want to say:');
    2. $data = trim(fgets(STDIN));

    按月出数据？？？

    1. select DATE_FORMAT(datetime, '%x-W%v-1') as startDate, DATE_FORMAT(datetime, '%x-W%v-7') as endDate, sum(user_pay)/100,sum(pay_user_cnt) from channel_summary_data as t1 left join channel_info as t2 on t1.channel_id = t2.channel_id where t2.status='0' and type='2' and payment_method in ('2','6') group by startDate;

    找出不在数据里面的数据

    1. 设数据源中一部分存在数据库，一部分不在数据库
    2. 先找出数据库有的部分，合并原数据，统计每个唯一ID存在个数，等于1则为不在数据库中
    3. sort temp.txt | uniq -c | sort -k1 | awk '{if($1 < 2) print $2}'

    430000分发充值数据

    1. select datetime,channel_id,download_cnt,round(user_pay/100, 2) from channel_summary_data where channel_id>='430000' and channel_id<='439999' and datetime>='2015-07-01' and datetime<='2015-09-30' and length(channel_id)=6 order by datetime desc;
    3. select datetime,channel_id,download_cnt,round(user_pay/100, 2) from channel_summary_data where channel_id>='430000000' and channel_id<='439999999' and datetime>='2015-07-01' and datetime<='2015-09-30' and length(channel_id)=9 order by datetime desc;
    5. select datetime,channel_id,download_cnt,round(user_pay/100, 2) from channel_summary_data where channel_id>='660000' and channel_id<='660099' and datetime>='2015-07-01' and datetime<='2015-09-30' and length(channel_id)=6 order by datetime desc;
    7. select datetime,channel_id,download_cnt,round(user_pay/100, 2) from channel_summary_data where channel_id>='496000' and channel_id<='496199' and datetime>='2015-07-01' and datetime<='2015-09-30' and length(channel_id)=6 order by datetime desc;