/*
MySQL Backup
Database: aroad
Backup Time: 2019-5-27 09:55:52
*/

SET FOREIGN_KEY_CHECKS = 0;
DROP VIEW IF EXISTS `aroad`.`v_traffic_route`;
DROP VIEW IF EXISTS `aroad`.`v_traffic_roadsection`;
DROP FUNCTION IF EXISTS `aroad`.`func_inc_var_session`;
CREATE
    DEFINER = `root`@`localhost` FUNCTION `func_inc_var_session`(reset BIT) RETURNS int(11)
BEGIN
    IF reset THEN
        SET @var := 0;
    ELSE
        SET @var := IFNULL(@var, 0) + 1;
    END IF;
    return @var;
END;

CREATE ALGORITHM = UNDEFINED DEFINER =`root`@`localhost` SQL SECURITY DEFINER VIEW `v_traffic_route` AS select `func_inc_var_session`(0)                          AS `id`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`luxianbianhao`           AS `road_code`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`luxianmingcheng`         AS `road_name`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`guancelicheng`           AS `spot_length`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`jidongche_dangliangheji` AS `JDC_current_amount`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`jidongche_ziranheji`     AS `JDC_natural_amount`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`qiche_dangliangheji`     AS `QC_current_amount`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`qiche_ziranheji`         AS `QC_natural_amount`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`qiche_xiaoxinghuoche`    AS `QC_s_truck`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`qiche_zhongxinghuoche`   AS `QC_m_truck`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`qiche_daxinghuoche`      AS `QC_l_truck`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`qiche_tedahuoche`        AS `QC_xl_truck`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`qiche_jizhuangxiangche`  AS `QC_jzx_truck`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`qiche_zhongxiaokeche`    AS `QC_sm_bus`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`qiche_dakeche`           AS `QC_l_bus`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`motuoche`                AS `motor`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`tuolaji_dangliangshu`    AS `TLJ_current_amount`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`tuolaji_ziranshu`        AS `TLJ_natural_amount`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`xingshiliang`            AS `drive_amount`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`shiyingjiaotongliang`    AS `adapt_amount`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`jiaotongyongjidu`        AS `crowd`,
                                                                                                               `aroad`.`t_lwjc_sdlxjtl`.`nian`                    AS `year`
                                                                                                        from (`aroad`.`t_lwjc_sdlxjtl`
                                                                                                                 join (select `func_inc_var_session`(1) AS `func_inc_var_session(1)`) `r`)
                                                                                                        where ((`aroad`.`t_lwjc_sdlxjtl`.`luxianmingcheng` <> '合计') and
                                                                                                               (length(`aroad`.`t_lwjc_sdlxjtl`.`luxianbianhao`) = 4))
                                                                                                        union all
                                                                                                        select `func_inc_var_session`(0)                          AS `id`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`luxianbianhao`           AS `road_code`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`luxianmingcheng`         AS `road_name`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`guancelicheng`           AS `spot_length`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`jidongche_dangliangheji` AS `JDC_current_amount`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`jidongche_ziranheji`     AS `JDC_natural_amount`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`qiche_dangliangheji`     AS `QC_current_amount`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`qiche_ziranheji`         AS `QC_natural_amount`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`qiche_xiaoxinghuoche`    AS `QC_s_truck`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`qiche_zhongxinghuoche`   AS `QC_m_truck`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`qiche_daxinghuoche`      AS `QC_l_truck`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`qiche_tedahuoche`        AS `QC_xl_truck`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`qiche_jizhuangxiangche`  AS `QC_jzx_truck`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`qiche_zhongxiaokeche`    AS `QC_sm_bus`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`qiche_dakeche`           AS `QC_l_bus`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`motuoche`                AS `motor`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`tuolaji_dangliangshu`    AS `TLJ_current_amount`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`tuolaji_ziranshu`        AS `TLJ_natural_amount`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`xingshiliang`            AS `drive_amount`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`shiyingjiaotongliang`    AS `adapt_amount`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`jiaotongyongjidu`        AS `crowd`,
                                                                                                               `aroad`.`t_lwjc_gdlxjtl`.`nian`                    AS `year`
                                                                                                        from (`aroad`.`t_lwjc_gdlxjtl`
                                                                                                                 join (select `func_inc_var_session`(1) AS `func_inc_var_session(1)`) `r`)
                                                                                                        where ((`aroad`.`t_lwjc_gdlxjtl`.`luxianmingcheng` <> '合计') and
                                                                                                               (length(`aroad`.`t_lwjc_gdlxjtl`.`luxianbianhao`) = 4));
