熟悉一些常用的sql写法便于工作中快速导出数据，本文不涉及到业务，所以对表库做了名字的修改，仅提供一些用法的说明。
以下直接举例子并讲解
1 单表批量数据迁移
场景：日志迁移
具体实例：将test_log2日志表2的数据全部迁移到test_log1日志表1
sql：
INSERT INTO `xxx`.test_log1
(
`operate_account_id`,
`student_id`, `transfer_type`, `gmt_create`, `gmt_modify`
)
SELECT
operate_account_id AS operate_account_id
,student_id AS student_id
,1 AS transfer_type
,gmt_create AS gmt_create
,gmt_modify AS gmt_modify
FROM `xxx`.test_log2;
说明：数据量只有几十万的话，还是很轻松的，可以直接使用上面sql
2 联表批量更新数据
场景：tab2表的数据
具体实例：需要把tab2表的最新记录的insertTime更新到tab1的lastFollowTime
sql：
UPDATE `xxx`.tab1 t
INNER JOIN (
SELECT MAX(insertTime) AS insertTime,userId FROM `xx`.tab2
WHERE type!=2 OR (type=2 and (content is not null and content != "" )) GROUP BY userId
) f
ON t.UserId = f.userId
SET t.lastFollowTime = f.insertTime
WHERE f.insertTime is not null
AND (t.lastFollowTime IS NULL
OR DATE_FORMAT(f.insertTime, '%Y-%m-%d %H:%i:%s') < DATE_FORMAT(f.insertTime, '%Y-%m-%d %H:%i:%s')
);
说明：这里有两个小技巧
- 第一个是left join子查询里面用分组+max找出当前userId最新的那条记录(算是一个找最新记录的小技巧)
- 第二个是DATE_FORMAT(f.insertTime, '%Y-%m-%d %H:%i:%s')进行比较，因为`xx`.tab2的时间格式是这样的"2019-09-10 17:57:48.793"，所以“2019-09-10 17:57:48.793”会永远大于“2019-09-10 17:57:48”,为了过滤这些条件，就加了个DATE_FORMAT
3 多次left join联表导出数据
场景：查出某个时间段带有某些字段的报表数据
具体实例：查出2019/0.9/01至2019/10/01之间的数据，以下会说明一些查的技巧
sql：
SELECT
# S点
DATE_FORMAT(tc.start_date, "%Y/%m/%d %H:%i") AS "开始时间"
,DATE_FORMAT(date_add(tc.start_date, interval 1 hour), "%Y/%m/%d %H:%i") AS "结束时间"
# A点
,dep.name AS "部门"
# B点
,IF(aspc.payDate IS NOT NULL, "是", "否") AS "是否存在B点数据"
,IF(aspc.payDate IS NOT NULL, aspc.payDate, "") AS "B点数据对应时间"
FROM `xxxx`.`u_t_c_s` ut
INNER JOIN `xxxx`.`t_c_s` tc ON ut.t_c_s_id = tc.id
INNER JOIN `xxx`.userinfo ui ON ui.Id = tc.user_id
# 获得A点数据
INNER JOIN `xxxx`.account acc ON acc.userId = ui.Id
INNER JOIN
(
SELECT a2.name AS groupName,a1.id AS groupId FROM `xxxx`.`dept` a1,`xxxx`.`dept` a2
a1.pId = a2.id
) dep
ON dep.groupId = acc.deptId
# 获得B点数据
LEFT JOIN
(
SELECT MAX(IF(fitstDate IS NOT NULL, fitstDate, secondDate)) AS payDate, userId FROM `xxx`.table_b WHERE state=3 GROUP BY userId
) aspc
ON aspc.userId = ut.user_id
WHERE tc.start_date >= '2019-09-01 00:00:00' AND tc.start_date <= '2019-10-01 00:00:00'
AND tc.type IN (1, 2) AND tc.`status`=0 AND ut.`status`=0;
说明：上面sql使用了点技巧
- S点的技巧：DATE_FORMAT用法，可以直接获取当前时间一个钟后时间，用法见：
- A点技巧：获取A点数据采用了dept自己跟自己连接的方式来处理，使用pId父Id关联
- B点技巧：这里采用了跟"2 联表批量更新数据"类似的技巧，max+group by筛选出每一个userId对应最新的那条数据
额外：其中IINER JOIN是内连，必须要符合的条件(可用于筛选过滤条件),LEFT JOI是外连，用于关联一些可能存在的数据
4 按范围导出多个时间段的数量分布
范围也属于条件的一种，可用CASE..WHEN，或者用区分INTERVAL这个范围更简单，事例如下：
INTERVAL用法：
SELECT
INTERVAL(tmp.totalTime,5*60,10*60,20*60,30*60,40*60,50*60,60*60+1) AS TIME,
COUNT(*)
FROM
(
SELECT MAX(totalTime) AS totalTime,account FROM
`xxxx`.`tab_a`
WHERE
id IN (
# 这里是一系列的子查询
SELECT xx FROM XXXXXXXX
)
GROUP BY account
) tmp
GROUP BY TIME;
这里的步骤是：
- 红色部分子查询先用group by account 根据account分组，找出每个account对应的最大totalTime的那条记录
- 以上面作为结果集返回，然后使用INTERVAL划分区间，表示为如下：
- 区间为：(tmp.totalTime < 5*60)、(tmp.totalTime >= 5*60 && tmp.totalTime < 10*60)、(tmp.totalTime >= 10*60 && tmp.totalTime < 20*60)....................
- 然后COUNT(*)就可以把每个区间的数量统计出来
CASE..WHEN用法(作用与上面一致):
SELECT
CASE
totalTime
WHEN totalTime = 0 THEN
'0分钟'
WHEN 5>totalTime >= 0 THEN
'5分钟'
WHEN 10>totalTime >= 5 THEN
'10分钟'
WHEN 20>totalTime >= 10 THEN
'20分钟'
WHEN 30>totalTime >= 20 THEN
'30分钟'
WHEN 40>totalTime >= 30 THEN
'40分钟'
WHEN 50>totalTime >= 40 THEN
'50分钟'
WHEN 60>totalTime >= 50 THEN
'60分钟'
END AS time,
count(*)
FROM
(
SELECT MAX(totalTime) AS totalTime,account FROM
`xxx`.`tab_a`
WHERE
id IN (
SELECT MAX(totalTime) AS totalTime,account FROM
`xxxx`.`tab_a`
WHERE
id IN (
# 这里是一系列的子查询
SELECT xx FROM XXXXXXXX
)
GROUP BY account
)
)
GROUP BY
CASE
totalTime
WHEN totalTime = 0 THEN
'0分钟'
WHEN 5>totalTime >= 0 THEN
'5分钟'
WHEN 10>totalTime >= 5 THEN
'10分钟'
WHEN 20>totalTime >= 10 THEN
'20分钟'
WHEN 30>totalTime >= 20 THEN
'30分钟'
WHEN 40>totalTime >= 30 THEN
'40分钟'
WHEN 50>totalTime >= 40 THEN
'50分钟'
WHEN 60>totalTime >= 50 THEN
'60分钟'
END;
总结：mysql性能毕竟还是不够高，使用连接的时候并没有使用hash连接，就像有一次我写了一条很多left join的sql，结果使用mysql导出要半个钟左右，后改改用分布式的SQL查询引擎(Presto)，导出仅需十几秒，类似这些工具也可以多采用。
更多待续~~~~~~~
标签：totalTime,10,SQL,WHEN,60,mysql,写法,id,SELECT From： https://blog.51cto.com/u_13854513/7954273