162 lines
3.6 KiB
SQL
162 lines
3.6 KiB
SQL
create procedure proc_insert_chengyongche()
|
|
BEGIN
|
|
-- 车品牌
|
|
|
|
insert into lb_cardata_brand (
|
|
ID,
|
|
brandid,
|
|
brandname,
|
|
brandinitial,
|
|
opertype,
|
|
modeltimestamp,
|
|
seriestimestamp,
|
|
brandtimestamp,
|
|
currentversion,
|
|
initialversion,
|
|
retainfield1,
|
|
retainfield2,
|
|
retainfield3,
|
|
inputuserid,
|
|
inputorgid,
|
|
inputtime,
|
|
updateuserid,
|
|
updateorgid,
|
|
updatetime
|
|
) (
|
|
SELECT
|
|
REPLACE(UUID(),'-',''),
|
|
CONCAT('APCB',@rownum := @rownum + 1) a,
|
|
t.brand,
|
|
'' brandinitial,
|
|
'' opertype,
|
|
'' a1,
|
|
'' a2,
|
|
DATE_FORMAT(NOW(),'%Y/%m/%d %T') a11,
|
|
'' a3,
|
|
'1.0.001',
|
|
'' a4,
|
|
'' a5,
|
|
'' a6,
|
|
'administrator' ,
|
|
'8009001',
|
|
DATE_FORMAT(CURDATE(),'%Y/%m/%d'),
|
|
'' a7 ,
|
|
'' a8 ,
|
|
'' a9
|
|
FROM li_passenger_car t,(SELECT @rownum := IFNULL((SELECT COUNT(brandid) AS CON FROM lb_cardata_brand WHERE brandid LIKE 'APC%'),0)) r
|
|
WHERE T.brand NOT IN(SELECT brandname FROM lb_cardata_brand GROUP BY brandname) GROUP BY t.brand);
|
|
-- 车系 23
|
|
insert into lb_cardata_series (
|
|
id,
|
|
brandid,
|
|
brandname,
|
|
seriesid,
|
|
seriesname,
|
|
seriesgroupname,
|
|
levelname,
|
|
makertype,
|
|
opertype,
|
|
modeltimestamp,
|
|
seriestimestamp,
|
|
brandtimestamp,
|
|
currentversion,
|
|
initialversion,
|
|
retainfield1,
|
|
retainfield2,
|
|
retainfield3,
|
|
inputuserid,
|
|
inputorgid,
|
|
inputtime,
|
|
updateuserid,
|
|
updateorgid,
|
|
updatetime
|
|
)(
|
|
SELECT a.id,lccb.brandid,a.brand,a.a,a.cars,a.seriesgroupname,a.levelname,a.makertype,a.opertype,a.modeltimestamp,
|
|
a.b1,a.brandtimestamp,a.currentversion,a.b2,a.b3,a.b4,a.b5,a.b6,a.b7,a.b8,
|
|
a.a7,a.a8,a.a9 FROM (
|
|
SELECT
|
|
REPLACE(UUID(),'-','') id,
|
|
'',
|
|
t.brand brand,
|
|
CONCAT('APDS',@rownum := @rownum + 1) a,
|
|
t.cars,
|
|
'' seriesgroupname,
|
|
'' levelname,
|
|
'' makertype,
|
|
'' opertype,
|
|
'' modeltimestamp,
|
|
DATE_FORMAT(NOW(),'%Y/%m/%d %T') b1,
|
|
'' brandtimestamp,
|
|
'' currentversion,
|
|
'1.0.001' b2,
|
|
'' b3,
|
|
'' b4,
|
|
'' b5,
|
|
'administrator' b6,
|
|
'8009001' b7,
|
|
DATE_FORMAT(CURDATE(),'%Y/%m/%d') b8,
|
|
'' a7 ,'' a8 ,'' a9
|
|
FROM (SELECT t1.* FROM li_passenger_car t1 GROUP BY t1.cars,t1.brand) t,( SELECT @rownum := ifnull((SELECT count(seriesid) as con FROM lb_cardata_series WHERE seriesid LIKE 'APD%'
|
|
),0)) r WHERE t.cars NOT IN(SELECT seriesname FROM lb_cardata_series GROUP BY seriesname)) a
|
|
LEFT JOIN lb_cardata_brand lccb ON a.brand=lccb.brandname);
|
|
|
|
|
|
-- 车型
|
|
insert into lb_cardata_model (
|
|
ID,
|
|
brandid,
|
|
brandname,
|
|
seriesid,
|
|
seriesname,
|
|
seriesgroupname,
|
|
modelid,
|
|
modelname,
|
|
price,
|
|
liter,
|
|
geartype,
|
|
modelyear,
|
|
makertype,
|
|
dischargestandard,
|
|
seatnumber,
|
|
minregyear,
|
|
maxregyear,
|
|
opertype,
|
|
modeltimestamp,
|
|
seriestimestamp,
|
|
brandtimestamp,
|
|
currentversion,
|
|
initialversion,
|
|
retainfield1,
|
|
retainfield2,
|
|
retainfield3,
|
|
inputuserid,
|
|
inputorgid,
|
|
inputtime,
|
|
updateuserid,
|
|
updateorgid,
|
|
updatetime
|
|
)(
|
|
SELECT a.id,lccs.brandid,a.brand,lccs.seriesid,a.cars,a.seriesgroupname,a.a,a.models,a.b1,a.a12,a.a13,a.a14,a.a15,a.a16,a.a17,a.a18,a.a19,a.a20,a.a21,a.a22,
|
|
a.a23,a.a24,a.b2,a.a25,a.a26,a.a27,a.b6,a.b7,a.a28,a.a7,a.a8,a.a9 FROM (
|
|
SELECT
|
|
REPLACE(UUID(),'-','') id,
|
|
'' bbb,
|
|
t.brand,
|
|
'' aaa,
|
|
t.cars,
|
|
'' seriesgroupname,
|
|
CONCAT('APEM',@rownum := @rownum + 1) a,
|
|
t.models,
|
|
t.the_guided/10000 b1,
|
|
'' a12,'' a13,'' a14,'' a15,'' a16,'' a17,'' a18,'' a19,'' a20,
|
|
DATE_FORMAT(NOW(),'%Y/%m/%d %T') a21,'' a22,'' a23,'' a24,
|
|
'1.0.001' b2,'' a25,'' a26,'' a27,'administrator' b6,'8009001' b7,
|
|
DATE_FORMAT(CURDATE(),'%Y/%m/%d') a28,
|
|
'' a7,'' a8,'' a9
|
|
FROM (SELECT t1.* FROM li_passenger_car t1 GROUP BY t1.models,t1.cars,t1.brand) t,( SELECT @rownum := ifnull((SELECT count(modelid) as con FROM lb_cardata_model WHERE modelid LIKE 'APE%'),0)) r WHERE t.models NOT IN(SELECT modelname FROM lb_cardata_model GROUP BY modelname)
|
|
) a
|
|
LEFT JOIN lb_cardata_series lccs ON a.brand=lccs.brandname AND a.cars=lccs.seriesname
|
|
);
|
|
END;
|
|
|