mysql 建表时增加一个字段标识数据的更新时间

最新推荐文章于 2024-03-11 09:50:07 发布

时光不语,静待花开

最新推荐文章于 2024-03-11 09:50:07 发布

阅读量395

点赞数 1

分类专栏： sql 文章标签： sql

本文链接：https://blog.youkuaiyun.com/BD_fuhong/article/details/118732833

版权

sql 专栏收录该内容

11 篇文章

订阅专栏

需求:

报表开发时,想要每一份数据打上标识已识别数据的更新时间

解决:

 CREATE TABLE `rep_makeloan_day` 
 (dt date DEFAULT NULL  comment '统计日期',
 capital_no varchar(50) DEFAULT NULL  comment '资金端',
 order_num_history int  DEFAULT NULL  comment '历史累计放款笔数',
 order_count_history int  DEFAULT NULL  comment '历史累计放款总笔数',
 order_per_history double  DEFAULT NULL  comment '笔数占比',
 amount_history int  DEFAULT NULL  comment '历史累计放款金额',
 amount_sum_history int  DEFAULT NULL  comment '历史累计放款总金额',
 amount_per_history double  DEFAULT NULL  comment '金额占比',
 update_time timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP comment '更新时间'
 ) comment='教育分期-资金端放款统计' ;

update_time timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP comment '更新时间'

注意:

insert into 数据的时候,要写上需要的字段,否则源数据字段和目标数据字段数量对不上。

INSERT INTO mysql_federated_34_104.report_db.rep_makeloan_day (
	dt,
	capital_no,
	order_num_history,
	order_count_history,
	order_per_history,
	amount_history,
	amount_sum_history,
	amount_per_history
) SELECT
	cast(dt AS date) dt,
	cast(capital_no AS VARCHAR(50)) capital_no,
	cast(order_num_history AS INT) order_num_history,
	cast(order_count_history AS INT) order_count_history,
	cast(order_per_history AS DOUBLE) order_per_history,
	cast(amount_history AS INT) amount_history,
	cast(amount_sum_history AS INT) amount_sum_history,
	cast(amount_per_history AS DOUBLE) amount_per_history
FROM……