mysql常用总结

1.左表自造列
select A.type, IFNULL(B.count, 0) success, IFNULL(C.count, 0) `read`, IFNULL(D.count, 0) `all`
from
(SELECT '终端' as type
    UNION select '产品'
    UNION select '流量包'
    UNION select '宽带'
    UNION select '其他') A
LEFT JOIN
(select type, COUNT(1) count from frequentrecommendrecord where accountName = #{accountName} and state = 2 GROUP BY type)B
on A.type = B.type
LEFT JOIN
(select type, COUNT(1) count from frequentrecommendrecord where accountName = #{accountName} and state = 1 GROUP BY type)C
on A.type = C.type
LEFT JOIN
(select type, COUNT(1) count from frequentrecommendrecord where accountName = #{accountName} GROUP BY type)D
on A.type = D.type

2.自定义排序
order by
case 
    when dishi='西安' then 1
    when dishi='咸阳' then 2
    when dishi='宝鸡' then 3
    when dishi='渭南' then 4
    when dishi='铜川' then 5            
    when dishi='延安' then 6
    when dishi='榆林' then 7
    when dishi='汉中' then 8
    when dishi='安康' then 9
    when dishi='商洛' then 10
    when dishi='合计' then 11
    when dishi='西城' then 12
    when dishi='西县' then 13
end

order by field(value,str1,str2,str3,str4,,,,,,strn)
其中value后面的参数自定义，不限制参数个数

3.批量插入
INSERT into tongjibaobiao_center(dishi, area, type, createTime)
select b.city, field6, field11, field12 from tongjibaobiao a
LEFT JOIN sys_account b on a.field3 = b.accountName

4.两条记录合并为一列 外呼系统（等同千店万员常客推荐） 类型一列 有重复号码记录
利用group_concat()方法，参数为需要合并的字段，合并的字段分隔符默认为逗号，可通过参数separator指定，该方法往往配合group by 一起使用。
select b.businessContext,group_concat(b.businessContext separator '-'), a.* from obs_business_progress_data a
LEFT JOIN obs_business_type b
on a.businessId = b.businessId
where a.staffPhone = '13509120356'
and isMarketing = 0
GROUP BY a.clientPhone

5.更新条件为子查询时 需要再包一层，Mysql无法直接使用。
UPDATE myd_vicecardregister SET isdelete = 1  where id in (SELECT c.* from (
SELECT a.id from myd_vicecardregister a
INNER JOIN phone_temp b ON a.vicecard1 = b.phone) c);

6.
<insert id="TargetUser" parameterType="map">
    load data local infile #{path} ignore into table myd_marketing_bottomtable IGNORE 1 LINES (marphone) 
    <set>
        marMark = #{marMark}, groupId = #{groupId},createOrder = #{createOrder},createTime = #{createTime},createUser = #{accountName},isDel = 0
    </set>
</insert>
load data local infile #{path} ignore into table myd_boss FIELDS TERMINATED BY ',' IGNORE 1 LINES (netPhone,netTcbm,netTcmc,netTime,netPrestore,scState,drcState,fkIsPayChanel) 

7.SUBSTRING_INDEX(accountName,'93',1)

8.删除重复
delete from myd_huoyue_register where id not in (select minid from (select min(id) as minid from myd_huoyue_register group by customerPhone) b);

9.连表修改
update myd_huoyue_register_temp a INNER JOIN (select accountName, dishi from sys_account) b
set a.dishi = b.dishi where a.userPhone = b.accountName

10 2字段去重思路
select * from 
(select id  from exam_score where type_id = 0) a
LEFT JOIN 
(select id from (select *, concat(accountName,manager) as __f from exam_score order by accountName desc) exam_score where type_id = 0  group by __f ) b
on a.id = b.id
where b.id is null