129 lines
3.0 KiB
SQL
129 lines
3.0 KiB
SQL
create procedure proc_insert_shangyongche()
|
|
BEGIN
|
|
-- 记录试图vi_product备注
|
|
-- 导入车品牌
|
|
insert into lb_cardata_commercial_brand (
|
|
ID,
|
|
brand_id,
|
|
brand_name,
|
|
brand_initial,
|
|
initialversion,
|
|
inputuserid,
|
|
inputorgid,
|
|
inputtime,
|
|
updateuserid,
|
|
updateorgid,
|
|
updatetime
|
|
)
|
|
(
|
|
SELECT a.* FROM (
|
|
SELECT
|
|
REPLACE(UUID(),'-',''),
|
|
CONCAT('APB',@rownum := @rownum + 1) a,
|
|
t.brand,
|
|
'' brand_initial,
|
|
'1.0.001',
|
|
'administrator' ,
|
|
'8009001',
|
|
DATE_FORMAT(CURDATE(),'%Y/%m/%d'),
|
|
'' a1 ,'' a2 ,'' a3
|
|
FROM li_cardata_commercial t,( SELECT @rownum := (SELECT MAX(CONVERT(SUBSTR(BRAND_id,4),SIGNED)) FROM lb_cardata_commercial_brand WHERE BRAND_id LIKE 'AP%'
|
|
)) r
|
|
GROUP BY t.brand
|
|
) a
|
|
LEFT JOIN lb_cardata_commercial_brand lccb ON a.brand =lccb.brand_name
|
|
WHERE lccb.id IS NULL
|
|
) ;
|
|
|
|
-- 导入车系
|
|
|
|
|
|
INSERT INTO lb_cardata_commercial_series
|
|
( ID,
|
|
series_id,
|
|
series_name,
|
|
brand_id,
|
|
brand_name,
|
|
car_type,
|
|
initialversion,
|
|
inputuserid,
|
|
inputorgid,
|
|
inputtime,
|
|
updateuserid,
|
|
updateorgid,
|
|
updatetime
|
|
)(
|
|
SELECT REPLACE(MD5(UUID()),'-','') id,a.a,a.cars,lccb.brand_id,a.brand,a.vehicle_type,a.b1,a.b2,a.b3,a.b4,a.a1,a.a2,a.a3 FROM (
|
|
SELECT
|
|
'' id,
|
|
CONCAT('APS',@rownum := @rownum + 1) a,
|
|
t.cars,
|
|
'',
|
|
t.brand brand,
|
|
t.vehicle_type,
|
|
'1.0.001' b1,
|
|
'administrator' b2,
|
|
'8009001' b3,
|
|
DATE_FORMAT(CURDATE(),'%Y/%m/%d') b4,
|
|
'' a1 ,'' a2 ,'' a3
|
|
FROM (SELECT t1.* FROM li_cardata_commercial t1 GROUP BY t1.cars,t1.brand) t,( SELECT @rownum := (SELECT MAX(CONVERT(SUBSTR(series_id,4),SIGNED)) FROM lb_cardata_commercial_series WHERE series_id LIKE 'AP%'
|
|
)) r
|
|
GROUP BY t.cars,t.brand
|
|
) a
|
|
LEFT JOIN lb_cardata_commercial_series lccs ON a.cars =lccs.series_name AND a.brand=lccs.brand_name
|
|
LEFT JOIN lb_cardata_commercial_brand lccb ON a.brand=lccb.brand_name
|
|
WHERE lccs.id IS NULL
|
|
);
|
|
|
|
-- 导入车型
|
|
|
|
|
|
INSERT INTO lb_cardata_commercial_model
|
|
( ID,
|
|
model_id,
|
|
model_name,
|
|
series_id,
|
|
series_name,
|
|
brand_id,
|
|
brand_name,
|
|
price,
|
|
liter,
|
|
vehicle_type,
|
|
level,
|
|
initialversion,
|
|
inputuserid,
|
|
inputorgid,
|
|
inputtime,
|
|
updateuserid,
|
|
updateorgid,
|
|
updatetime
|
|
)(
|
|
SELECT REPLACE(MD5(UUID()),'-','') id,a.a,a.models,lccs.series_id,a.cars,lccs.brand_id,a.brand,a.b1,a.a4,a.vehicle_type,a.level,a.b2,a.b3,a.b4,a.b5,a.a1,a.a2,a.a3 FROM (
|
|
SELECT
|
|
'' id,
|
|
CONCAT('APM',@rownum := @rownum + 1) a,
|
|
t.models,
|
|
'' aaa,
|
|
t.cars,
|
|
'' bbb,
|
|
t.brand,
|
|
t.the_guided/10000 b1,
|
|
'' a4,
|
|
t.vehicle_type,
|
|
t.LEVEL,
|
|
'1.0.001' b2,
|
|
'administrator' b3,
|
|
'8009001' b4,
|
|
DATE_FORMAT(CURDATE(),'%Y/%m/%d') b5,
|
|
'' a1,'' a2 ,'' a3
|
|
FROM (SELECT t1.* FROM li_cardata_commercial t1 GROUP BY t1.models,t1.cars,t1.brand) t,( SELECT @rownum := (SELECT MAX(CONVERT(SUBSTR(model_id,4),SIGNED)) FROM lb_cardata_commercial_model WHERE model_id LIKE 'AP%') ) r
|
|
) a
|
|
LEFT JOIN lb_cardata_commercial_model lccm ON a.models =lccm.model_name AND a.cars=lccm.series_name AND a.brand=lccm.brand_name
|
|
LEFT JOIN lb_cardata_commercial_series lccs ON a.brand=lccs.brand_name AND a.cars=lccs.series_name
|
|
WHERE lccm.id IS NULL
|
|
)
|
|
|
|
;
|
|
END;
|
|
|