常用函数sql

SELECT
f_pile.sid AS '站ID',
f_pile_site.sname AS '站名称', 
sum((case when f_order_settlement.sn is not null then f_order.useele else f_order.useele end)) AS '充电度数',
sum((case when f_order_settlement.sn is not null then f_order_settlement.ele_amount else f_order.ele_price end)) AS '电费收入',
sum((case when f_order_settlement.sn is not null then f_order_settlement.service_amount else f_order.server_price end)) AS '服务费',
sum((case when f_order_settlement.sn is not null then f_order_settlement.total_amount else f_order.ele_price + f_order.server_price end)) AS '总电费' ,
count(f_order.sn) as '总订单量',
DATE_FORMAT(f_order.createtime,'%Y%m') 月份
FROM
f_order
LEFT JOIN f_user ON f_order.uid = f_user.uid 
LEFT JOIN f_pile ON f_pile.pilenum = f_order.pid
LEFT JOIN f_pile_site ON f_pile_site.sid = f_pile.sid
left join f_order_settlement on f_order_settlement.sn=f_order.sn
WHERE
f_order.ostate in (1)
and f_order.createtime>='2018-10-01'
and f_order.createtime<'2019-03-01'
group by f_pile.sid,月份

select * from (SELECT pcui.user_name AS '真实姓名', concat('\t',pcui.id_card) AS '证件号码',pic.contract_id AS contractId, pic.contract_title AS '合同名称', pic.contract_num AS '合同编号',
pepi.project_num AS '项目编号', pic.contract_sign_time AS '签订时间', pic.entrust_operate_effective_time AS '运营生效时间', pic.entrust_operate_invalid_time AS '运营失效时间',
pic.warranty_start_time AS '质保开始时间', pic.warranty_end_time AS '质保结束时间',
cc.customer_id AS customerId, concat('\t',pcui.phone) as '手机号', pcui.postal_address AS '通讯地址', pcui.email as '电子邮箱', 
pic.buy_pile_num AS '购桩总台数', pic.total_money AS '总金额（元）', pic.operate_id AS operateId, 
pic.update_id AS updateId, pic.create_time AS createTime, pic.update_time AS updateTime, case pic.del_status when 2 then '是' else '否' end AS '合同作废', 
case IF( pic.entrust_operate_invalid_time < now(), 2, 1 ) when 2 then '失效' else '有效' end AS '质保',
case IF( pic.warranty_end_time < now(), 2, 1 ) when 2 then '失效' else '有效' end AS '委托营运', 
if(pepi.project_num is NULL,2,1) AS projectStatus, 
case if(ppi.address is null,2,1) when 2 then '无' else '有' end AS '项目地址',
case if(pcp.electric_pile_id is null,2,1) when 2 then '无' else '有' end AS '合同桩',
ppi.project_name AS '项目名称', concat('\t',pepi.pile_num_one) AS '桩号1', concat('\t',pepi.pile_num_two) AS '桩号2' FROM p_contract_info pic 
left join (SELECT contract_id, electric_pile_id,del_status from p_contract_pile GROUP BY id) pcp on pic.contract_id = pcp.contract_id 
left join p_electric_pile_info pepi on pcp.electric_pile_id = pepi.electric_pile_id 
LEFT JOIN p_contract_customer cc ON pic.contract_id = cc.contract_id LEFT JOIN p_customer_info pcui ON cc.customer_id = pcui.customer_id LEFT JOIN p_bankcard_info pb 
ON cc.bankcard_id = pb.bankcard_id LEFT JOIN p_project_info ppi ON ppi.project_num = pepi.project_num ) as contractInfo ORDER BY '合同作废' ASC

select '峰',sum(useele) from f_order 
left join f_pile on f_pile.pilenum=f_order.pid
left join f_pile_site on f_pile_site.sid=f_pile.sid
where f_order.createtime>='2019.5.13'
and f_order.createtime<'2019.5.20'
and f_pile_site.provinceid=510000 and f_pile_site.sstate=1 and f_pile_site.sid<>79
and (
(DATE_FORMAT(f_order.createtime,'%H:%i:%s')>='07:00:00' and DATE_FORMAT(f_order.createtime,'%H:%i:%s')<'11:00:00')
or (DATE_FORMAT(f_order.createtime,'%H:%i:%s')>='19:00:00' and DATE_FORMAT(f_order.createtime,'%H:%i:%s')<'23:00:00')
)union all
select '谷',sum(useele) from f_order 
left join f_pile on f_pile.pilenum=f_order.pid
left join f_pile_site on f_pile_site.sid=f_pile.sid
where f_order.createtime>='2019.5.13'
and f_order.createtime<'2019.5.20' 
and f_pile_site.provinceid=510000 and f_pile_site.sstate=1 and f_pile_site.sid<>79
and (
(DATE_FORMAT(f_order.createtime,'%H:%i:%s')>='00:00:00' and DATE_FORMAT(f_order.createtime,'%H:%i:%s')<'07:00:00')
or (DATE_FORMAT(f_order.createtime,'%H:%i:%s')>='23:00:00' and DATE_FORMAT(f_order.createtime,'%H:%i:%s')<='23:59:59')
)
union all
select '平',sum(useele) from f_order 
left join f_pile on f_pile.pilenum=f_order.pid
left join f_pile_site on f_pile_site.sid=f_pile.sid
where f_order.createtime>='2019.5.13'
and f_order.createtime<'2019.5.20' 
and f_pile_site.provinceid=510000 and f_pile_site.sstate=1 and f_pile_site.sid<>79
and (
(DATE_FORMAT(f_order.createtime,'%H:%i:%s')>='11:00:00' and DATE_FORMAT(f_order.createtime,'%H:%i:%s')<'19:00:00')
);

SELECT
f_pile.sid AS '站ID',
ps.province AS '省份',
f_pile_site.sname AS '站名称', 
sum((case when f_order_settlement.sn is not null then f_order.useele else f_order.useele end)) AS '充电度数',
sum((case when f_order_settlement.sn is not null then f_order_settlement.ele_amount else f_order.ele_price end)) AS '电费收入',
sum((case when f_order_settlement.sn is not null then f_order_settlement.service_amount else f_order.server_price end)) AS '服务费',
sum((case when f_order_settlement.sn is not null then f_order_settlement.total_amount else f_order.ele_price + f_order.server_price end)) AS '总电费' ,
count(f_order.sn) as '总订单量',
DATE_FORMAT(f_order.createtime,'%Y%m') 月份
FROM
f_order
LEFT JOIN f_user ON f_order.uid = f_user.uid 
LEFT JOIN f_pile ON f_pile.pilenum = f_order.pid
LEFT JOIN f_pile_site ON f_pile_site.sid = f_pile.sid
left join f_order_settlement on f_order_settlement.sn=f_order.sn
LEFT JOIN provinces ps ON ps.provid = f_pile_site.provinceid
WHERE
f_order.ostate in (1) AND f_pile_site.provinceid != '510000'
and f_order.createtime>='2019-01-01'
and f_order.createtime<'2019-08-31'
group by f_pile.sid,月份,省份;

查询有充电中，但是结算为0的数据 

select * from f_order where EXISTS(select 1 from f_order_settlement where total_amount=0 and f_order.sn=f_order_settlement.sn) and
createtime>='2019-10-01' and EXISTS(select 1 from charge_data_temp where useele>0 and f_order.oid=charge_data_temp.oid)