CREATE ALGORITHM = UNDEFINED DEFINER =`root`@`localhost` SQL SECURITY DEFINER VIEW `v_traffic_roadsection` AS select `func_inc_var_session`(0)                                      AS `id`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`guanchazhanmingcheng`            AS `spot_name`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`guanchazhanbianhao`              AS `spot_code`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`guancelicheng`                   AS `spot_length`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`jishudengji`                     AS `tech_level`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`luxiankuandu`                    AS `road_width`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`qiche_xiaoxinghuoche`            AS `QC_s_truck`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`qiche_zhongxinghuoche`           AS `QC_m_truck`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`qiche_daxinghuoche`              AS `QC_l_truck`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`qiche_tedahuoche`                AS `QC_xl_truck`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`qiche_jizhuangxiangche`          AS `QC_jzx_truck`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`qiche_zhongxiaokeche`            AS `QC_sm_bus`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`qiche_dakeche`                   AS `QC_l_bus`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`qiche_qichexiaoji_ziranheji`     AS `QC_natural_amount`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`qiche_qichexiaoji_dangliangheji` AS `QC_current_amount`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`motuocheziranshu`                AS `motor`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`tuolajiheji_dangliangshu`        AS `TLJ_current_amount`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`tuolajiheji_ziranshu`            AS `TLJ_natural_amount`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`jidongcheheji_ziranheji`         AS `JDC_current_amount`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`jidongcheheji_dangliangheji`     AS `JDC_natural_amount`,
                                                                                                                     `aroad`.`t_lwjc_gdldnpjrjtl`.`nian`                            AS `year`
                                                                                                              from (`aroad`.`t_lwjc_gdldnpjrjtl`
                                                                                                                       join (select `func_inc_var_session`(1) AS `func_inc_var_session(1)`) `r`)
                                                                                                              where ((`aroad`.`t_lwjc_gdldnpjrjtl`.`guanchazhanmingcheng` <> '全线平均') and
                                                                                                                     (`aroad`.`t_lwjc_gdldnpjrjtl`.`jishudengji` <> '高速公路'))
                                                                                                              union all
                                                                                                              select `func_inc_var_session`(0)                                      AS `id`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`guanchazhanmingcheng`            AS `spot_name`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`guanchazhanbianhao`              AS `spot_code`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`guancelicheng`                   AS `spot_length`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`jishudengji`                     AS `tech_level`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`luxiankuandu`                    AS `road_width`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`qiche_xiaoxinghuoche`            AS `QC_s_truck`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`qiche_zhongxinghuoche`           AS `QC_m_truck`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`qiche_daxinghuoche`              AS `QC_l_truck`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`qiche_tedahuoche`                AS `QC_xl_truck`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`qiche_jizhuangxiangche`          AS `QC_jzx_truck`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`qiche_zhongxiaokeche`            AS `QC_sm_bus`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`qiche_dakeche`                   AS `QC_l_bus`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`qiche_qichexiaoji_ziranheji`     AS `QC_natural_amount`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`qiche_qichexiaoji_dangliangheji` AS `QC_current_amount`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`motuocheziranshu`                AS `motor`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`tuolajiheji_dangliangshu`        AS `TLJ_current_amount`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`tuolajiheji_ziranshu`            AS `TLJ_natural_amount`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`jidongcheheji_ziranheji`         AS `JDC_current_amount`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`jidongcheheji_dangliangheji`     AS `JDC_natural_amount`,
                                                                                                                     `aroad`.`t_lwjc_sdldnpjrjtl`.`nian`                            AS `year`
                                                                                                              from (`aroad`.`t_lwjc_sdldnpjrjtl`
                                                                                                                       join (select `func_inc_var_session`(1) AS `func_inc_var_session(1)`) `r`)
                                                                                                              where ((`aroad`.`t_lwjc_sdldnpjrjtl`.`guanchazhanmingcheng` <> '全线平均') and
                                                                                                                     (`aroad`.`t_lwjc_sdldnpjrjtl`.`jishudengji` <> '高速公路'));
