Mysql数据库身份证统计sql数据库加密等操作

最新推荐文章于 2023-11-13 10:41:29 发布

爱摄影的程序员。

最新推荐文章于 2023-11-13 10:41:29 发布

阅读量800

点赞数

分类专栏： Mysql专栏文章标签： sql 数据库 mysql python

本文链接：https://blog.csdn.net/php_xml/article/details/108081576

版权

这篇博客介绍了如何在MySQL数据库中对重要字段进行加解密操作，特别是在处理身份证信息时。同时，文章还涉及根据时间戳进行数据分段统计的方法，满足特定用户需求，包括用户ID、注册信息、身份证性别判断等。

摘要由CSDN通过智能技术生成

时间：2020年8月18日16:46:52


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 = 1
		THEN up.money
		ELSE 0 end new_invest
	from sl_user u
	left join sl_user_account ua on u.id = ua.user_id
	left join sl_user_plan up on up.user_id = u.id
	left join sl_plan p on p.id = up.plan_id
	where p.is_novice = 1 and up.status > -1
	) dd

	left join 

	-- 充值-投资金额
	(
	select aa.user_id,aa.top_money,bb.plan_money from (
	select user_id,sum(money) top_money from sl_top_up
	where status = 2 and pay_type=9 
	group by user_id) aa

	left join 

	(select user_id,sum(money) plan_money from sl_user_plan
	where status > -1 
	group by user_id) bb on aa.user_id = bb.user_id
	)ee on dd.id = ee.user_id
) qq

right 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 = 16
		THEN up.money
		end ten,
	CASE WHEN coupon_id = 17
		THEN up.money
		end ten1,
	CASE WHEN coupon_id = 18
		THEN up.money
		end ten2,
	CASE WHEN coupon_id = 19
		THEN up.money
		end ten3,
	CASE WHEN coupon_id = 20
		THEN up.money
		end ten4
	from promote_user_coupon uc
	left join sl_user_plan up on uc.sn = up.sn
	where 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 = 16
		THEN up.money
		end ten,
	CASE WHEN coupon_id = 17
		THEN up.money
		end ten1,
	CASE WHEN coupon_id = 18
		THEN up.money
		end ten2,
	CASE WHEN cou