概述
mysql5.7.6之派生表合并(derived_merge = on)
外部查询分组导致子查询order by生效:可以通过在子查询中使用任何阻止合并的构造来禁用合并,如DISTINCT的。
批量更新手机号重复的记录只留下最新一条不更新
UPDATE
test_user vm4
SET
vm4.phone = vm4.mobile,vm4.mobile = NULL
WHERE vm4.mobile IN
(
SELECT
tmp2.mobile
FROM
(
SELECT vm5.mobile FROM test_user vm5 GROUP BY vm5.mobile HAVING COUNT(vm5.id)>1
) tmp2
)
AND vm4.id NOT IN
(
SELECT tmp.id FROM (
SELECT
vm3.*, MAX(vm3.registerDate) recently
FROM
(
SELECT DISTINCT (vm1.id) tt,vm1.* FROM test_user vm1 WHERE vm1.mobile IN
(
SELECT vm2.mobile FROM test_user vm2 GROUP BY vm2.mobile HAVING COUNT(vm2.id)>1
) ORDER BY vm1.registerDate DESC
) vm3 GROUP BY vm3.mobile
) tmp
)
最后
以上就是贤惠枫叶为你收集整理的mysql5.7.6之派生表合并的全部内容,希望文章能够帮你解决mysql5.7.6之派生表合并所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错,欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复