mysql 转移字段_Mysql数据迁移与类型转换

条件语句CASE

CASE

WHEN a.business_mark!='' THEN 2ENDas source_type,

条件语句 CASE ELSE

CASE

WHEN a.business_mark!='' THEN 2WHEN a.rebate_id!='' THEN 1ELSE0ENDas source_type,

CASE a.is_pay

when1 then 20when2 then 10endas recharge_state,

if语句

if(a.receive_time='',null,from_unixtime(a.receive_time)) as time_receive_user_pay,

如果receive_time为空，则取第二个值，否则就去第三个值

时间转化 (1477984283 转 Y-m-d H:i:s)

from_unixtime(a.add_time) as time_publish_user_pay,

字段长度截取 CAST(a.price*0.93 AS DECIMAL(18,2))

CASE

when a.rebate_id!=' ' OR a.business_mark !=' ' THEN CAST(a.price*0.85 AS DECIMAL(18,2)) *100ELSE CAST(a.price*0.93 AS DECIMAL(18,2)) *100endas actual_pay_to_receiver_amount,

子查询

casewhen a.business_mark!='' then (select id from dl_business where mark=a.business_mark)

when a.rebate_id!=''then a.rebate_id

endas source_id,

条件为空查询

SELECT * FROM `dl_order` where business_mark is not null;

SELECT* FROM `dl_order` where business_mark !='';

SELECT * FROM `dl_order` where business_mark is null;

select max(length(desc)) from table order by length desc

两表查询

selecta.member_idasuser_id,

a.game_levelasdivision_id,

a.add_timeastime_create,

b.nameasdivision_namefromdl_member_attest a

left JOIN dl_game_type b ON a.game_level=b.id

limit0,10

三表查询

SELECT

a.idasuser_id,

c.idasdivision_id,

c.nameasdivision_name,

a.member_markashadchetman_sn,

CASE

WHEN b.is_status= 1 THEN 0WHEN b.is_status= 2 THEN 20WHEN b.is_status= 3 THEN 10end

AS hadchetman_state,

b.add_timeastime_createfromdl_member a

left JOIN dl_member_attest b ON a.id=b.member_id

INNER JOIN dl_game_type c ON b.game_level=c.id

limit0,10

多个left join

SELECTif(a.pay_status =1,null,from_unixtime(a.add_time)) astime_publish_user_pay,if(a.receive_time='',null,from_unixtime(a.receive_time)) astime_receive_user_pay,

game_area.nameasgame_area_name,

last_division.nameaslast_season_division_name,

current_division.nameascurrent_division_name,

target_division.nameastarget_division_name,

member.mobileasreceive_user_name,0 asis_delete,1 asdata_versionfromdl_order a

LEFT JOIN dl_order_lol b ON b.order_id=a.id

left JOIN dl_game_type game_area on b.game_area=game_area.id

left join dl_game_type last_division on b.game_start_level=last_division.id

left join dl_game_type current_division on b.game_start_level=current_division.id

left join dl_game_type target_division on b.game_start_level=target_division.id

left JOIN dl_member member on a.receive_member_id=member.id

ORDER BY a.id desc

limit0,100