Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
SELECT
id, t1.name, model, body_name, color, engine, grade, inspection, recycle,
year,
country_name,
price AS used_price,
large_area_name, shop_pref_name, shop_lat, shop_lng, shop_name,t1.shop_pref_code,
maintenance, maintenance_fee,
odd, odd_numerics, odd_unit,
IF( substr(odd_unit, 0, 2) = 'km', odd_numerics, odd_numerics*10000 ) AS odd_km,
warranty, warranty_distance, warranty_fee, warranty_length_kigen, warranty_length_kikan,
IF( substr(warranty_length_kikan, length(warranty_length_kikan), 1) = '',
regexp_extract(warranty_length_kikan, '([0-9]*)(.*)', 1) * 12,
regexp_extract(warranty_length_kikan, '([0-9]*)(.*)', 1)
) AS warranty_length_kikan_m,
main_photo_l, `desc`
FROM
(
SELECT id, name, model, price, recycle, body_name, color, engine, grade, inspection, maintenance_fee, shop_pref_name, shop_pref_code, shop_lat, shop_lng, shop_name, maintenance, warranty, warranty_distance, warranty_fee, warranty_length_kigen, warranty_length_kikan, year, main_photo_l, `desc`,
odd,
CAST(regexp_extract(odd, '([\.0-9]*)(.*)', 1) AS FLOAT) AS odd_numerics,
regexp_extract(odd, '([\.0-9]*)(.*)', 2) AS odd_unit
FROM usedcar
) t1
JOIN
(
SELECT country_name, name
FROM brand_master
) t2
ON (t1.name = t2.name )
JOIN
(
SELECT code, large_area_name
FROM pref_master
) t3
ON ( t1.shop_pref_code = t3.code )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment