59 lines
2.6 KiB
SQL
59 lines
2.6 KiB
SQL
select * FROM testdb.bike2025 a
|
||
|
||
|
||
|
||
|
||
select
|
||
right(a.手机号,11) 手机号,
|
||
SUBSTRING_INDEX(a.所属片区, '（', 1) 城区,
|
||
replace(replace(REGEXP_SUBSTR(a.所属片区, '（.*?）'),'（',''),'）','') 片区,
|
||
a.支付状态,
|
||
replace(a.订单费用,'元','') 订单费用,
|
||
a.开始时间,a.结束时间,
|
||
replace(a.骑行费用,'元','') 骑行费用,
|
||
replace(a.调度费,'元','') 调度费,
|
||
a.骑行时长,
|
||
replace(a.骑行距离,'公里','') 骑行距离,
|
||
a.骑行开始站点,a.骑行结束站点
|
||
FROM testdb.bike2025 a
|
||
|
||
|
||
select c.骑行站点ID,replace(c.站点地址,'null','') 站点地址 from
|
||
(select a.骑行开始站点ID as 骑行站点ID,a.骑行开始站点 as 站点地址 FROM testdb.bike2025 a
|
||
UNION
|
||
select b.骑行结束站点ID as 骑行站点ID,b.骑行结束站点 as 站点地址 FROM testdb.bike2025 b) c
|
||
--
|
||
-- REPLACE INTO testdb.tingchequ(骑行站点ID, 站点地址)
|
||
-- select c.骑行站点ID,replace(c.站点地址,'null','') 站点地址 from
|
||
-- (select a.骑行开始站点ID as 骑行站点ID,a.骑行开始站点 as 站点地址 FROM testdb.bike2025 a
|
||
-- UNION
|
||
-- select b.骑行结束站点ID as 骑行站点ID,b.骑行结束站点 as 站点地址 FROM testdb.bike2025 b) c
|
||
SELECT * FROM testdb.tingchequ WHERE 经度 IS NULL OR 纬度 IS NULL
|
||
|
||
|
||
select SUBSTRING_INDEX(a.站点地址,'至',1) from testdb.tingchequ a WHERE 经度 IS NULL OR 纬度 IS null
|
||
|
||
|
||
update testdb.tingchequ a set a.站点地址 = SUBSTRING_INDEX(a.站点地址,'至',1) WHERE 经度 IS NULL OR 纬度 IS NULL
|
||
update testdb.tingchequ a set a.站点地址 = '蟠龙枢纽站' WHERE 经度 IS NULL and 纬度 IS null and 站点地址 = '--3'
|
||
|
||
|
||
insert ignore INTO testdb.tingchequ(骑行站点ID, 站点地址)
|
||
select c.骑行站点ID,replace(c.站点地址,'null','') 站点地址 from
|
||
(select a.骑行开始站点ID as 骑行站点ID,a.骑行开始站点 as 站点地址 FROM testdb.bike2025 a
|
||
UNION
|
||
select b.骑行结束站点ID as 骑行站点ID,b.骑行结束站点 as 站点地址 FROM testdb.bike2025 b) c
|
||
|
||
SELECT b.骑行开始站点ID,b.骑行开始站点,c.经度,c.纬度, b.骑行结束站点ID,b.骑行结束站点,d.经度,d.纬度
|
||
FROM testdb.bike2025 b, testdb.tingchequ c, testdb.tingchequ d
|
||
where b.骑行开始站点ID = c.骑行站点ID
|
||
and b.骑行结束站点ID = d.骑行站点ID
|
||
|
||
-- delete from testdb.tingchequ a where a.经度 not like '108%'
|
||
|
||
SELECT count(b.订单编号),b.骑行开始站点ID,b.骑行开始站点,c.经度,c.纬度, b.骑行结束站点ID,b.骑行结束站点
|
||
FROM testdb.bike2025 b, testdb.tingchequ c
|
||
where b.骑行开始站点ID = c.骑行站点ID
|
||
and c.经度 = '108.254643'
|
||
|