UPDATE
update gps_percent_201605_copy s set
newtran=(select DISTINCT v.tran_new from vehicle v where v.vincode=s.vin and left(v.areacode,2)=left(s.accesscode,2) and v.color=s.color LIMIT 0,1 ),
enter=(select DISTINCT v.enterid from vehicle v where v.vincode=s.vin and left(v.areacode,2)=left(s.accesscode,2) and v.color=s.color LIMIT 0,1 )
where newtran is null
聚合函数+GROUP BY
select b.cname areacode,a.tran,a.dateday,SUM(a.p90) p90,SUM(a.pall) pall,SUM(a.p90)/SUM(a.pall) lv from
(select areacode,tran,dateday,p90,(p90+p80+p70+p60+pl) pall from qualified_copy where tran is not null ) a ,
area b where a.areacode=b.city GROUP BY areacode,tran ORDER BY lv DESC
按字段去重，利用伪列ROWID
--删除重复数据-------------------------------------
-按照 省份编码 车牌号码 车牌颜色代码 去重
DELETE FROM YUNHUO_qg_20150326 a WHERE (a.province_code,a.vehicle_no, a.plate_color_code) IN ( SELECT province_code,vehicle_no, plate_color_code
FROM YUNHUO_qg_20150326
GROUP BY province_code,vehicle_no, plate_color_code
HAVING COUNT (0) > 1)
AND ROWID NOT IN ( SELECT max (ROWID) FROM YUNHUO_qg_20150326
GROUP BY province_code,vehicle_no, plate_color_code
HAVING COUNT (0) > 1);
commit;
查出一个excel
SELECT aa. province_code,SUM(CASE WHEN (aa.BUSINESS_STATE_CODE = '10') THEN 1 ELSE 0 END) sheet1,
SUM(CASE WHEN (aa.BUSINESS_STATE_CODE = '90') THEN 1 ELSE 0 END) sheet2,
SUM(CASE WHEN (aa.BUSINESS_STATE_CODE NOT IN
('10', '21', '22', '31', '32', '33', '34', '35', '80', '90')) THEN 1 ELSE 0 END) sheet3,
SUM(CASE WHEN (aa.BUSINESS_STATE_CODE IS NULL) THEN 1 ELSE 0 END) sheet4,
COUNT(0) 总量,
SUM(CASE WHEN (aa.approve_ton >= 12) THEN 1 ELSE 0 END) sheet5,
SUM(CASE WHEN (aa.approve_ton < 12 AND aa.approve_ton >0 ) THEN 1 ELSE 0 END) sheet6,
SUM(CASE WHEN (aa.approve_ton >=70 ) THEN 1 ELSE 0 END) sheet7,
SUM(CASE WHEN (aa.approve_ton >=12000 ) THEN 1 ELSE 0 END) sheet8,
SUM(CASE WHEN ((aa.APPROVE_TON = 0 OR aa.APPROVE_TON IS NULL) AND (aa.VEHICLE_TYPE_CODE NOT IN ('Q11', 'Q21', 'Q31', 'Q12', 'Q22', 'Q32') OR
aa.VEHICLE_TYPE_CODE IS NULL) ) THEN 1 ELSE 0 END) sheet9,
SUM(CASE WHEN (aa.vehicle_type_code in ('Q11','Q21','Q31')) THEN 1 ELSE 0 END) sheet10,
SUM(CASE WHEN (aa.vehicle_type_code in ('Q11','Q21','Q31') and aa.approve_ton >= 12) THEN 1 ELSE 0 END) sheet12,
SUM(CASE WHEN (aa.vehicle_type_code IS null ) THEN 1 ELSE 0 END) sheet13,
SUM(CASE WHEN ((aa.approve_ton >= 12 OR ((aa.APPROVE_TON = 0 OR aa.APPROVE_TON IS NULL) AND (aa.VEHICLE_TYPE_CODE NOT IN ('Q11', 'Q21', 'Q31', 'Q12', 'Q22', 'Q32') OR
aa.VEHICLE_TYPE_CODE IS NULL))) AND
(aa.vehicle_type_code in ('Q11','Q21','Q31') OR aa.vehicle_type_code IS null )
) THEN 1 ELSE 0 END)
from YUNHUO_qg_20180423 aa
GROUP BY aa. province_code
ORDER BY aa. province_code;
intersect求差集
to_date('2018-06-27 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from DIS_TRANS_CERT_20180627 where (PROVINCE_CODE,MCU_ID) in(
(select PROVINCE_CODE,MCU_ID from
(select * from DIS_TRANS_CERT_20180627 where (PROVINCE_CODE,MCU_ID) not in
(select b.PROVINCE_CODE,b.MCU_ID from ERR_VEHICLE_20180627 a,DIS_TRANS_CERT_20180627 b where b.PROVINCE_CODE=a.PROVINCE_CODE and a.MCU_ID=b.MCU_ID
and ( a.error like '%NV00008%'
or b.error like '%NVC0002%' or b.error like '%NVC0003%' or b.error like '%NVC0006%' or b.error like '%NVC0007%' or b.error like '%NVC0008%' or b.error like '%NVC0009%' or b.error like '%NVC0010%'
or a.error like '%IV00022%'
or b.error like '%IVC0002%' or b.error like '%IVC0003%' or b.error like '%IVC0004%' or b.error like '%IVC0006%' or b.error like '%IVC0007%' or b.error like '%IVC0008%'))
and CERTIFICATE_EXPIRE_DATE >= to_date('2018-06-27 00:00:00','yyyy-mm-dd hh24:mi:ss')
) where (BUSINESS_SCOPE_CODE like '%020%' or BUSINESS_SCOPE_CODE like '%021%' or BUSINESS_SCOPE_CODE like '%022%' or BUSINESS_SCOPE_CODE like '%023%'))
intersect
(
select PROVINCE_CODE,MCU_ID from
(select * from DIS_TRANS_CERT_20180627 where (PROVINCE_CODE,MCU_ID) not in
(select b.PROVINCE_CODE,b.MCU_ID from ERR_VEHICLE_20180627 a,DIS_TRANS_CERT_20180627 b where b.PROVINCE_CODE=a.PROVINCE_CODE and a.MCU_ID=b.MCU_ID
and ( a.error like '%NV00008%'
or b.error like '%NVC0002%' or b.error like '%NVC0003%' or b.error like '%NVC0006%' or b.error like '%NVC0007%' or b.error like '%NVC0008%' or b.error like '%NVC0009%' or b.error like '%NVC0010%'
or a.error like '%IV00022%'
or b.error like '%IVC0002%' or b.error like '%IVC0003%' or b.error like '%IVC0004%' or b.error like '%IVC0006%' or b.error like '%IVC0007%' or b.error like '%IVC0008%'))
and CERTIFICATE_EXPIRE_DATE >= to_date('2018-06-27 00:00:00','yyyy-mm-dd hh24:mi:ss')
) where (business_scope_code like '%030%' or business_scope_code like '%031%' or business_scope_code like '%032%' or business_scope_code like '%11000%' or business_scope_code like '%11100%' or business_scope_code like '%11101%'
or business_scope_code like '%11102%' or business_scope_code like '%11103%' or business_scope_code like '%11200%'
or business_scope_code like '%11201%' or business_scope_code like '%11202%' or business_scope_code like '%11203%')));
minus
select * from (select * from DIS_TRANS_CERT_20180627 where (business_scope_code like '%030%' or business_scope_code like '%031%' or business_scope_code like '%032%' or business_scope_code like '%11000%' or business_scope_code like '%11100%' or business_scope_code like '%11101%' or business_scope_code like '%11102%' or business_scope_code like '%11103%' or business_scope_code like '%11200%'
or business_scope_code like '%11201%' or business_scope_code like '%11202%' or business_scope_code like '%11203%'))
minus
(select * from (select * from DIS_TRANS_CERT_20180627 where (PROVINCE_CODE,MCU_ID) not in
(select b.PROVINCE_CODE,b.MCU_ID from ERR_VEHICLE_20180627 a,DIS_TRANS_CERT_20180627 b where b.PROVINCE_CODE=a.PROVINCE_CODE and a.MCU_ID=b.MCU_ID and ( a.error like '%NV00008%' or b.error like '%NVC0002%' or b.error like '%NVC0003%' or b.error like '%NVC0006%' or b.error like '%NVC0007%' or b.error like '%NVC0008%' or b.error like '%NVC0009%' or b.error like '%NVC0010%'
or a.error like '%IV00022%' or b.error like '%IVC0002%' or b.error like '%IVC0003%' or b.error like '%IVC0004%' or b.error like '%IVC0006%' or b.error like '%IVC0007%' or b.error like '%IVC0008%'))and CERTIFICATE_EXPIRE_DATE >= to_date('2018-06-27 00:00:00','yyyy-mm-dd hh24:mi:ss')) where (business_scope_code like '%030%' or business_scope_code like '%031%' or business_scope_code like '%032%' or business_scope_code like '%11000%' or business_scope_code like '%11100%' or business_scope_code like '%11101%'
or business_scope_code like '%11102%' or business_scope_code like '%11103%' or business_scope_code like '%11200%'
or business_scope_code like '%11201%' or business_scope_code like '%11202%' or business_scope_code like '%11203%'));
