select user.id, user.realname 注册姓名, user.mobile 注册手机, act_address.name 收货姓名, act_address.mobile 收货手机, act_address.address 收货地址 from active.act_address join uranus.user on act_address.userId = user.id;

select `user`.id, `user`.mobile 手机, `user`.realName 姓名, luckDrawName 奖品, act_luckdrawprize.createDate 获奖时间 from `user` join `act_luckdrawprize` on act_luckdrawprize.userId = `user`.id and act_luckdrawprize.type = 'TWO';

select `user`.id, `user`.mobile 手机, `user`.realName 姓名, luckDrawName 奖品, act_luckdrawprize.createDate 获奖时间, addr.mobile 收货手机, addr.name 收货姓名, addr.address 收货地址 from `user` join `act_luckdrawprize` on act_luckdrawprize.userId = `user`.id and act_luckdrawprize.type = 'TWO' left join activity.act_address addr on addr.type = 'TWO' and addr.userId = user.id;

ALTER TABLE marketingtool ADD ruleType varchar(50) DEFAULT NULL COMMIT '规则类型';
UPDATE marketingtool t set t.ruleType="SIGN" WHERE t.tooltype="RULE_SIGN";
UPDATE marketingtool t set t.ruleType="TRANSACYION" WHERE t.tooltype="RULE_TRANSACYION"

SELECT grade, IFNULL(COUNT(1), 0) FROM (SELECT CASE WHEN old >= 0 AND old < 20 THEN '0' WHEN old >= 20 AND old < 30 THEN '1' WHEN old >= 30 AND old < 45 THEN '2' WHEN old >= 45 THEN '3' END AS grade FROM (SELECT year(curdate()) - if(length(idCard) = 18, substring(idCard, 7, 4), if(length(idcard) = 15, concat('19', substring(idCard, 7, 2)), NULL)) AS old FROM `user` WHERE idCard IS NOT NULL AND idCard != '') a) b GROUP BY grade order by grade;

| 10000.00 | 6 |
| 1000.00 | 90 |
| 2000.00 | 90 |
| 2000.00 | 90 |
| 6000.00 | 90 |
| 30000.00 | 182 |
| 10000.00 | 90 |
| 5000.00 | 62 |
| 5000.00 | 92 |
| 5000.00 | 365 |
| 8000.00 | 92 |

select sum(`order`.amount)*financing.term*1.0/365*0.008 from `order` join `product` on `order`.product_id = product.id and product.exclusive = 'JH_DY' and `order`.status = 'STATUS_PAID' join financing on financing.order_id = `order`.id and transProductId is null join invitation on invitee_id = `order`.user_id;

select inviter_id as '邀请人', invitee_id as '受邀者', amount as '购买金额', financing.term as '期限' from invitation join financing on financing.createDate >= '2016-09-16 00:00:00' and financing.createDate <= '2016-09-30 23:59:59' and transProductId is null and invitee_id = user_id join product p on product_id = p.id and left(exclusive, 2) != 'JH';

select inviter_id as '邀请人', invitee_id as '受邀者', amount as '购买金额', financing.term as '期限', financing.amount*0.008 as '所获佣金' from invitation join financing on financing.createDate >= '2016-09-16 00:00:00' and financing.createDate <= '2016-09-30 23:59:59' and transProductId is null and invitee_id = user_id join product p on product_id = p.id and left(exclusive, 2) != 'JH';

select inviter_id as '邀请人', invitee_id as '受邀者', amount as '购买金额', financing.term as '期限', financing.amount*financing.term/365*0.008 as '所获佣金' from invitation join financing on financing.createDate >= '2016-09-16 00:00:00' and financing.createDate <= '2016-09-30 23:59:59' and transProductId is null and invitee_id = user_id join product p on product_id = p.id and left(exclusive, 2) != 'JH' and invitation.category is null;

咦，小X你现在在做数据库这一块了？

你竟然？？？