php sql纯语句

条件语句CASE

CASE
    WHEN a.business_mark !='' THEN 2
END
    as source_type,

条件语句 CASE ELSE

CASE
        WHEN a.business_mark !='' THEN 2
        WHEN a.rebate_id !='' THEN 1
    ELSE 0
    END
        as source_type,

CASE a.is_pay
        when 1 then 20
        when 2 then 10
    end as 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)) *100
    ELSE CAST(a.price*0.93 AS DECIMAL(18,2)) *100
    end as actual_pay_to_receiver_amount,

子查询

case 
        when a.business_mark !='' then (select id from dl_business where mark=a.business_mark)
        when a.rebate_id !='' then a.rebate_id
    end 
        as 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

两表查询

select 
    a.member_id as user_id,
    a.game_level as division_id,
    a.add_time as time_create, 

    b.name as division_name
    

from dl_member_attest a
    left JOIN dl_game_type b ON a.game_level = b.id
limit 0,10

三表查询

SELECT 
    a.id as user_id,
    c.id as division_id,
    c.name as division_name,
    a.member_mark as hadchetman_sn,
    
    CASE
        WHEN b.is_status = 1 THEN 0
        WHEN b.is_status = 2 THEN 20
        WHEN b.is_status = 3 THEN 10
    end 
        AS hadchetman_state,

    b.add_time as time_create

    
    

from dl_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

limit 0,10

多个left join

SELECT 

    if(a.pay_status =1,null,from_unixtime(a.add_time)) as time_publish_user_pay,

    if(a.receive_time='',null,from_unixtime(a.receive_time)) as time_receive_user_pay,
    game_area.name as game_area_name,
  last_division.name as last_season_division_name,
  current_division.name as current_division_name,
  target_division.name as target_division_name,

    member.mobile as receive_user_name,
    0 as is_delete,
    1 as data_version 

from dl_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

limit 0,100