mysql的insert into from select 和update by select
来源:互联网 发布:思迅收银软件 编辑:程序博客网 时间:2024/05/21 22:54
从contact表生成dialog表的uid, contact_uid, last_msg_id,last_msg_time, total_msg_count:
insert into sns_user_00.user_dialog_00(uid,contact_uid,last_msg_time, last_msg_id, totle_msg_count) select uid,contact_uid, ctime as last_msg_time, msg_id as last_msg_id, count(*) astotal_msg_count from (select msg_id,uid, contact_uid, ctime, type from sns_user_00.user_contact_00 order by ctimedesc) `temp` group by uid, contact_uid orderby ctime desc;
上面语句实现了insert into select,其中包括查询每一个类别中时间最晚的记录,利用嵌套select
从contact表生成dialog表的last_recv_time:
insert intosns_user_00.user_dialog_00(uid, contact_uid, last_recv_time) select * from (select uid, contact_uid, ctime as recv_time from sns_user_00.user_contact_00where type=1 order by ctime desc ) `temp` group by uid,contact_uid order by recv_time desc on duplicate key update last_recv_time =values(last_recv_time);
上面语句实现了从结果集中去值然后update, 当然如果是多字段的话 last_recv_time = values(last_recv_time)可以写多个,以逗号隔开。
有时候可以利用mysql自身实现数据转移,不必从db导出为文本,然后再处理为新的插入语句,最后再执行一次导入。
- mysql的insert into from select 和update by select
- SELECT INTO 和 INSERT INTO SELECT 和 Update From的用法
- Mysql的INSERT INTO SELECT与SELECT INTO FROM
- Insert into select 和 select into from
- mysql select into和insert into select
- select into from 和 insert into from 的区别
- insert into from和select from into的区别
- insert .... select ... 和 select ...into from
- select into 和 insert into from的区别
- insert into select from
- insert into select from
- insert into select 和 select into from 用法和区别
- mysql select / insert into / from file
- INSERT INTO SELECT 和 SELECT INTO FROM 两种表复制语句
- Insert into select 和 select into from 区别
- INSERT INTO SELECT语句 和 SELECT INTO FROM语句
- select into from 和 insert into select 区别
- select into from 和 insert into select 区别
- 子衿的事(30)
- [函数与公式] 如何用函数公式实现双条件排名?
- 淘宝将推SNS产品加强用户粘度:24日正式上线
- smartform 中currency 字段的小数点问题
- 用Visual C#实现端口监听模拟即时通讯
- mysql的insert into from select 和update by select
- linux中shell变量
- 人体必须清除的12种毒素
- 仿Google翻译 中--->英
- select 控件js
- Fusioncharts的数字格式化
- null值判断的一个避免错误
- [Excel VBA] 如何根据单元格的值跳转到另一工作表有相同值的单元格?
- Android中Message机制的灵活应用