我现在面临一个日常性的操作:
需要根据项目(project)名称,查找机器表(machine),并将查找的结果,插入到新表(blacklist)中。
为完成此功能,我创建了一个临时的表(blacklist_temp),先将查询放入其中,去重后再插入新表。
各表的结构,以及我编写的SQL语句如下:
需要根据项目(project)名称,查找机器表(machine),并将查找的结果,插入到新表(blacklist)中。
为完成此功能,我创建了一个临时的表(blacklist_temp),先将查询放入其中,去重后再插入新表。
各表的结构,以及我编写的SQL语句如下:
mysql> USE push;
Database changed
mysql> SHOW TABLES;
+-------------------------+
| Tables_in_push |
+-------------------------+
| blacklist |黑名单
| blacklist_temp |临时表
| machine |机器表
| project |项目表
+-------------------------+
mysql> DESC project;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |项目编号
| name | varchar(50) | YES | | NULL | |项目名称
| description | varchar(250) | YES | | NULL | |项目描述
| updateDate | datetime | YES | | NULL | |登记日期
+-------------+--------------+------+-----+---------+----------------+
mysql> DESC machine;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |机器标号
| imei | varchar(50) | NO | MUL | | |IMEI号
| imsi | varchar(50) | NO | MUL | | |IMSI号
| width | int(11) | NO | | 0 | |屏幕宽度
| height | int(11) | NO | | 0 | |屏幕高度
| manufacturer | varchar(64) | NO | | | |生产厂商
| model | varchar(32) | NO | | | |机型
| ip | varchar(32) | NO | | | |IP地址
| city | varchar(64) | NO | | | |所在城市
| project | int(11) | NO | | 0 | |项目号
| updateDate | datetime | YES | | NULL | |登记日期
+--------------+--------------+------+-----+---------+----------------+
mysql> DESC blacklist;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |编号
| imsi | varchar(50) | NO | UNI | NULL | |IMSI号
| mark | varchar(50) | YES | | NULL | |备注信息
+-------+-------------+------+-----+---------+----------------+
mysql> DESC blacklist_temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| imsi | varchar(50) | NO | PRI | NULL | |IMSI号
| mark | varchar(50) | YES | | NULL | |备注信息
+-------+-------------+------+-----+---------+-------+
#--清空临时表
DELETE FROM blacklist_temp;
#--根据项目名称,查找结果,并插入到临时表
INSERT INTO blacklist_temp
(SELECT DISTINCT m.imsi, p.name AS mark
FROM machine m, project p
WHERE m.project=p.id AND p.name like '%zhongyao_0515'
GROUP BY imsi
ORDER BY m.updateDate
LIMIT 100);
#--删除临时表中跟黑名单表中重复的记录项
DELETE blacklist_temp
FROM blacklist_temp,
(SELECT *
FROM (SELECT DISTINCT imsi FROM blacklist
UNION ALL
SELECT DISTINCT imsi FROM blacklist_temp) t
GROUP BY imsi having count(*)>1) temp ;
WHERE blacklist_temp.imsi = temp.imsi;
#--将临时表中的记录,插入到黑名单表中
INSERT INTO blacklist(imsi,mark)
SELECT * FROM blacklist_temp;